How to Use Square Brackets Instead of Nesting the Substitute Function

Articles, Digging-Deep, Quick Tips

FileMaker Quick Tip
  • Who: All Users
  • What: How to Use Square Brackets Instead of Nesting Functions
  • With: FileMaker 11+ (not sure how far back it goes)
  • Why: Nesting with functions like Substitute can get tricky the deeper you go, but with square brackets you can add as many extra conditions as you need and keep it clean and readable.

 

Problem

When I first starting using FileMaker I found many uses for the Substitute function, but as a beginner I often found myself using nested Substitutes.  For example, let’s say I had some text where I wanted to change month values from July to August and change commas to pilcrows (paragraph symbol) in order to create a list of values.  My first step might look like this:

Substitute ( “June,July,August” ; “July” ; “August” )

giving the result of: June,August,August

The substitution of August for July worked, but in order to create the list of values, I would then “Cut” (as in: cut/copy/paste) that substitute statement and insert it into another substitute statement like this:

Substitute ( Substitute ( “June,July,August” ; “July” ; “August” ); “,” ; “¶”)

which results in the list:

July
August
August

You can see the Substitute within a Substitute.  However, if you have to go another level deeper it begins to look unwieldy like this:

Substitute ( Substitute ( Substitute ( “June,July,August” ; “July” ; “August” ); “,” ; “¶”) ; “June” ; “August”)

which returns:

August
August
August

Solution

Fortunately, using the magic of square brackets, we can simplify nested to Substitutes like this:

Substitute (
     “June,July,August” ;
     [“July” ; “August”];
     [“,” ; “¶”];
     [“June” ; “August”]
)

which still results in:

August
August
August

With the square bracket example, we can see that the first line is the original text.  The next 3 lines which use the square brackets simply list 3 different substitution parameters to apply to the original text. This square bracket configuration can be so much easier to read the deeper your nested Substitutes go.

Bonus tip: be sure to leave the semi-colon out after the last square bracket pair or it won’t work.

Hope that helps! 🙂