The past few posts have covered an array of text parsing functions. There are countless scenarios where you may need to manipulate text in FileMaker, but the two I most often encounter are data migration and data grooming. I don’t know how many times I’ve been tasked with taking a free-form telephone number field, laden with inconsistent information and formatting, and cleanly breaking it out into discrete fields for the area code, phone number, and extension. When you have to clean up sloppy data like that, the Left, Right and Middle functions I’ve been writing about just aren’t sufficient for the job. Adding the Length and Position functions to the mix brings significantly more power.
Length has just one parameter for some text or the name of a field containing text. The result you get is the number of characters that appear in that text. FileMaker counts every character including punctuation, returns, and spaces.
The Position function is a bit more involved. The first of its four parameters is text. In the above example our text is the word “HELLO”. The second parameter is a search string, or more plainly put, it’s whatever you’re looking for. We’re looking for the letter L in this example. The third parameter specifies where in the text FileMaker should start scanning for that search string; the number 1 here tells FileMaker to start at the very beginning of the text. The final parameter is the occurrence. Because your search string can appear more than once in the text, you must specify which match you’re seeking. My text has two Ls and by putting a number 2 in the last parameter, I’ve told FileMaker to give me the position number of the second occurrence of the letter L. In this case, the second L is the fourth character in the text so the Position function returns a 4.
Now that we’ve got the tools, here’s how we can put them together for some tricky data grooming. I’ve got a set of inconsistently formatted phone numbers from which I need to extract the extensions. Of course, not every extension is the same length and one even has a hyphen. The only thing I can count on here is that extensions are preceded by a letter x and are the rightmost information in the field. I need to construct a calculation that will provide me with all the characters between the x and the end of the field.
Since we know the extension will always be the rightmost part of the phone number field, we can use the Right function with the phone number field as it’s first parameter. The second parameter of the Right function (line 3 in the example image above) is the number of characters to return. This is where we have to figure out how many characters each extension is. So we start with the Length function to determine how long the entire phoneNum field is. Next we use the Position function to get the location of that letter x at the start of each extension number. Subtract the Position number from the Length and you’ll get the number of characters in the extension.
Taking the third phone number in the example below, Length ( phoneNum ) is 22 and Position ( phoneNum ; “x” ; 1 ; 1 ) equals 18. Subtract 18 from 22 and to get 4- the number of characters in that extension.
Multi-step examples like these can be a bit much to try to comprehend just by reading. So build yourself a little example database with the fields and calculations presented here and you’ll see it all come together. It’s another example of how very simple functions can be combined in powerful and complex ways.
Subscribe to our blog feed