Filemaker Pro 11 Find the number of commas in a field

PachecoPrimo used Ask the Experts™
I am trying to see if it is possible to create a script that can count the number of commas found in a text field (Text_Field) and populate that number(of commas) on another field ('Number_Of_Commas'). I also want to be able to populate the number
of consecutive words (items) that are found on each group that is separated by a comma.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President, Dedication Technologies, Inc.
The number of commas is easy to find using the PatternCount() function. Using your example file it's simply:

PatternCount( Text_Field ; "," )

For the second item, it sounds like you are looking to find the number of comma-separated values. The number of commas plus 1 should give you the correct number of values but if you want to be sure, in case someone added a comma at the end but put nothing after it, you should:

   1) Convert the commas to paragraph returns "¶" using the Substitute function
   2) use the ValueCount() function to count the number of values

The calculation would look like this:

   ValuesInTextField =
   ValueCount( Substitute ( Text_Field ; ","  ; "¶" ) )

To make is a little cleaner, I would add a Trim function to remove any leading or trailing spaces:

   ValueCount( Trim( Substitute ( Text_Field ; ","  ; "¶" ) ) )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial