Phone number issues

[cobrak69]

OK here is my problem:

I am working on a Database that is basically a CRM. I use the Database to consolidate all of my contacts from various reports and sources. (all Excell format importable) The problem is, all of my reports I import from handle phone numbers differently. One report says: "8885551212" another says; "888-555-1212" (which is the way I would like them ALL to read)another does (888) 555-1212 and yet another does "888 555 1212" since there really is no set way the number comes in, formatting is a PITA. PLEASE HELP.

I would like the format to be 888-555-1212, keep in mind I have over 5000 records in the CRM so far and import 10-20 leads daily. I have all the weird formats in the CRM right now, so those will need to be converted too.

BTW: To add insult to injury, I am a Newbie to FM, and I don't understand code or scripting very well, so speak slow and spell it out please.

[comment]

Do all your numbers have 10 digits?

[cobrak69]

Do all your numbers have 10 digits?

They all have 10 numbers. although they sometimes have 10-14 characters counting blanks and "()".

[JerrySalem]

Cobrak69

You have two separate issues to deal with.

1) correct the data already in the database.

2) make sure NEW data gets formatted correctly.

What you should do is go to Brian Dunnings web site and grab one of the custom functions that format phone numbers.

To fix problem 2, make sure that you install the custom function, then use the AutoEnter option on your phone number field to replace the phone number with the formatted phone number.

To fix problem 1,

export two columns from your database. The Key field and the phone number.

do a replace on the phone numbers using the formula in your custom functions definition to fix them all.

import back the phone numbers into your database (Use an update based on the key field, and make sure the checkbox to automatically update fields is OFF, this way the modification date won't change)

There are lots of examples of phone formatting and importing here at FMForums

Good Luck!

Jerry

[Lee Smith]

I would like the format to be 888-555-1212, keep in mind I have over 5000 records in the CRM so far and import 10-20 leads daily. I have all the weird formats in the CRM right now, so those will need to be converted too.

See if this thread helps. PhoneFormat

There are several Threads about formatting phone numbers.

HTH

Lee

[Raybaudi]

I think that a simple calculation field, result text and calc:

Let(

phone# = Filter ( YourPhone#Field ; 1234567890 );

Replace ( Replace ( phone# ; 4 ; 0 ; "-" ) ; 8 ; 0 ; "-" )

)

will do the job.

[comment]

1. What Daniele said.

2. I would make it a calculation field at first. Check the results, and if you are satisfied, change the field's type to Text, with auto-entered calculated value (replacing existing value) using the same formula. Check again, and if OK, you can delete the original field.

When importing new data, make sure to turn on the auto-enter option.

[David Jondreau]

They all have 10 numbers. although they sometimes have 10-14 characters counting blanks and "()".

No one's entering things like:

212 555-NYC1

212 555-1212 x34

1 212 555-1212

+44 0999 999 1212

Those'll trip you up.

[Rick Whitelaw]

No one's entering things like:

212 555-NYC1

212 555-1212 x34

1 212 555-1212

+44 0999 999 1212

Those'll trip you up.

Agreed. Another potential problem is "+" and a country code if dealing with overseas numbers. I use:

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 );input )

RW

EDIT: Just saw that the above is NOT dealing with overseas numbers properly. Will repost.

EDIT 2: Changed it so overseas numbers work. However, due to the variety of lengths of country codes with area codes (or not) plus the fact that not all local numbers are 7 digits in all countries, the overseas numbers must be typed in properly, i.e. they're not formatted by this function. As well, this function formats numbers as (xxx) xxx-xxxx. Easily changed . . .

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 )

[cobrak69]

Thank you everyone for your input. It works perfect. This forum is awesome.