I was able to successfully use the substitute function to search through an entire column of strings and look for "hello" and replace it with "world".
I used the Calculation field instructions to create a new calculation field that runs the following code:
Substitute(column3;“hello”;“world”)
So now I have a new column4 which is an exact copy of column3 except "hello" is replaced with "world".
But I would additionally like to do more substitutions like:
Substitute(column3;“BBB”;“Better Business Bureau”)
Substitute(column3;“CCC”;“Cats Candy Cinder”)
This creates an error message indicating I need place a +, -, * operator in between the substitute statements. I'm not really doing a math calculation here, just some string manipulation.
How can I implement a script in File Maker pro that performs all 3 substitutions sequentially?
You can do this:
Substitute(column3;[“AAA”;“Acme”] ; [“BBB”;“Better Business Bureau”] ; [“CCC”;“Cats Candy Cinder”])
This stacked substitution, using the [], will substitute in order. It will go through the column3 three times and perform each substitution separately. So something that was substituted in during the first round could be potentially substituted out in the next round.
https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/substitute.html