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
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.

Under construction. Email me your wish list for improvements.