FM Function of the Week: Substitute

Like the substitutes you knew in your youth, FileMaker’s Substitute function is easily taken advantage of. Depending on your temperament, however, you may be disappointed to learn that the Substitute function is not easily flustered and won’t demand to know who threw that.

Substitute simply examines some text for a specific set of characters (called a searchString) and replaces them with a new set of characters (the replaceString). Consider this example:

PatternCountAbove, we’re instructing FileMaker to look in the text “Boffins Build Best Butter Dish” and find any instances of the utterly inane word “Boffin”. Should it find any, FileMaker should replace it with the vastly more dignified term “Scientist”. When evaluated, the resulting output is “Scientists Build Best Butter Dish”.

But PatternCount can do more than simply filter fatuous words. Say you’ve got a double-spaced list of items taking up too much screen real estate. Substitute ( yourList ; “¶¶” ; “” ) will collapse the list down to single spacing. Or you can turn a comma-separated list into a return-separated list using Substitute ( yourList ; “,” ; “” ).

If you need to remove something entirely, Substitute is good for that too. Substitute ( yourList ; “!” ; “” ) strips out every exclamation point from your text leaving nothing in their place.

Extra Credit

For you gifted children out there, here’s something extra. FileMaker allows you to perform multiple substitutions on the same text. By encasing  multiple searchString and replaceString pairs inside square brackets, you can run together up to 999 substitutions in the same function call. Copy and paste this calculation into your Data Viewer to see it in action.

Substitute (
“You’ll have all the Slurm you can drink when you’re partying with Slurms McKenzie!” ;
[“You” ; “They”] ;
[“you” ; “they”] ;
[“Slurm” ; “Squid”] ;
[“Kenzie” ; “Corny”]


This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.