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

# phone formatting

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

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

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

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

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

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

Thank you for the quick reply

I will get onto it after the school holidays

David

• ###### 7. Re: phone formatting

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

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

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