By Andrew Koller
FileMaker offers a plethora of premade tools to organize and display data, so it may come as a surprise to many new developers that there are no native functions built in for phone number formatting.
Lucky for us, this is a quick and easy DIY project, and a great chance to expand your knowledge of functions and script triggers. Read on for a step-by-step walkthrough on how to create and implement this yourself!
Or, if you don’t feel like learning, just scroll down and swipe the code. Nobody’s watching. 👀
Time: 20 minutes
Level: Beginner What you’ll need: a basic understanding of layouts and custom functions
TLDR
- Create a custom function to format the phone number field after some text has been entered by a user:
- In Field Options, set the field as a Calculated value of the custom function and pass in the field itself as a parameter. Make sure that the “Do not replace existing values” is unchecked.
- Create a script for a script-trigger to format the phone number as you type:
- Set script trigger to OnObjectKeystroke, and pass in the field itself as a parameter.
Walkthrough
As with any good solution, the first step is identifying the problem and the desired outcome. In this case, I want the phone number field in my FileMaker app to format a user-entered string (some combination of characters) with a desired format of xxx-xxx-xxxx.
Broken down into bite-sized chunks, the task list reads as follows:
- Accept input from a specified field
- Remove all non-numeric values
- Remove any digits beyond the first ten
- Place dashes in front of the fourth and the seventh values
- Store our calculated value in a specified field
In the Manage Database window, I can automatically modify a field’s contents by setting it to a calculated value. I can take my input in the same step by adding the field that’s being pulled from as a parameter. That would take care of steps 1 and 5, so I can set those aside for now and plan to build around that (don’t worry if this is a little confusing – I’ll do a more detailed walkthrough when it’s time to implement this portion). With this plan in mind, I’ll start by creating a custom function to do the necessary calculations. I’ll call the function “FormatPhoneUS,” and add a parameter to represent the input. I’ll call that parameter “string.”
From there, the first thing to do is remove everything from the string that isn’t a number. The function GetAsNumber may seem like an obvious choice, but this function retains periods (to represent decimal points) as well as numbers, so in this case Filter is a better choice. I’ll set the parameter “string” as my textToFilter, and all digits as the filterText (literally “0123456789”). I’ll need to access the filtered string throughout the function, so I’ll set it to a variable called “format” using the Let function.
Next comes formatting the number. I need ten digits to get the proper format, but what if a user tries to put in a longer string?
As with many scripting exercises, I’ll have to make some assumptions in order to handle potential user error. In this case, I’ll assume that any digits after the first ten were typed in error, and leave them out of the final equation.
Using the Left and the Middle functions, I can set three more variables to capture the three left, three middle, and four right digits. I’ll call those “l”, “m”, and “r”. By using the Middle function rather than Right on the last four digits, I can get the numbers I need without having to trim the string first.
I now have the numbers I need grouped in easy-to-call variables. I’ll use List to concatenate those variables. Since List separates each variable with a carriage return, I can use Substitute to get the dashes I want by substituting the carriage return (”¶”) with a dash (“-”). I’ll set my original “format” variable to the value returned by substitute, and have that be the value returned by Let.
All together, this looks like:
Now back to steps 1 and 5 so I can add this function to my field as a calculated value.
In the Fields tab in the Manage Database window, I’ll select the field that I want to edit (in this example, I’ll use a field named “phoneNumber”). Pressing the Options button brings up more options for the field. Under the Auto-Enter tab, I’ll select the Calculated value checkbox and set that to “FormatPhoneUS ( phoneNumber )” – (I could have also passed “Self” as the parameter to the same effect in this case). I’ll also make sure the “Do not replace existing value” is unchecked, and there we have it! I now have a field that will format phone numbers.
Predicting and handling edge cases: +1
While the phone number formatter technically meets all of the original requirements, there’s a glaring omission: what if someone prefixes a number with a “1”? As it currently stands, if a user enters 1-877-776-6301 for example, the field would populate as 187-777-6630.
That’s not what I want at all! Because the +1 prefix is used to represent the US country code, no US area code can start with “1.” With this in mind, I can make some assumptions. Any time the first number is “1”, I can assume that the user was accounting for the +1, and remove it prior to formatting.
This is easy to achieve with an If statement. If the first number of our filtered variable f is “1”, then I want to return everything in “format” minus the first number; otherwise, I’ll just return “format” as it is.
I’ll use Left to identify whether or not the first number is 1. If it is, I’ll use Right and trim the leading “1” off by simply return the Length of format – 1; otherwise I’ll just return format in its current form and continue the process.
Formatting on the fly
Let’s take this one step further and auto-populate the dashes while information is being entered. This can be achieved through the utilization of scripts and script triggers. The script trigger “OnObjectKeystroke” is what I’ll use in this case. I’ll set it to trigger on the field that I’m formatting and pass in the field’s contents as a parameter.
In the script, I can capture the keystroke that was entered by utilizing Get(TriggerKeystroke). I’ll set a local variable using the Set Variable script step, and set it to the value of Get(TriggerKeystroke). I’ll call that variable $keystroke. From there, I’ll take a page from the function I wrote earlier and filter $keystroke for digits.
I’d like to use an If statement in combination with Filter to determine whether or not a digit was entered; the problem with that is that If interprets “0” as a false statement, rather than as a digit. I can get around this by wrapping the Filter function with IsEmpty. If the filtered result of “0123456789” does not return empty, we know that a digit was entered and the script will move to the next step. The next part seems relatively straightforward: get our script parameter, add $keystroke to the end of it using “&”, run those through FormatPhoneUS, and insert those back into the field using Insert Calculated Result.
But what if our user tries to make edits to the middle of the string? According to this setup, the edit would be tacked to the end of the current field contents, no matter where the user is in the field.
No bueno. One way to get around this is by using Get(ActiveSelectionStart). I can effectively chop our current string at the selection position and use that to capture the left and right portions of the string in two separate variables.
I’ll start by going back to the top and setting a new variable called $parameter to Get(ScriptParameter), and another called $position to Get(ActiveSelectionStart) – 1. I’m subtracting one because Get(ActiveSelectionStart) returns the ordinal position, but the string is indexed at zero.
From there I’ll go back inside of the If statement and set a new variable called $leftString. Using the Left function, I’ll use $parameter for the text, and get $position numberOfCharacters. Setting the variable $rightString to capture the right side looks similar. I’ll use the function Right to get $parameter for Length ( $parameter ) – $position numberOfCharacters.
I can stitch together the three string components as a formatted string by setting a variable (I’ll call it $result) to the value of “FormatPhoneUS ($leftString & $keystroke & $rightString)”. Now I can insert $result using Insert Calculated Result, and boom! I have a field that automatically formats as the digits are being typed.
Accounting for Edits
Since I filtered the keystroke to include only digits, I’ll need to add an exception to allow users to move around the field and make edits. In this case I’d like them to be able to use the backspace, delete, left arrow, and right arrow keys, as well as to use tab to move to the next field. I’ll set this up as an Else If statement and use FilterValues. I can filter according to the unicode values of the keys I want by using Code to interpret $keystroke as the textToFilter, and using List to hold the unicode values I want to accept. If the statement evaluates as true, I’ll set another variable called $validChar to 1 (aka “true”).
Now all I have to do is add a quick If check for validChar, and exit the script if $validChar evaluates as false. And there we have it! A phone number field that automatically formats with each keystroke.