Dial Into That Phone Format

By Jennifer Neighbors, Senior Consultant

One of the most constant concerns of those who create lots of databases is the issue of phone number formats. Users like it if phone numbers are look consistent and are appropriate for their region. Making this a challenge is that users may enter the number using a variety of styles and add spaces, dashes, periods or plus signs as they like, which creates a mixed bag of phone number results!

Not long ago I was lucky enough to be given a tool by my colleague David Gyenes that comes in especially handy for this. It’s a function that strips everything but digits from anything a user enters. For example if the name of your phone number field is “Phone”, then this function will return only the numbers from anything the user might enter into that field:

replacex(Phone, “\D”, “g”, “”)

I understand how this works only very imperfectly, so I’m hoping David will break that down for you himself next week!

Here’s how I use it to make phone number formatting simple: In the trigger after update function of the phone field, I store the results of the function replacex(Phone, “\D”, “g”, “”) in a variable. I then use the “length” and the “substr” functions to transform the variable into the format I want. For example, I may want to return the long form of a French telephone number regardless of whether the user enters the long form (with international dialing code), or the short form (the way a person inside France would call) of the number. Using the “length” function to measure the number of digits in my variable, I can see which version of the number was entered and pick out the numbers I want to reuse. Then I can easily use Ninox’s string operators and the “substr” function to form the phone number as I want it to appear in the Phone field and put it there. Wonderfully, my phone number fields accept any style of input and then appear in exactly the manner I want as the user exits the field!