by Lorne Walton, <lornew@wimsey.com>
Working with Text in FileMaker
Part 1: The Text Functions (Beginner's Series)
This month we start a new series with a look at FileMaker Pro v 3's built-in functions for handling text data. Even the intermediates in the class may learn something from this. For example, do you know what TextToDate[field] comes up with when "field" does not contain a valid date?
An upcoming Bonus file called FUNCTNS.FP3 written by ISO Staff and Contributors will have these functions plus others.
Other articles in this series will include:
Number Functions
Date/Time Functions
Aggregate/Summary/Repetition Functions
Finance/Trigonometric Functions
Logical Functions
Status Functions
What's a Text Function?
For purposes of this series, a text function is a formula used in a calculation that works with text as its primary focus. We will be dealing with Text Functions primarily in ScriptMaker - although you CAN use Status Functions in any calculation. For example, create a new script named "Test", click the "Create" button (or press enter) and then the "Clear All" button at the bottom of the script, then double-click on "Set Field" or "If" from the script steps menu on the left.
Now when you click on the "Specify" button, you get a nice big scrollable window with a pull-down menu at the top right corner. The ScriptMaker and Define Calculation windows give you lots of space for entering your calculations. Instead of "all by name" select "Text Functions" from the pop-up menu in the upper right, and you'll see a list of the 20 text-manipulating functions.
Exact(original text, comparison text)
Returns "1" as a numeric result if the two text items (at least one of which usually is a field name) are identical, case and all; zero otherwise. Think of "1" as "true" and "0" as "false". FileMaker thinks of any non-zero number as "True", but always returns a value of 1 when computing a Boolean (true/false) result.
Want your database to insist on the data in a Name field being properly capitalized? You might do this by going to Define Fields, selecting the Name field, pressing options, and then Verify By Calculation, entering in the calculations window the text:
Exact (Name, Proper(Name))
Then FileMaker will alert you when "Name" is not exactly the same as "Proper(Name)". (But see the warning under "Proper", below.)
Left (text, number)
Returns the first number characters of text. Again, at least one of the two arguments is usually going to be a field name. A simple example is:
Set Field [Short Name, Left (Name, 5)]
TIP courtesy of ISO Staff Writer Bob Cusick: You can COMMENT your calculations by using:
Left ("THIS IS MY COMMENT", 0)
LeftWords (text, number of words)
Returns the first number of words from the text or field specified. Note that words are delimited by spaces, by the carriage return (or paragraph) character, ¶, and by a number of other punctuation and special characters. A series of multiple special characters is counted as a single occurrence. Leading and trailing special characters are ignored. If multiple special characters are embedded in the text they are still considered a single delimiter, but are returned as part of the result:
LeftWords (" ¶Mary had a little", 2)
LeftWords (" :::()¶Mary had a little", 2)
LeftWords (" :;,;:()¶Mary had a little", 2)
all return "Mary had" while
LeftWords (" :::()¶Mary//,/had a little", 2)
returns "Mary//,/had". Be careful with these Words functions.
Length (text)
The number of characters in text. All characters are included.
Length ("All¶good men")
equals 12.
Lower (text)
Converts text to all lower-case. Non-alphabetic characters are not affected.
Middle (text, start, size)
To extract text from the middle of a string, this function needs to be told where to start and how many characters to grab. Thus:
Middle ("The time has come¶for all good men", 11, 9)
returns "as come¶f".
MiddleWords (text, starting word, number of words)
You've probably already figured out that the result of this function will be the number words starting with the starting -th word, of text. The same caveats apply as with the LeftWords function.
PatternCount (text, search string)
Computes the number of occurrences of search string within text. The value of:
PatternCount ("The time has come for all good men", "me")
will be 3, while
PatternCount ("The time has come for all good men", "me ")
will be 2.
To find the number of "items" in a ¶-delimited list named "field", when individual items may contain a space (so you can't use WordCount) and you want to count even blank items:
PatternCount (field, "¶") + 1
To do the same with a slash-delimited list or a comma-delimited list, substitute your delimiter for the paragraph character in the above formula.
Position (text, search string, start, occurrence)
A more potent cousin of the preceding function, this returns a zero if search string is not present in text. Otherwise it returns the starting position of the string. Start and occurrence must be specified, and further refine the search. Here's how to find the position of the first occurrence of a substring:
Position ("The time has come for all good men", "me", 1, 1)
If you want to calculate the start of the 2nd occurrence of "me" after the 10th character in our test phrase, use:
Position ("The time has come for all good men", "me", 11, 2)
and your result will be 32. If your occurrence is given a negative value, the search will start at the right end of text, and the value of:
Position ("The time has come for all good men", "me", 34, -3)
should be 7. Get it?
Proper (text)
Converts text into proper noun, or "title" format: each word's first letter capitalized. Careful when using this on hyphenated names: J. SMITH-JONES will become "J. Smith-jones".
Replace (text, start, size, replacement text)
Replaces a series of characters in text starting at start for size characters, with replacement text. The following will change the word "time" of our test string to "summer":
Replace ("The time has come for all good men", 5, 4, "summer")
Right (text, number)
Fetches the last number characters of the string text. If the field named field contains our test string, then this line catches all but the first 3 characters of the string:
Right (field, Length(field) - 3)
while this one results in everything beyond the 2nd space:
Right (field, Length(field) - Position (field, " ", 1, 2))
TIP courtesy of ISO Staff Writer Bob Cusick: You can COMMENT your calculations by using:
Right ("THIS IS MY CALC COMMENT", 0)
RightWords (text, number of words)
At first glance, the following script line does the exact same thing as the last one. But it also takes into account the possibility of other characters than <space> as word delimiters, and it considers a series of those funny characters as a single delimiter rather than a series:
RightWords (field, WordCount(field) - 2)
Substitute (text, search string, replace string)
Locates each occurrence of search string within text and replaces it with replace string.
Substitute ("row, row, row your boat", "row", "sink")
results in "sink, sink, sink your boat".
TextToDate (text)
Converts a valid date in the form of text to FMPro's internal date format. In the bonus file, see what you get when you type a date like 2/30/96.
TextToNum (text)
Watch what happens when text contains non-numeric characters.
TextToNum("12a?b c34/|")
will return 1234.
TextToTime (text)
If you understood TextToDate, you already have this one figured.
Trim (text)
Removes only the leading and trailing spaces from text. Here's how to put the trimmed text into a field named new field while retaining the text in old field:
Set Field [new field, Trim (old field)]
However, there's nothing wrong in FileMaker with:
Set Field [old field, Trim (old field)]
Trim (" Tom ")
will result in "Tom". A great place to use this is when you're calculating the first and last name of a person - since sometimes people put a trailing space after the first name and/or a leading space before the person's last name when doing data entry.
Upper (text)
Converts text to all upper-case.
WordCount (text)
Counts the number of "delimited words" in text. Refer back to the LeftWords discussion.
Concatenation
No discussion of text functions would be complete without mentioning the concatenation operator, "&", which is not listed under Text functions. You might use this formula to try to calculate a unique identifier for each record, something like so:
Left (Surname & "***", 4) & Left (Zip Code, 5) & Right (Phone Number, 4)
You'll see an ID code like this on a lot of address labels from magazine publishers. Note the concatenation in the first part of that formula. It simply ensures that there will be exactly 4 characters in that part of the string, even when Surname is very short, like "Ho".
Putting ‘em all together...
Combining the above functions allows you to do cool things with text. Here's an example that might be used in place of the Proper function, when you want hyphenated names to be handled properly:
If(Position(Name, "-", 1, 1), Replace(Proper(Substitute(Name, "-", " ")), Position(Name, "-", 1, 1), 1, "-"), Proper(Name))
First thing to do when you're tring to figure out someone else's complex, nested calculation, is break it up using the carriage return key on your keyboard, which is ignored in the ScriptMaker & Define Fields windows:
If(Position(Name, "-", 1, 1),
Replace(Proper(Substitute(Name, "-", " ")), Position(Name, "-", 1, 1), 1, "-"),
Proper(Name))
First note what the If function is doing: it separates those names that have a hyphen from those that don't. "Position" evaluates as "True" if non-zero... those names that contain a hyphen are manipulated by first substituting spaces for hyphens, converting to Proper, and then Replacing the space at "Position" with a hyphen again. (I await someone's better way of doing this, since my formula only handles properly those names with a single hyphen.) Those names that don't have a hyphen are generally handled by "Proper(Name)". (But what about O'Hara?)
Note that you can copy and paste calculations like the above into your own ScriptMaker or Define Fields windows. And speaking of breaking up the calculation, check out the Define Fields window for the field "AnsText" in the FUNCTNS.FP3 file. Jim provides a perfect example of how carriage returns should be used for clarity in long calculations.
Where to get help?
While the FMPro documentation and online help have been criticized for lacking certain pieces of useful information, they both provide clear, concise and easy-to-find descriptions of all the text-manipulation functions.
What if you enter arguments which make the formula nonsense? FMPro is very forgiving, logical and consistent when it comes to things like requesting 99 characters from a field that contains only 5. Play with Mr. Fortier's Bonus file to check those things out for yourself.
What if you make syntax errors or typoes in your calculations? Use the ScriptMaker & Define Fields windows to their full potential as described at the beginning of this article. You'll find yourself using your mouse a lot and your keyboard little when defining calculations. When you try to quit the calculation window, you'll be notified if FileMaker can't understand your calculation.
## END ##
This article is part of a series aimed at those who are not yet experienced with FileMaker Pro. If you have a novice-level request that you'd like to see addressed, contact the author at lornew@wimsey.com