Jump to content

Phone number issues


This topic is 4411 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

This topic is 4411 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.