1 2 Previous Next 16 Replies Latest reply on May 12, 2015 4:19 AM by TorstenBernhard

    Formatting phone numbers when dealing with international companies?

    jgalt

      I am working on a solution that has contacts with phone numbers from around the world. About 80% of them are US phone numbers and the rest of them are international. Currently there is no consistency to how the numbers are entered. This is what I am trying to clean up.  I also want to implement a system moving forward so there is a uniform appearance to the numbers.

       

      I have found some great custom functions that can handle reformatting phone numbers but how do you identify and deal with international numbers? One of the main problems that I am running into is that when user is entering the phone number I don't yet know the country of origin.

        • 3. Re: Formatting phone numbers when dealing with international companies?

          Country codes identify the country. The US is 1 that's why you see 1-800-234-5678.

           

          When you dial the formatting is irrelevant. Its only relevant to humans who want to see it parsed. And the phone number is irrelevant if your phone dials it for you.

           

          You could just hide the phone number and use a phone icon and have it dial the number... 

           

          Each phone number should have a country code and that would be the place to start. Get a list of country codes and check the first few digits against that.

           

          Next of course would be to force your users to enter the info for you and not be so sloppy, lazy and in a hurry... That often works... 

           

          Make a hierarchal set of popups:

           

          Country to area codes to phone numbers.

           

          Select a country then pick the area code then enter the phone number. Make the country code and area code mandatory.

           

          More than half your work is done there.

           

          If you give the users the freedom to enter what they want, you might as well go jump off a cliff...

           

          Required Fields:

          Country

          Country Code

          Area Code

          Phone Number

           

          Add other fields as you think of them.

           

          The big problem is that there is no universal phone company and many too many small phone companies still exist that have their own non-standard ways of doing things mostly so they can charge their users.

           

          Like I said, formatting is irrelevant except for human consumption.

          • 5. Re: Formatting phone numbers when dealing with international companies?
            Benjamin Fehr

            You like to set a script trigger 'on-leave' for this field.

            Script would be 2 steps action:

            - replace/delete all spaces and special characters ( - # / ) first

            - reformatting number according to the country of it's origin

            • 6. Re: Formatting phone numbers when dealing with international companies?
              erolst

              efficientbizz wrote:

              You like to set a script trigger 'on-leave' for this field.

               

              You mean “OnObjectExit” – but what is wrong with an auto-enter calculation?

              • 7. Re: Formatting phone numbers when dealing with international companies?
                Benjamin Fehr

                You mean “OnObjectExit”

                apologies. I still work on a german version. "BeiObjektVerlassen" …

                 

                but what is wrong with an auto-enter calculation?

                I wouldn't recommend, if the solution is built on a data-separation model. With auto-enter, the formula will be placed in the users data-file where you couldn't apply any further changes.

                • 8. Re: Formatting phone numbers when dealing with international companies?
                  Fred(CH)

                  As an anecdote, i  think it is better to trip OnObjectSave.

                   

                  But Waow, excellent argument.

                   

                  I definitely am not close to use separation model, since i didn't know this limitation…*

                   

                   

                  Edit : * - > i didn't thought about this limitation. It is obviously due to the separation model concept itself, but more i think about it, less i want to try it..

                  • 9. Re: Formatting phone numbers when dealing with international companies?
                    keywords

                    Jack's answer contains a smattering of inefficiencies ("Get a list of country codes and check the first few digits against that") and a couple of escapes from thoughtful programming at the expense of users ("force your users to enter the info for you and not be so sloppy, lazy and in a hurry" and "If you give the users the freedom to enter what they want, you might as well go jump off a cliff").  The thing is, there is more than one perfectly acceptable way to write phone numbers, and users should not be forced to enter them in a specific and unfamiliar way just because "the program" can't cope with anything else—that is one of my pet hates.

                    It is perfectly acceptable to write a phone number with no spaces, with spaces, with hyphens instead of spaces, with brackets around the area code, or not. A couple of years ago I set out to address this very issue for a client in Melbourne, Australia in a way that would allow for these differences, but trap for unacceptable data such as letters, carriage returns, symbols, etc. With a bit of thought—and a fairly long Let() calculation—I achieved what I felt was an acceptable solution, which I share with you here:

                     

                    // PURPOSE: format phone numbers in the standard INTERNATIONAL dialing format

                    // © Keywords; version 3, 11 May 2015

                    // NOTES:

                    // 1. the above calculation applies standard formatting to any telephone number entered

                    // 2. this version is configured for Victoria, Australia

                    // 3. it recognises and allows for various legitimate formats users may use when entering phone numbers

                    // 4. it filters out carriage returns, but delivers a user error message if letters are entered, or if there are too many or insufficient numbers for the type of phone number entered

                    // 5. it takes account of the fact that phone numbers in NE Victoria which begin with 6 use an 02 area code

                    // 6. it delivers a user check message if the numbered entered is not recognised as one of the standard types listed

                    // 7. result must (1) contain numerals only, and (2) be in the form: +61 xxx xxx xxx for mobiles; +613 xxxx xxxx for Victorian landlines; +61x xxxx xxxx for interstate landlines; 1800 xxx xxx; 1300 xxx xxx; 13 xx xx

                     

                    Let ( [

                    rawData = // this is where you field data will go

                    ; filtered = Filter ( rawData ; "0123456789¶ ()+-–—" )

                    ; numerals = Filter ( filtered ; "0123456789" )

                    ; numplus = Filter ( filtered ; "0123456789+" )

                    ; length = Length ( numerals )

                    ; letters = filtered ≠ rawData


                    ; ausmobile = Left ( filtered ; 4 ) = "+614"

                    ; ausland = Left ( filtered ; 3 ) = "+61" and not ausmobile

                    ; one800 = Left ( numerals ; 4 ) = "1800"

                    ; one300 = Left ( numerals ; 4 ) = "1300"

                    ; one3 = Left ( numerals ; 2 ) = "13" and not one300

                    ; mobile = Left ( numerals ; 2 ) = "04"

                    ; vicland = Left ( numerals ; 1 ) = Filter ( Left ( numerals ; 1 ) ; "985" ) and not ausmobile and not ausland

                    ; vicNE = Left ( numerals ; 1 ) = Filter ( Left ( numerals ; 1 ) ; "9865" ) and not ausmobile and not ausland

                    ; interstate = Left ( numerals ; 1 ) = "0" and not mobile


                    ; long = one800 and length > 10 or one300 and length > 10 or one3 and length > 7 or mobile and length > 10 or ausmobile and length > 11 or ausland and length > 11 or vicland and length > 8 or vicNE and length > 8 or interstate and length > 10

                    ; short = one800 and length < 10 or one300 and length < 10 or one3 and length < 7 or mobile and length < 10 or ausmobile and length < 11 or ausland and length < 11 or vicland and length < 8 or vicNE and length < 8 or interstate and length < 10

                     

                    ; result =

                        Case (

                                  letters ; "INCORRECT: " & Substitute ( rawData ; "¶" ; "" ) & " —must contain numerals only"

                                ; long ; "INCORRECT: " & numerals & " —too many digits"

                                ; short ; "INCORRECT: " & numerals & " —insufficient digits"

                                ; ausmobile ; Middle ( numplus ; 1 ; 3 ) & " " & Middle ( numplus ; 4 ; 3 ) & "-" & Middle ( numplus ; 7 ; 3 ) & "-" & Middle ( numplus ; 10 ; 3 )

                                ; ausland ; Middle ( numplus ; 1 ; 4 ) & " " & Middle ( numplus ; 5 ; 4 ) & "-" & Middle ( numplus ; 9 ; 4 )

                                ; one800 ; Middle ( numerals ; 1 ; 4 ) & "-" & Middle ( numerals ; 5 ; 3 ) & "-" & Middle ( numerals ; 8 ; 3 )

                                ; one300 ; Middle ( numerals ; 1 ; 4 ) & "-" & Middle ( numerals ; 5 ; 3 ) & "-" & Middle ( numerals ; 8 ; 3 )

                                ; one3 ; Middle ( numerals ; 1 ; 2 ) & "-" & Middle ( numerals ; 3 ; 2 ) & "-" & Middle ( numerals ; 5 ; 2 )

                                ; mobile ; "+61 " & Middle ( numerals ; 2 ; 3 ) & "-" & Middle ( numerals ; 5 ; 3 ) & "-" & Middle ( numerals ; 8 ; 3 )

                                ; vicland ; "+613 " & Middle ( numerals ; 1 ; 4 ) & "-" & Middle ( numerals ; 5 ; 4 )

                                ; vicNE ; "+612 " & Middle ( numerals ; 1 ; 4 ) & "-" & Middle ( numerals ; 5 ; 4 )

                                ; interstate ; "+61" & Middle ( numerals ; 2 ; 1 ) & " " & Middle ( numerals ; 3 ; 4 ) & "-" &  Middle ( numerals ; 7 ; 4 )

                                ; "UNRECOGNISED: " & rawData & " —check"  )  // default message

                    ] ;

                    result

                    )


                    Obviously if you have to deal with lots of international numbers you would need to adapt the above to suit this reality—for example, require entry into a country field, but your solution should then supply the required country code; you should also then be able to supply a list of possible area codes for that country.


                    Hope that helps some.

                    • 10. Re: Formatting phone numbers when dealing with international companies?
                      erolst

                      efficientbizz wrote:

                      I wouldn't recommend, if the solution is built on a data-separation model.

                      Good point to keep in mind, but I would cross that particular bridge when I come to it.

                      efficientbizz wrote:

                      I still work on a german version. "BeiObjektVerlassen" …

                      The German version of On(Object)Leave would be “BeiUrlaub”, or ”BeiErlaubnis“, depending on context – which might be hard to trigger on …

                      • 11. Re: Formatting phone numbers when dealing with international companies?
                        erolst

                        keywords wrote:

                        The thing is, there is more than one perfectly acceptable way to write phone numbers, and users should not be forced to enter them in a specific and unfamiliar way just because "the program" can't cope with anything else—that is one of my pet hates.

                        I agree – IMO, there is no such thing as a user error, there are only programming errors. (Some) users may indeed be sloppy and lazy, but that's just part of the human nature, and not an attitude we should subscribe to in our own work.

                         

                        Bringing order into the inherently unordered is one of the challenges that make coding such fun (if you succeed, that is …)

                        • 12. Re: Formatting phone numbers when dealing with international companies?

                          It's been a long time since someone has began a long reply that began with a negative about my post and then ended up agreeing with me. 

                          Jack's answer contains a smattering of inefficiencies ("Get a list of country codes and check the first few digits against that")

                          and then at the end...

                          Obviously if you have to deal with lots of international numbers you would need to adapt the above to suit this reality—for example, require entry into a country field, but your solution should then supply the required country code; you should also then be able to supply a list of possible area codes for that country.


                          Which is what the question involved, phone numbers from around the world... 


                          The function is quite impressive and I had not considered putting the ; at the beginning of the line but that is quite efficient and resolves several issues. I will adapt it in the future. I keep forgetting to put one at the end of my statements.

                           

                          I find the use of a year popup by online pharmacy ordering quite annoying since my year is way down at the bottom. But I know that if the page allowed manual entry of numbers rather than popup selection that many users would enter the wrong data.

                           

                          Since an address is most likely entered before a phone number, the country could be determined from the address.

                           

                          The formatting of phone numbers has nothing to do with the ability to dial the phone and is only for the convenience of the viewer. Most people would not be able to remember 18004562345 or to look at it and dial it. I find it quite aggravating when this occurs. But 1-800-456-2345 or 1.800.456.2345 or (800) 456-2345  are easier to look at and manually dial.

                           

                          Using a trigger for each keystroke and displaying a formatted result above is an idea to consider and let the user adjust the numbers in the field to produce the correct displayed number.

                           

                          Also providing fields for the types of phones encountered such as home, office and mobile since each might have different requirements.

                          • 13. Re: Formatting phone numbers when dealing with international companies?
                            nicolai

                            OK, we have on one side jackrodgers suggesting that an end-user can not be trusted and has to be forced or trained to use the system on a particular way as it was intended by the developer.

                             

                            On the other hand we have keywords and erolst suggesting that the end-user should have freedom and flexibility in the system usage and the developer has to programm the ability to cope with this.

                             

                            Please feel free to correct me if I got it wrong.

                             

                            This topic has always fascinated me.  Is this worth starting a separate discussion, as we are getting a bit off topic? Is anyone interested, as it is not really technical but more of a  philosophical topic?

                             

                            Nicolai

                            • 14. Re: Formatting phone numbers when dealing with international companies?

                              I worked on site with a client for 20+ years and I had to:

                               

                              Put up three warning dialogs when one user wanted to delete a record or accidentally clicked the delete in order to drag her out of her key typing frenzy and automatic responses. Do you want to Delete, Do you REALLY want to delete, I will not recover this for you....

                               

                              Another user found a way to use the Control key instead of Shift key and Control+M made a search fail, I was blamed.

                               

                              Other users found ways to make what should have been a single entry field into a multiple entry field so that they could claim they worked on something rather than come to me and say, we need this expanded... One of them was fired when the owner decided that he hadn't been doing enough work (without asking me to verify his results).

                               

                              In those many years I also made a lot of typos and had to unprogram them...

                               

                              They were terribly in love with FileMaker's messy date fields and its interpretations. What a messs.

                               

                              When I create something that worked like FoxBase's date fields  dd mm yyyy, that three number fields, it took a while for them to accept that typing 01 01 2011 was not more difficult than typing 1/1/2011. They just had to adapt.

                               

                              The same with phone numbers and social security. I broke those into 3 fields and as soon as they correct number of characters were entered, I tabbed out of the field and into the next one.

                               

                              In those three instances I did NOT have to worry about custom functions to parse the mess, the design of my fields eliminated that problem.

                               

                              When I changed from the FileMaker date field to my 3 number fields, there was resistance, but it died as soon as people became aware that they weren't creating any bad dates and the boss wasn't standing over they shoulder while they tried to find something they had miss typed.

                               

                              No more fussing with / or , or - just type the numbers, I'll do the work.

                              1 2 Previous Next