by Matt Petrowsky, <iso@iso-ezine.com>

Making text functions work for you
A beginner's guide to basic text manipulation.
BONUS: TEXTFUN.FP3

With just reason, it is the text functions that are my most favorite. Without the ability to manipulate text we would all be at a loss. Extracting data and recomposing it is what data manipulation is all about. If you have a database of email addresses and you want to find out how many come from somewhere.com then you need to be able to parse that information out. If you want to make your database more simple and just have one field display the full name of a person then you need to know how to do that.

Then there is that case where you want to include quotes into the result of a calculation and later you may want to get into even more complex text manipulation like being able to pull values out of a list. In yet another situation you may want to be able to do some text comparisons to see if the data in the record matches the data that you want to see. So it is here that we put these functions to work for you and explain where you get their power by combining them together with one another.

Basic Concatenation

You've heard the word before but you never knew what it meant. Well now you shall. To concatenate two (or more) things together you are basically going to glue them to each other. You can stick things together directly or you can add things in between them. In a FileMaker calculation anything that is within a pair of quotes is a text string that is considered literal, meaning it does not reference a field within the database. All other information is either part of a function, a field or an operator.

In FileMaker the character that performs a concatenation is the ampersand character "&". So when you stick the field First Name with the field Last Name you need to use the "glue" character the ampersand. But the following won't give you what you want.

First Name & Last Name
Result = MattPetrowsky

instead you want to add in a space like so....

First Name & " " & Last Name
Result = Matt Petrowsky

Parsing Information

One of the other basic functions when working with text is that of parsing out text. This is just like having a piece of paper and wanting to cut a circle out of the middle. You want something off of either the end or the middle. In order to do this you first need to know where you are starting and where you want to end up. In most of the functions with FileMaker you need to know where you want to start and for how long you intend to grab information.

Taking the example of an email address that you want the domain name off of you know where you start. You start at the "@" symbol because that is where the domain name starts after that symbol. So using the Position function you first find the position of where the "@" symbol is.

Position(text, search string, start, occurrence)
Position(Email,"@", 1, 1)
The starting position is where you start from in the string of text so starting at character one will work. The occurrence is the place where it occurs in the field that you reference dependent on the number you input. If there were 3 "@" symbols and you wanted to start on the third occurrence then you would use 3 for the occurrence parameter.

Once you have the position you know that you are going to take something from the middle of the email field. So using the Middle function you grab all the text starting from the "@" till the end of the line. The way that you find the size of the amount of text you want to grab is by getting the length of the whole string and subtracting the amount of text starting from the position of the "@" symbol. Because many text functions start on the character that they find you may need to add or subtract by one on some functions to move forward or backward in a text string.


Length(Email) - Position(Email,"@", 1, 1)
Result = the size of the text you want to grab

Then just add all the pieces together into the Middle function.


Middle(text,start,size)
Replace the Start with the Position you created above and replace the size with the Length minus Position just above. And what you end up with is the example below.

Middle(Email, Position(Email, "@", 1, 1) + 1, Length(Email) - (Position(Email, "@", 1, 1)))

This will give you the resulting domain name.

Adding quotes to your calculation

In some cases you may need to add in quotes into a calculation result. This is done by surrounding one pair of quotes with another. This will, however, result in only one quote being created. The math is pretty simple once you get used to it. Four quotes in a calculation will give you one quote and eight quotes will give you two. From there you can put anything on the inside or outside of the quotes.

List Parsing

For the most part I'll leave it up to you to dissect the database file named TEXTFUN.FP3 to really understand this one. For a basic understanding you need to know that most of the time a list is delimited by a return. In FileMaker that character is indicated by the "¶" symbol. If you know how to use the Position function you can look for the position of the carriage return at the end of a value within a return delimited list.

Substitutions

One of the more common things to do with data is to perform a substitution of one piece of data for another. This can be done with either the Replace, which is more demanding for information, or the more user friendly Substitute function. Using substitute is pretty straightforward. One of the better features of substitutions is that you can embed them within each other. Multiple embedded substitutions allow you to substitute out many things from one piece of text. To do this you just enter another substitution function where the previous substitution functions expects a text value.

Comparisons

Yet another text manipulation operation is a comparison. This is where you compare one piece of text to another piece to see if they match up or meet your criteria. In many cases you may end up using a combination of parsing information out of some text and then comparing it to some sort of global value to see if it meets your needs. In still other cases you may need to make multiple comparisons in order to give different results based on those comparisons.

Trial and Error

The best way to become familiar with the text functions is to play with them. Know what each one does and then start to use them together. At one point it will make sense to you and you will find that you will rarely use the functions all alone by themselves and will start to combine them in all kinds of new ways.

Good luck and happy text munging!

## END ##