CollapseList ( listOfValues )

Collapses a list of values by replacing serially incrementing sections with their range boundaries.

Average rating: 3.9 (54 votes) Log in to vote

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

Sample input:

CollapseList ( listOfValues )
with the following listOfValues:
Invoice 1
Invoice 2
Invoice 4
Invoice 5
Invoice 6
Invoice 7
Invoice 8
Invoice 9
Invoice 10
Invoice 11
Invoice 12
Invoice 18
Receipt 52
Receipt 53
Receipt 54

Sample output:

Invoice 1, Invoice 2, Invoice 4 to Invoice 12, Invoice 18, Receipt 52 to Receipt 54

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

/*
CollapseList function

Author
*COMMENT Visual Realisation

Format
CollapseList ( listOfValues )

Parameters
listOfValues - any text expression or text field

Data type returned
text

Description
Collapses a list of values by replacing serially incrementing sections with their range boundaries. 

The result is a comma-separated list of ranges and individual values. The list separator and the range placeholder can be easily modified or defined as additional function parameters.

Values are compared in the order they appear in the list. For best results, the list should be sorted in ascending order.

July 15, 2007
*/

Let ( [
countValues = ValueCount ( listOfValues ) 
] ;

Case ( 
countValues < 2 ; listOfValues ;

Let ( [ 
// --- MODIFY THESE VALUES TO FORMAT THE RESULT AS DESIRED ---
placeholder = " to " ;
separator = ", " ;

a = GetValue ( listOfValues ; 1 ) ; 
b = GetValue ( listOfValues ; 2 ) ; 
c = GetValue ( listOfValues ; 3 ) ; 

ab = b = SerialIncrement ( a ; 1 ) ;
bc = c = SerialIncrement ( b ; 1 ) ;

writeOut = Case (
a ≠ placeholder ; a ; 
not bc ; placeholder ) ;

nextA = Case ( 
bc and ( ab or a = placeholder ) ; placeholder ; 
b ) ;

sep = Case ( a ≠ placeholder and nextA = b ; separator ) ;

nextList = 
Case ( not IsEmpty ( nextA ) ; nextA & ¶ ) 
& 
RightValues ( listOfValues ; countValues - 2 )

] ; 
writeOut & sep & CollapseList ( nextList )
)

)
)

Collapses a list of values by replacing serially incrementing sections with their range boundaries.

The result is a comma-separated list of ranges and individual values. The list separator and the range placeholder can be easily modified or defined as additional function parameters.

Values are compared in the order they appear in the list. For best results, the list should be sorted in ascending order.

See also GapsInSequence() https://www.briandunning.com/cf/2605

Comments

		Andrey Kolganov, Moscow, Russia May 19, 2011
How about this way? Substitute( listOfValues ; "Â¶" ; ", ")

		comment, VR May 19, 2011
Try it and see the difference.

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

CollapseList ( listOfValues )

Comments

Support this website.

Support this website.