10 Replies Latest reply on Jun 5, 2015 10:07 AM by realgrouchy

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

    realgrouchy

      Title

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

      Post

      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
                                                                                                                                                                                                                                                                                                                                                     
      InputOutput
                     

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

                     
      123-456-7890
                     

      4567890
                     456-7890
                     (etc.)

                     
      123-456-7890
                     

      123-456-7890 (cell)
                     456.7890 - sister

                     
      123-456-7890
                     [only permits phone numbers and extensions]
      123-456-7890 x123
                     123-456-7890x123 (home)
                     123-456-7890
                     456-7890 ext. 123
                     456-7890/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"]
      011-01-234-567-890
                     (21)-34
                     2134 poste 3(11)
                     [i.e. anything that isn't 7 or 10 digits before or without an extension]
      011-01-234-567-890
                     (21)-34
                     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).

       

      Cheers,

       

      - RG>

        • 1. Re: Some code to auto-correct your phone number field based on nearly any situation...
          realgrouchy

          Sorry for replying to my own post... for posterity, here is what I started out with tonight! Far less versatile!

           

                                                           
                         

          Let ( [ e = Position ( Self ; Left ( Filter ( Self ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 1 )  ; 1 ; 1 ) ;
                                   P1a = If ( e ; Left ( Self; e -1 ) ; Self ) ;
                                   P1 = Filter ( P1a ; 1234567890 ) ;
                                   P2 = If ( e ; Right ( Self ; Length ( Self ) - Length ( P1a ) ) ) ] ;
                                  Case ( Length ( P1 ) = 10 ; Left ( P1 ; 3 ) & "-" & Middle ( P1 ; 4 ; 3 ) & "-" & Right ( P1 ; 4 ) ;
                                             Length ( P1 ) = 7 ;"(209) " & Left ( P1 ; 3 ) & "-" & Right ( P1 ; 4 ) ;
                                             P1
                                           ) // case
                                  & If ( not IsEmpty ( P2 ) ; " " & P2 ; "" )
                                 ) // let

                         

          - RG>

          • 2. Re: Some code to auto-correct your phone number field based on nearly any situation...
            projay

            Hi Rg.....Can this work...... LocalAreaCode = $$areacode

            -J

             

            • 3. Re: Some code to auto-correct your phone number field based on nearly any situation...
              realgrouchy

              Good idea. Since I was hardcoding in the area code for my own purposes, I didn't spend to much time thinking about fancier ways to make it more global.

              - RG>

              • 4. Re: Some code to auto-correct your phone number field based on nearly any situation...
                bigtom

                As well if you know the city / state or zip code I think there is a way to get the area code you need via URL from the web or you could have a table for that I guess. If you have a table with all known prefix you can get the area code easily.

                Expanding to phone numbers in other countries sounds exciting too.

                • 5. Re: Some code to auto-correct your phone number field based on nearly any situation...
                  projay

                  Hello RG, just a question on the phone calc.
                  It seems to only work if it is under the "Auto-Enter" calculated value.
                  For example for my invoices my phone numbers is a "Looked-up value" from Customer Info so its not typed in directly.
                  So my thinking is if I have your phone calculation under the "Validation" tab "Validated by Calculation" it should change my phone digits if I edit the phone field...but nothing happens when I change the phone number. Any thoughts on this? Thanks for your help. -J

                  • 6. Re: Some code to auto-correct your phone number field based on nearly any situation...
                    philmodjunk

                    Validation rules only cause error messages to appear, they don't alter data. You need to use the auto-enter method. If your stored phone number in the look up table and the phone number produced by the auto-calc match, you'll still be able to look up values. Another approach is to set up calculation fields (or text fields with auto-enter calcs) that store these numbers without any formatting that you use for look up purposes to look up the formatted version.

                    Note: It's been my experience that phone numbers are not the best method for uniquely identifying people. Not only do people have multiple phone numbers, people give up phone numbers and then other individuals receive them from the phone company. This is less common than it once was, but I recall searching a contact database for duplicate phone numbers just a year or two ago and pulled up multiple contacts with the same phone number simply due to customers not telling us that they had a new phone number or due to our users failing to update the data when they did.

                    • 7. Re: Some code to auto-correct your phone number field based on nearly any situation...
                      RickWhitelaw

                      I use this as an auto enter (replace existing values). Input is self.

                      Phone Format(input)

                      Case(  Length ( Filter (input ; "0123456789" ) )    ≠  10  and Left (input; 1 ) ≠ "+"; "Enter 10 digits!";Length ( Filter (input; "0123456789" ) )   = 10; "(" &  Left ( Filter ( input; "0123456789" ) ; 3 ) & ") " & Middle ( Filter ( input; "0123456789" ) ; 4 ; 3 ) &"-" & Middle ( Filter ( input;"0123456789" ) ; 7 ; 4 );Left (input; 1 )  =  "+";input;input )

                      Edit: now that I examine it more closely, a little more code is need at the end to format international numbers. However, they can be typed in.

                      • 8. Re: Some code to auto-correct your phone number field based on nearly any situation...
                        projay

                        Thanks for the quick reply.  So when I create a new order I have a phone field where I check marked the "Lookup-value"... so that it gets the customers phone value and places it in the orders phone field and it is formatted correctly (123) 456-7890 in the orders.

                        So if for example the customer gives me another number and I input that number in this field then the number is no longer formatted as above.

                        Since the checkmark is placed for "Lookup-value" you no longer have the option of using the "Calculated Value" under "Auto-Enter" tab.

                        Hopefully that makes sense.

                        • 9. Re: Some code to auto-correct your phone number field based on nearly any situation...
                          RickWhitelaw

                          Why use LookUp? If the tables are related why not have the phone number field on the layout and use whatever custom function (self) you need?

                          • 10. Re: Some code to auto-correct your phone number field based on nearly any situation...
                            realgrouchy

                            I inherited a non-local database (i.e. no default area code), and am reviewing the existing phone numbers for formats. If they all

                            I see that the existing entries in the new dataset includes some numbers in the formats listed below. I haven't tested the code in this database and it's been a while since I created or used the code in the OP, so some of these formats may not be handled as desired. (they might, but I hadn't listed them above)

                            North American phone numbers: (I can't believe I missed the first two!)

                            1(234)567-8901

                            1 234 567 8901

                            (123)- 456-7890

                            123) 456-7890

                            123-456-78-90

                            123-456-VITO(8486)

                            123-456-VITO

                            cell 123-456-7890

                            (c) 123 456 7890

                             

                            Extensions:

                            123-456-7890 # 4

                            123-456-7890*2

                            123-456-7890, 1234

                            123-456-7890 x.123

                            123-456-7890 ext: 1234

                            123-456-7890 local 1234

                             

                            International numbers: (my examples above only show hyphens, not spaces)

                            +44 01234567890

                            44-123-456-7890

                            64 9 123 4567

                            12345-6789-0123

                             

                            I was hoping to quickly implement the formula to tidy up the phone numbers in this database, but I'll have to save that for a slower day...

                            - RG>