Text Functions - File Maker

As the name implies, text functions work with and, usually, return text values. They are most useful when you need to parse textual data. Parse means to extract a piece of data from its larger surroundings. A couple of examples are extracting the first name out of a full name field or extracting the email address out of a block of larger text.

Exact(original text, comparison text), Exact(original container field, comparison
container field)

Value Returned: Boolean.
The Exact function takes two text expressions or two container fields and compares them. It returns 1 (true) if the two texts or two containers are exactly the same and 0 (false) if they are not. The text must match exactly, as the name implies. This includes spacing and capitalization. The Exact function is very similar to the equal operator. In fact you could duplicate the functionality of the Exact function with the equal operator if you were so inclined, but it would be more difficult.

If case doesn’t matter, you can use the equal operator or use the Upper or Lower functions to convert the string to upper or lower case (see these functions below). The first two calculations follow return 1, but th last one returns 0:

Left(text, number)

Value Returned: text.
Returns the number of characters specified starting from the left. For example, Left( "George Washington", 6 ) returns "George". If the number of specified characters resolves to 0, an empty string is returned, so that Left( "George Washington",0 ) returns "", an empty text string. This can be useful to comment your calculations, giving you the opportunity to place information meant for yourself and other developers to document the purpose and idea behind the calculations.

For instance, you might have a calculation like the following:

This technique works very well when the calculation you are commenting has a text result, but if it has a time, date, or number result, then you need to use another function to convert the text (which is what is returned by the Left function, even if the expression evaluates to an empty string) to the appropriate type. Fortunately, FileMaker provides functions for converting information of one type to another. For example, commenting your tax rate function would look like this:

Notice that even the real portion of the calculation appears inside of the TextToNum function. This isn’t always necessary, and commenting your calculations with the Left function without using one of the text conversion functions may work fine, but there are times when unpredictable results will happen, so it’s best to simply always include the text conversion functions if you decide to comment your calculations in this manner.

Another application of the Left function is to extract the first line of text from a field that has returns in it. This requires the use of the Position function also.

LeftWords(text, number of words)

Value Returned: text.
Returns the specified number of words from the text passed as the first parameter, counting from the left. A little while ago you learned that one of the uses of the text functions is the ability to parse data, such as for extracting the first name from a field that has the full name entered in it. Such a procedure might be needed if you imported data from another source and that other source didn’t have separate name fields. Assuming that the full name field doesn’t also contain salutations such as “Mr.” or “Miss”, the first word of a full name field should be the first name.

If the full name field fulfills the above requirements, then you could extract the first name from such a field with this calculation:


Value Returned: number.
Given a text value, returns the length of the text, the length being the number of characters in the text. Now that FileMaker allows verification of a maximum length built into the field definitions, this function is most useful when the length of a field is needed to pass that number onto another function. Because this is the case, you’ll get a closer look at it once you start creating complex calculations that make use of multiple functions. Length( "George Washington") resolves to 17 because the entire text (including the space) is 17 characters long.


Value Returned: text.
Returns the text given in lowercase letters. Pretty simple and self-explanatory. The calculation below resolves to "george washington":

Middle(text, start, size)

Value Returned: text.
Similar to the Left function, except that it returns the specified text beginning at a starting point and going for the number of characters specified in the size. An interesting note is that with the Middle function you can replace the functionality of the Left and the Right function (which you learn about next).

For example, Left( "George Washington", 6 ) and Middle( "George Washington", 1, 6 ) both return "George". The Right function is a bit more difficult to duplicate, because it needs the Length function as well, but as an example, the function Right( "George Washington", 10 ) and Middle( "George Washington", Length( "George Washington" ) – 10, 10 ) both return "Washington".

Obviously you normally wouldn’t want to use the Middle function when the Right function can perform the job more easily, but the point is that with FileMaker functions, there is usually more than one way to solve a problem. It also demonstrates why it is so useful to know all the functions. If you were familiar with only the Middle function and you needed to perform an action that the Left function could perform, you could do so, but with a more complicated calculation.

Like the Left function, the Middle function is mostly useful for parsing data. For example, using your full name field example, here’s the calculation that would extract the middle name:

MiddleWords(text, starting word, number of words)

Value Returned: text.
MiddleWords is to LeftWords what Middle is to Left. It returns the number of words specified beginning at the starting word in th text specified:

MiddleWords( "When in the course of human events", 2, 3 ) returns "in the course".

PatternCount(text, search string)

Value Returned: number.
Returns the number of occurrences of the search string within the text. PatternCount("Peter Piper picked a peck of pickled peppers", "pi" ) returns 3 because the text "pi" appears three times in the text.

FileMaker doesn’t provide a Contains function. In other words, there is no function that will return a Boolean value based on whether one text contains another text. But because FileMaker interprets nonzero values as true, the PatternCount function can operate as a Contains function. If the value returned by PatternCount for two texts is zero, then the second parameter is not found in the first. If it returns anything but zero, then the second parameter is found in the first. This is most useful when scripting using the If script step. Although you haven’t yet learned about scripting in FileMaker, this script should be simple enough to understand:

Here’s what this script says: if the Text_Block field doesn’t have the “@” character, it can’t contain a valid email address and you need to alert the user to this fact. This script works because if Text_Block contains at least one “@” character, Pattern Count (Text_Block, "@") will be interpreted as true, and the “not” operator will reverse this to false, in which case the Show Message step won’t execute.

Another useful place for PatternCount is with fields that are formatted on a layout as checkboxes or radio buttons. Remember that if a field is formatted as a checkbox, the field actually contains the text of each checked item separated by a paragraph return character. Knowing this, you could find out if the field has been checked on a particular item, or find out how many items were checked using PatternCount.

This would return a 1 if “An Item” was checked (because it can only be checked once so that text can appear in the field only once) and a 0 if it is not. Remembering that items are separated by the “¶” character if there are multiple items, then:

would give you the total items that have been checked. You add 1 to what PatternCount returns because the last item checked won’t have a paragraph mark after it. You use the If and the IsEmpty function because if the field is empty, it would have zero paragraph mark characters, and you need to account for this. Otherwise the calculation would tell you that an empty field has one checked item.

Position(text, search string, start, occurrence)

Value Returned: number.
Between the functions Position, PatternCount, Left, Right and Middle, you can manage pretty much all of your text parsing needs. Position returns the position of the first character that search string matches in text beginning at start and looking for the occurrence indicated by the last parameter.

That’s kind of hard to grasp perhaps, so take a look at some examples of this function in action

The Position function is most often used to pass information as a parameter to another function, such as Middle. For instance, say you have a field called Path that holds the path to a database on your hard drive. This takes the form “C:/ TOPFOLDER / NEXTFOLDER /FILE.FP5”. For some reason, you want to extract the name of the top level folder. Well, given how paths work on PC systems, you know that the top-level folder is going to be between the first and second slashes.

Of course, the first slash should always be the third character in such a text string, so that part is easy. But how to you find out which character the second slash is? Why you use the Position function, of course! Here’s the function that returns the position of the second slash in a given text. For these examples, rather than use a field for the text parameter, you’re going to use a text constant so that you can get hard results back:

Great! You’re on your way to parsing that path (there’s a song in there somewhere).

What do you do with this information that PatternCount has given you?
Well, the text you want to extract is in the middle of the original text, so you need the Middle function. Remember, the form of the Middle function is Middle( text,start, size ). The first two parameters are pretty straightforward. You are extracting from the text C:/TOPFOLDER /NEXTFOLDER /FILE.FP5 and you want to start with the fourth character (the first character after the first slash). But how do you get the size? Well, the number of characters is going to be the position of the second slash minus the position of the first character that you want to extract. So your complete function would look like this:

Of course, this isn’t exactly very useful as it is. This function will return "TOPFOLDER" every time. You want to generalize this function, and all you need to do for that is to replace your text constant with the appropriate field name:


Value Returned: text.
Here’s another simple one, which is good, especially after such a complex and powerful function as Position. All characters that are passed to this function are put into proper name capitalization. In other words, the first letter of every word that is passed to the Proper function will be capitalized:

Replace(text, start, size, replacement text)

Value Returned: text.
This function is another one of those powerful text-parsing functions that FileMaker offers. Using the text as a reference and beginning at the start character and going for the number of characters in size, it inserts the replacement text:

As you’ll see, Replace and Substitute are very similar. Replace is useful when you don’t know what text you are going to replace, but you know (or can calculate) where to start the replacement text and know (or can calculate) how long the text to replace should be. So that you can see the difference right now, take a look at how you would solve the same calculation above using the Substitute function:

To give a possibly useful example of when Replace would come in handy, consider the case where you have a database of contacts with their telephone numbers. You have created the database so that phone numbers are formatted in the form XXX-XXX-XXXX. The telephone company decides to switch area codes because so many people have modems, cellular phones, pagers, and fax machines, and the area codes it is switching are based on the zip code. By searching your database for the zip codes that match the new area code, you could use the Replace function to change the data for all of these, possibly by using a script:

Right(text, number)

Value Returned: text.
Returns the specified number of characters in the text, beginning from the right. After seeing how Left and Middle work, this one is probably pretty easy:

I often come across the need to add leading zeros to number fields. This might be so that when creating a set of databases from within FileMaker, I want to also create the database names and I want them to sort in the correct order, so the database names become “File001”, “File002”, and so on. How can you do this? You can’t simply append two zeros to the beginning of the number, because then you might end up with databases named “File0010”, which wouldn’t sort by name correctly. The answer is, surprisingly, use the Right function:

RightWords(text, number of words)

Value Returned: text.
Just like LeftWords, but uses the number of words parameter to get the words counting from the right:

Substitute(text, search string, replace string)

Value Returned: text.
Substitutes the search string when found in the text with the replace string:

Substitute is an invaluable function for many tasks. Here’s an example from the field: You had a project where the database system collected survey information. When a new survey was created, it needed to be sent to someone via email, and the person receiving the email wanted the survey formatted in a table. The way you decided to solve this was to send them an HTML file with the survey information formatted in a HTML table.

You created the HTML file with special tags wherever field information should go. For instance, when the field holding the email address of the person who filled out the survey was needed, you placed the text “[EmailAddress]” in the HTML file. Once the HTML file had been created, you pasted it into a global text field.

You then set up a calculation field with a text result that used the Substitute function to substitute the actual data for your custom tags. For instance, the portion that substituted the email address in the records looked like this:

To substitute all of the field information, you nested many Substitute functions.To insert the name of the person who filled in the survey, you took what was calculated from the first Substitute function and passed it as a parameter to the next Substitute function:

You kept nesting the Substitute function until all of the fields that you wanted to include were covered. You won’t show the full calculation for this because you did this for about a hundred different fields, so you can see that it got to be quite a complicated calculation. But without the Substitute function to help you, it would have been even more complicated and difficult.

This points out the power of nesting yet again. If there are two different text items that you want to substitute, simply nest one within the other:


Value Returned: date.
Takes the text entered and returns the date that the text represents:

This function could be useful if you have to clean up data that you’ve imported from another system. But another use for it is the commenting of calculations that have a date result and for setting date fields to be empty.

In scripting there will often come times when you want to clear a field of any existing data. For text and number fields, this is straightforward. You simply set the field to an empty string:

But when you try to do the same thing with a date field, you’ll get an error because FileMaker automatically validates to ensure that only valid dates can be entered into date fields. There are two ways around this. One is to use the Insert Calculated Result script step. The downside to this is that it needs to have the field it inserts text into on the current layout. The other option is to use the TextToDate function, passing it an empty string as the parameter:

Set Field [ "DateField", "TextToDate("")"]


Value Returned: number.
Converts the text entered into a number data type. Just as FileMaker ignores any non-numeric data entered into a number field, this function will ignore any nonnumeric data passed to it in its parameter:


Value Returned: time.
Returns the time as passed in the text. Like the TextToDate function, this can be useful in scripts for clearing the data from a time field:

Set Field [ "TimeField", TextToDate( "" ) ]


Value Returned: text.
Returns the text passed to it, but without any spaces that might be before it or after it. If there are no leading or trailing spaces, then it simply returns the text itself:


Value Returned: text.
Converts all of the characters in the given text to uppercase:


Value Returned: number.
Returns the number of words in the given text:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

File Maker Topics