GapsInSequence ( listOfValues )

Returns a list of gaps found in an incrementing sequence.

Be the first to rate this function Log in to vote

Michael Horak - Show more from this author
*COMMENT Visual Realisation

Sample input:

GapsInSequence ( listOfValues )
with the following listOfValues:
Invoice 1
Invoice 2
Invoice 5
Invoice 6
Invoice 8
Invoice 11
Invoice 12

Sample output:

Invoice 3 - Invoice 4
Invoice 7
Invoice 9 - Invoice 10

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
GapsInSequence function

Author
*COMMENT Visual Realisation

Format
GapsInSequence ( listOfValues )

Parameters
listOfValues - any text expression or text field

Data type returned
text

Description
Returns a list of gaps (missing values) found in an incrementing sequence. Each gap is listed as an individual value or as a range.

A companion function to CollapseList() https://www.briandunning.com/cf/734

August 10, 2022
*/

Let ( [
value1 = GetValue ( listOfValues ; 1 ) ; 
value2 = GetValue ( listOfValues ; 2 ) ; 
value1inc =  SerialIncrement ( value1 ; 1 ) ;
value2dec = SerialIncrement ( value2 ; -1 ) ;
gap = value1inc ≠ value2 ;
range = value1inc ≠ value2dec ;
countValues = ValueCount ( listOfValues ) 
] ;
If ( gap ; value1inc & If ( range ; " - " & value2dec )  & ¶ )
&
If ( countValues > 2 ; GapsInSequence  ( RightValues ( listOfValues ; countValues - 1 ) ) )
)

Returns a list of gaps (missing values) found in an incrementing sequence. Each gap is listed as an individual value or as a range.

A companion function to CollapseList() https://www.briandunning.com/cf/734

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

GapsInSequence ( listOfValues )

Comments

Support this website.

Support this website.