CF_ListToCommas ( ListOfItems ; And_Or_Parameter ; MaxNumber )

Converts a carriage-return delimited list to one separated by commas, with the word

Average rating: 4.5 (41 votes) Log in to vote

Nick Stockbridge - Show more from this author
Transmedia Communications Ltd
http://www.transmedia.co.uk

Sample input:

Peter
Sue
Brian
Kamal
Sarah
John
Amina
Earl
Jo
Dominique
James

Sample output:

Peter, Sue, Brian, Kamal, Sarah and 6 others

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

/*
 
Converts any return-delimited list to items separated by commas, with the word "and" before the last item. No full stop (period) at the end in case it's not needed.   

Specify the list, whether to use AND or OR as the last separator and the maximum number of items to name before stating 'and X others' 
Result examples:

Red, Blue, Green and Yellow
Bill, Sue, Jo, Peter and 6 others


*/

Let(

[

List = ListOfItems ;
Max = If ( IsEmpty ( MaxNumber ) ; 99999 ; MaxNumber ) ;  // Use a large number if no specific maximum have been set
CountValues = PatternCount ( List ; "¶" )  + 1 ;
Extras = If ( CountValues > Max ; CountValues - Max ; 0 ) ;
LastValue = RightValues ( List ; 1 ) ;
FirstValues = If ( Max  ≥  CountValues ; LeftValues ( List ; CountValues - 1 ) ; LeftValues ( List ; Max ) ) ;

FirstValues = Substitute ( FirstValues ; "¶" ; ", " ) ;

/* The last 2 chars are a comma and a space so remove these */

FirstValues = Replace ( FirstValues ; Length ( FirstValues ) - 1; 2 ; "" ) ; 

Result = Case (

not IsEmpty ( FirstValues ) and Extras = 0 ; FirstValues & " " & And_Or_Parameter  & " " & LastValue ; 
not IsEmpty ( FirstValues ) and Extras = 1 ; FirstValues & " " & And_Or_Parameter  & " one other" ; 
not IsEmpty ( FirstValues ) and Extras > 1 ; FirstValues & " " & And_Or_Parameter  & " " & Extras  &  " others" ; 
LastValue ) ;

FinalResult = Substitute ( Result ; "¶" ; "" ) 

] ;

FinalResult
)


/*

Nick Stockbridge, Transmedia Communications Ltd, 2008.
nick@transmedia.co.uk  

*/

Converts a carriage-return delimited list to one separated by commas, with the word "and" placed before the last item. I'm expecting that most people will use the List function on a related set of records to generate the items required.

Added a Max Number parameter and an And/Or preference, April 2010.

Comments

		Michelle, Houston Mar 11, 2009
Meant to rate this a 5, accidentally clicked on the wrong one. CF works great, exactly what I needed. Thanks!

		Denise, Boston Apr 7, 2010
I get the last value duplicated. For example: Value1, Value2, and Value2 How do I correct this?

		Sonja Froyen, Minneapolis Jun 17, 2014
I added the Oxford comma by adding the following line to the case statement: not IsEmpty ( FirstValues ) and Extras = 0 and CountValues = 2; FirstValues & " " & And_Or_Parameter & " " & LastValue ; and modified the following case to include the comma. not IsEmpty ( FirstValues ) and Extras = 0 ; FirstValues & ", " & And_Or_Parameter & " " & LastValue ; Great CF

		Nick Stockbridge, Transmedia Communications Ltd Jun 17, 2014
Thanks Sonja. I confess I had not thought of the Oxford comma situation! Nick

		Daniel Wood, Digital Fusion Ltd Jul 2, 2014
brilliant, just what I was looking for ! Thank you !

		Nick Stockbridge, Transmedia Communications Ltd Jul 2, 2014
Thanks Daniel. Glad to hear it.

		Matt, Chicago Feb 24, 2018
How do I use the AND_OR_Parameter

		Nick Stockbridge, Transmedia Communications Ltd Feb 24, 2018
Hi You replace the And_Or_Parameter with the word "and" or "or" as you require for your prefix to the ending "others". So in my example with people's names above, I substituted And_Or_Parameter with "and" to derive the result "Peter, Sue, Brian, Kamal, Sarah and 6 others". Hope that makes sense. Nick

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

CF_ListToCommas ( ListOfItems ; And_Or_Parameter ; MaxNumber )

Comments

Support this website.

Support this website.