CODEWITHSUNDEEP
filemaker
user1068636
user1068636

Reputation: 1939

How do you invoke the Substitute function in File Maker pro multiple times to form a single new column in your results?

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?

Upvotes: 0

Views: 410

Answers (2)

matijads
matijads

Reputation: 1

J Brown's answer is correct. Alternatively you could do 3 substitutions within a single Let function like this:

Let ( [
    _input_text = column3;
    _sub1 = Substitute(_input_text; “AAA” ; “Acme” );
    _sub2 = Substitute(_sub1; “AAA” ; “Acme” );
    _sub3 = Substitute(_sub2; “AAA” ; “Acme” );
    _output_text = _sub3
] ;
    _output_text
)

Upvotes: -1

J Brown
J Brown

Reputation: 145

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

Upvotes: 5

Related Questions