AnsweredAssumed Answered

Some code to auto-correct your phone number field based on nearly any situation...

Question asked by realgrouchy on Jan 31, 2015
Latest reply on Jun 5, 2015 by realgrouchy


Some code to auto-correct your phone number field based on nearly any situation...


A couple of months ago, I found some code online that I put in the "Auto-Enter > Calculated Value" part of my phone number fields, which I edited a bit. I don't remember where I found it (if I did I'd be submitting this there) or how much I had added (but the sample code had area code 209. I think all it did was format the area code and phone number, and I modified it at the time to use it for extensions and my preferred phone number format). I didn't find any recent threads in this forum so I'm posting this as a new one to share it.

Anyway I've just spent a bunch of time improving the code I had previously modified and now it works in a LOT of scenarios (North American phone numbers only, sorry!) and I've modified it to be easily adapted by non-expert users. Feel free to use it in any of your projects, modify it all you want, etc., no attribution required.

It will:

  • always format a 7 or 10 digit phone number in ten-digit format separated by dashes
  • allow you to enter international phone numbers (if they are not 7 or 10 digits long)
  • always format the extension as x123 (including in international phone numbers)
  • strip out anything but the numbers in the extension

               (123) 456-7890
               123 456 7890
               (or any combination of the above, etc.)




123-456-7890 (cell)
               456.7890 - sister

               [only permits phone numbers and extensions]
123-456-7890 x123
               123-456-7890x123 (home)
               456-7890 ext. 123
               (etc.; includes any variant of the phone number)
123-456-7890 x123
               [Always returns only the numeric portion of the phone extension, separated from the phone number by " x"]
               2134 poste 3(11)
               [i.e. anything that isn't 7 or 10 digits before or without an extension]
               2134 x311
               [triggers validation warning]


I'm not sure if there is an equivalent to the [code] tag in bbCode, so I'll just enter my code here:

As you'll note, it includes commented-out code to use in the validation script, including an error message to use.


/* This is a calculation to enter in the "Auto-Enter Calculated Value" options of the field definitions, to auto-format the phone number and extension in a North-American context based on nearly any format, and allowing override if an international number is used.
               This originally came from some code I found somewhere online (it had area code 209), but I've adapted it almost beyond recognition, including by adding much documentation (commenting) to help navigate it.


It returns 7 or 10-digit phone numbers in the format 123-456-7890 x1234 no matter how the number or extesion is entered. If it is not 7 or 10 digits it returns the entered string as is, with the properly-formatted extnsion, and warns the user that the number entered is not a valid North American phone number.


The code doesn't quite work in three unlikely scenarios (but will still trigger a validation error to warn the user): First, if the extension is not denoted with a letter, slash, or asterisk, the entire entered string will be returned as-is. Second, where a string that is not 7 or 10 digits long is followed by a text-only string, such as "1234 (cell)" returns "1234 (". Both of those cases are unlikely and if the validation code at the bottom is used then the user will be prompted in either of these cases. Third, if a letter (or slash or asterisk) comes before the end of the phone number, so 123h456-7890 becomes 123 x4567890.
               // To change how the extension is formatted, edit " x" in the first variable
               // To change the local area code, change "123" in the second variable
               // To change the phone number format you'll have to tweak the results in the first two lines of the "Case" function.


Let (


               // change 123 below to update the area code.
                         LocalAreaCode = 123 ;


// change what comes between the phone number and the extension, e.g. for "123-456-7890 ext. 123", replace " x" with " ext. " (with spaces and quotes) (default format is 123-456-7890 x123)
                         xFormat = " x" ;


// e is a variable to denote where the first letter is (if any) in the phone number field (e.g. extension)
                         e = Position ( Self ; Left ( Filter ( Self ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ\/*" ) ; 1 )  ; 1 ; 1 ) ;


// P1a is a variable that returns anything before the first letter (i.e. should be the phone number only), otherwise the whole field if no letters
                         P1a = If ( e ; Left ( Self; e -1 ) ; Self ) ;


// P1 is a variable that filters the phone number to strip out anything that isn't a number
                         P1 = Filter ( P1a ; 1234567890 ) ;


// P2a is a variable that assumes anything after the phone number is the extension and returns only the numeric portion
                         P2a = If ( e ; Filter ( Right ( Self ; Length ( Self ) - Length ( P1a ) ) ; 1234567890 ) )  ;


// P2 is a variable that returns the properly formatted extension, or nothing if there are no numbers in the extension.
                         P2 = If ( IsEmpty ( Filter ( P2a ; 1234567890 ) ) ; "" ; xFormat & P2a )


// Case evaluates whether the length of the numeric portion before any letters is 7 or 10 digits and parses the phone number accordingly, appending the extension separated by a space.
                        Case ( Length ( P1 ) = 10 ; Left ( P1 ; 3 ) & "-" & Middle ( P1 ; 4 ; 3 ) & "-" & Right ( P1 ; 4 ) & P2 ;


// Adds local area code if not entered. Remove this line if you don't want to convert 7-digit numbers with a common area code.
                                   Length ( P1 ) = 7 ; LocalAreaCode & "-" & Left ( P1 ; 3 ) & "-" & Right ( P1 ; 4 ) & P2 ;


// (Case continued) If the numeric portion isn't 7 or 10 digits long, it returns the number as-is with formatted extension. (Validation should throw a warning to the user in case they erroneously entered the wrong number of digits)
                      P1a & P2


                  ) // case


        ) // let


               /* Use the following in the validation calculation for this field:

               //See Auto-enter calculation for more notes on how this works and what it does
               Let (
               [ e = Position ( Self ; Left ( Filter ( Self ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 1 )  ; 1 ; 1 ) ;
                         P1a = If ( e ; Left ( Self; e -1 ) ; Self ) ;
                         P1 = Filter ( P1a ; 1234567890 )
                         ] ;
                        ( Length ( P1 ) = 10 ) or ( Length ( P1 ) = 7 )
               //Use this as custom error text in validation: "Phone number is not a valid North American phone number.  Use anyway? (Click no to edit)"



To use it:
- Go to the menu option File > Modify > Database, select the "fields" tab, select the desired table in the dropdown, then select the desired field you want to apply it to.
- Near the bottom-right of the "Manage Database" dialog, ensure the "Type" is set to "text", and click "Options". In the first tab ("Auto-Enter"), click the "Specify..." button next to "Calculated value".
- In the text box of the dialog that pops up, copy the entire text above and paste it into that box. Replace the "123" in the line LocalAreaCode = 123 with your preferred default area code.
- Select everything between the /* and */ lines near the bottom and copy it (you can remove it from this box or leave it in without problems). Click "OK"
- Still in the Options dialog, click the "validation" tab, then select the "Specify..." button next to "Validated by calculation"
- Paste the text you copied from above into the calculation box. Note that the final line includes a sample error message to use. Select and copy that message (without the quotes), copy it, then click "OK".
- Still in the Options dialog "validation" tab, paste the selected text into the box at the bottom and select "Display custom error message if validation fails".
- Make sure the "Allow user to override during data entry" checkbox is selected at the top of the dialog
- Click "OK" to close the Options dialog
- Click "OK" to close the Manage Database dialog
- Test with some sample entries and you're done!


I hope it saves you a lot of headaches and makes it easier for your users. So many phone number validation methods force 10-digit numbers which doesn't let you include extensions or international phone numbers, so this gives your users much more flexibility (while still maintaining a highly consistent formatting).




- RG>