8 Replies Latest reply on Aug 6, 2017 10:02 PM by davidcoutts

    phone formatting

    davidcoutts

      following problem :- I have addresses in UK , CH , D and USA - I have made 4 separate tables for these addresses

      but would now like to make a new Contact database with all addresses together

      I would like to ask if anybody knows a formula for formatting the tel numbers so that all are accepted ?

       

      Swiss           061 811 55 55

      German       0049 5121 1234

      UK               0044 151 400 1234

       

      At the moment I have 3 calculations - CH UK D

        • 1. Re: phone formatting
          keywords

          This can be a whole lot more complex than you are proposing here. The basic approach I use is to set up an auto-enter calc that replaces whatever data the user enters into the field. But, here in Australia at least, there are many more variations to deal with than just your three. I use a Let ( ) function for the calc, and my code runs to c.30 lines in all to: 1. filter the user input so that the balance of the calc can work with consistent data; 2. define the various acceptable phone number formats (mobiles, landlines and various other formats—9 in all); 3. identify whether the data entered falls outside all of the formats; return the correctly formatted number if one of the 9 types, or an error message otherwise—4 types.

          1 of 1 people found this helpful
          • 2. Re: phone formatting
            karina

            Hi David,

             

            Is the complete number in the separate tables, Can you give a sample?

            Do you have a country table?

             

            Greetz,

            Karina

            1 of 1 people found this helpful
            • 3. Re: phone formatting
              davidcoutts

              Hello Karina

               

              Yes the complete number is in the separate tables

               

              No country table (Good idea)

               

              Samples :-

                      

               

                      

               

              Thanks for the help

               

              Greetz

               

               

              David

              • 4. Re: phone formatting
                Menno

                You could create a custom function that simply substitutes your phone-number into the correct format:

                // ###################################################

                // # Name : PhoneFormat

                // # Target : Copy a phonenumber into a format

                // # Recursive : Yes

                // # Parameters : 2

                // # Syntax : CF_PhoneFormat ( PhoneNumber ; Format )

                // # phone# : +31 123 456789

                // # format : ## (###) ## ## ##

                // # Result : +31 (123) 45 67 89

                // #

                // # Created by Menno van Beek - august 2017

                // # Van Beek Zakelijke Software - www.vbzs.nl

                // # mailto: menno@vbzs.nl

                // ###################################################

                 

                 

                Let ( [

                     pnr = Filter ( PhoneNumber ; "1234567890+" ) ;

                     msk = Format & "#########################" ;

                     pos = Position ( msk ; "#" ; 1 ; 1 ) + 1 ;

                     dgt = Left ( pnr ; 1 ) ;

                     lnr = Length ( pnr )

                ] ;

                     If ( lnr > 0 ;

                          dgt &

                          Middle ( msk ; pos ; Position ( msk ; "#" ; 1 ; 2 ) - pos ) &

                          CF_PhoneFormat ( Middle ( pnr ; 2 ; lnr - 1 ) ; If ( dgt ≠ "+" ; Middle ( msk ; pos ; Length ( msk ) - pos ) ; msk ) )

                     )

                )

                You will need a way to determine the correct format, so for that you can use a table like this one:

                 

                CountryFormat
                CH### ### ## ##
                DE#### #### ####
                UK#### ### ### ####
                NL## (###) ## ## ##

                 

                For +31123456789 which is a dutch number, the Format could be retrieved with ExecuteSQL:

                ExecuteSQL ( "SELECT \"Format\" FROM myTable WHERE \"Country\"=?" ; "" ; "" ; "NL" )

                Then you can use the result in the CF like this:

                CF_PhoneFormat ( "+31123456789" ; "## (###) ## ## ##" )

                Result: +31 (123) 45 67 89

                2 of 2 people found this helpful
                • 5. Re: phone formatting
                  fmpdude

                  These kind of pattern matching examples are perfect uses for a Regular Expression. I added RegEx parsing to my FMP applications via a small (tiny, really) separate REST micro-service (several postings with screenshots here on the forum).

                   

                  Here's the RegEx I came up with for your listed phone number variations. Not sure this RegEx handles all your cases, but it matches the three you listed:

                  In my FMP applications, I would just copy this RegEx into a field (could be a Global field) and it would do matches using the REST service. Simple. Fast. Free.

                   

                  Just a couple lines of setup code in FMP. (GetAsUrlEncoded, and INSERT FROM URL.)

                   

                  ----------------------------

                   

                  Here's how your numbers match (or could be validated) In FMP (note bottom example, correctly, does not match):

                   

                  (CLICK ON THE IMAGE IF CUT OFF ON RIGHT)

                   

                  HOPE THIS HELPS.

                  1 of 1 people found this helpful
                  • 6. Re: phone formatting
                    davidcoutts

                    Thank you for the quick reply

                    I will get onto it after the school holidays

                     

                    David

                    • 7. Re: phone formatting
                      karina

                      Hi,

                       

                      Have a nice holiday.

                      Because, people put down a telephone number in a lot of different way's, I adjusted a sample file for you with the a country table.

                       

                      Hope this helps.

                       

                      Greetz,

                      Karina

                      Screen Shot 2017-08-06 at 21.57.05.png

                      Screen Shot 2017-08-06 at 21.59.26.png

                      2 of 2 people found this helpful
                      • 8. Re: phone formatting
                        davidcoutts

                        Hi Katina

                         

                        Many many THANKS for the reply and what a super answer - Thanks for that

                        Holidays start in about 3 hours time - off trekking

                        I will most certainly have a very good look when home again

                        Thank YOU all very much indeed for the super help you gave

                         

                        Slainthé Mhath

                         

                         

                        David