If you have a database, chances are you have a phone number field in there somewhere. Alongside names, emails, and address info, the phone numbers pop up all the time. However, the manner in which phone numbers are entered vary widely. With spaces, dashes, dots, parentheses, plus signs, extension tags, and more. Before you know it, your phone number list looks like a creative kindergarten project.

Although in FM10 and higher, you can assign a script to the phone number field, which when triggered (usually when exiting the field) could take a look at the entered value for the phone number, and clean it up — I personally like to opt to a clever but simple calculated field validation, using a combination of the Let and Case functions. The goal being (for this example) to find any entry that consists of seven or  ten numbers, and reformat it in a consistent fashion (“xxx-xxxx” or “(xxx) xxx-xxxx”). The idea is to do this even if the original phone number has multiple spaces, or non-numeric characters in it, such as brackets, dashes, letters, or periods.

Here’s how it works.

For this example, we’ll be reformatting a field called TelCell. The first step is to identify which characters in the field are numbers, and not text. We will call these characters “Numbers”, and will use a Let function in the beginning of our calc to identify them. This allows us to more easily apply conditional logic on that dataset (Numbers) later in the function. In the example here, we do this by saying, for this function, to let all the characters in this field that are numbers be called Numbers. Or, in Filemaker’s geek speak:

Let ([Numbers=Filter(TelCell; "0123456789"]; the rest of the function)

Now that it knows we just want to base our remaining the part of the function on just the Numbers in the field, we can continue on with that part.

For this bit, we’ll use the Case function. Much like an IF statement, but without the pitfalls of nested scenarios. Basically with a Case statement you can say “IF this, THEN that” time after time after time, until you’ve run out of scenarios you deem important, and close it out with a final ‘THEN that’ at the very end, in case none of the earlier IF scenarios were met. In this example, we have four scenarios we’re looking for — and if we don’t find any of them, we leave the TelCell field contents alone. Here they are, in order:

Case(
PatternCount(TelCell; “@”) = 1; TelCell;
Length ( Numbers )=11 and Left(Numbers; 1) = 1 ; “(” & Left(Right(Numbers; 10); 3) & “) ” & Middle(Numbers; 5; 3) & “-” & Right(Numbers; 4);
Length ( Numbers )=10 ; “(” & Left(Numbers; 3) & “) ” & Middle(Numbers; 4; 3) & “-” & Right(Numbers; 4);
Length ( Numbers )=7 ; Left(Numbers; 3) & “-” & Right(Numbers; 4);
TelCell)

And now, in English:

1. Case Step 1: If the field TelCell has an ‘@’, set the field to its current content (ie, leave it alone)
2. Case Step 2: If the amount of numeric characters in TelCell equals 11 and starts with the number 1; strip the starting 1, wrap parentheses around the next three digits, and add a dash just before the final four digits.
3. Case Step 3: If the amount of numeric characters in TelCell equals 10, then do the same as above, but without getting rid of the starting digit.
4. Case Step 4: If the amount of numeric characters in TelCell equals 7, then just put a dash between the third and fourth digit.
5. Case Step Final: If none of the above criteria are met, set TelCell to… TelCell — in effect, leave it as it was to start with.

So, perhaps not a starter script, but still, a simple one. And useful. By placing it in a Case statement, you can add even more rules to it fairly easily. Just keep in mind that the Case statement works its way from start to finish, one step at a time, and once it meets a step it likes (ie, is true), then it skips the rest.

Also, by putting this small calc in the field’s auto-enter validation settings, you don’t need to run any scripts. Filemaker will run this logic on the TelCell’s contents each time anybody makes a change to the field, automatically. Just remember to uncheck the box outlined in the screenshot above. Otherwise it will only work the first time you enter a value into the field.