The FileMaker Substitute Function

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

Substitute
( text ; searchString ; replaceString )
The Substitute function allows you to substitute a string of text in a field with a different string of text. This function is case sensitive.

EXAMPLE: Substitute(Area Code, "217", "512")
This example would substitute a new area code of 512 where it was previously 217. This is a basic example. In most everyday FileMaker work this will probably be embedded within a number of other functions both text and logical.

The substitute function is case sensitive, so you may have to take that in account within your calculations that use it.

EXAMPLES:
....................................

1) Search sting in the field called Text String has the word solutionmakers

Calculation is
Substitute ( Text String ; "solutionmakers" ; "SolutionMakers" )

The returned text from the function is SolutionMakers
(case sensitivity was met)

....................................

2) Search sting in the field called Text String has the word SOLUTIONMAKERS

Calculation is
Substitute ( Text String ; "solutionmakers" ; "SolutionMakers" )

The returned text from the function is SOLUTIONMAKERS
(case sensitivity was NOT met)

....................................
3) Search sting in the field called Text String has the word SOLUTIONMAKERS

Calculation is
Substitute ( lower(Text String) ; "solutionmakers" ; "SolutionMakers" )

Here, you might think I cheated a little bit. I used a nested function, which is a function within a parameter of another function. I took the text from the field and lowered it for evaluation purposes. So no matter how SolutionMakers was typed in, I would get the result I was looking for.

.....................................

As you can see, there is almost always a way to adjust a calculation to meet your needs.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.