- 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.
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:
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”)
Fortunately, using the magic of square brackets, we can simplify nested to Substitutes like this:
[“July” ; “August”];
[“,” ; “¶”];
[“June” ; “August”]
which still results in:
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! 🙂