Brian Dunning's FileMaker Custom Functions

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   Nick Stockbridge - Show more from this author
Transmedia Communications Ltd
http://www.transmedia.co.uk

Share on Facebook Share on Twitter

  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 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   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   Denise, Boston
Apr 7, 2010
I get the last value duplicated. For example:

Value1, Value2, and Value2

How do I correct this?
 
Sonja Froyen   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   Nick Stockbridge, Transmedia Communications Ltd
Jun 17, 2014
Thanks Sonja. I confess I had not thought of the Oxford comma situation!

Nick
 
Daniel Wood   Daniel Wood, Digital Fusion Ltd
Jul 2, 2014
brilliant, just what I was looking for ! Thank you !
 
Nick Stockbridge   Nick Stockbridge, Transmedia Communications Ltd
Jul 2, 2014
Thanks Daniel. Glad to hear it.
 
Matt   Matt, Chicago
Feb 24, 2018
How do I use the AND_OR_Parameter
 
Nick Stockbridge   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
 

Log in to post comments.

 

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: