Database Reference
In-Depth Information
Substitute()
is one of a few FileMaker functions that support a special
bracketed syn-
tax
. You can perform
several
replacements on a piece of text with one substitute func-
tion. Each pair in brackets represents one search value and its replacement value. Here's
how to remove all the vowels from a field:
Substitute ( My Field ; ["a" ; ""] ; ["e" ; ""] ;
["i" ; ""] ; ["o" ; ""] ;
["u" ; ""] )
NOTE
This example shows another nice fact about the substitute function: You can use it to
remove
something. Just replace it with empty quotes:
“”
.
▪ While the substitute function can be used to change or remove what you specify,
Fil-
ter()
can remove everything you
don't
specify. For example, suppose you want to strip
any non-numeric characters from a credit card number. You can
try
to think of all the
possible things a person might type in a Credit Card Number field (good luck!), or you
can use the filter function instead:
Filter ( Credit Card Number ; "0123456789" )
This calculation tells FileMaker to return the contents of the Credit Card Number field
with everything except the numerals removed. In other words, simply put the characters
you'd like to
keep
in the second parameter.
▪ The
PatternCount()
function tells you how many of a specific character string exist in
the text. The function needs to know what you're searching, and what to look for. Con-
sider:
PatternCount ( US Constitution ; "citizen" )
In this case, the calculation returns
11
. If the pattern doesn't appear in the text, then the
calc returns a
0
.
▪ The
Trim()
function cleans up after those data entry folks who can't stop typing extra
spaces before and after the important stuff. Just tell the function what to look at. From
the calculation below, you get the string “Anyway” placed in your field.
Trim ( "Anyway " )
