Can I make a phone field re-configure itself? 1

[Kurkus]

Can i make a phone field configure itself into a fixed format?

example: user inputs 517.555.5555

and I would like the database to change it to (517) 555-5555

can this be done and if so, please show an example script

thanks in advance

Kurk@mcca.org

 

[JeanW]

You don't need a script.
A calculation field will do.


Solution 2.

You want the format to be (XXX) XXX-XXXX, but you have entered (or imported) the data in ALL KINDS of different ways:

111 555-1212
(111) 555-1212
111.555.1212

Here’s what you do:

1. Choose “Define Fields...” from the “Select” menu and create a new CALCULATION field called “Phone Numbers Only” (WITHOUT the quotes!). The calculation is very simple, just type in the name of the phone number field, in our case “Phone”. MAKE SURE the “Calculation result” is set to “NUMBER” and click OK.

2. Create ANOTHER calculation field called “Formatted Phone” and enter the following calculation:

“(“ & Left(Phone Numbers Only,3) & “) “ & Middle(Phone Numbers Only, 4, 3) & “-” & Right(Phone Numbers Only, 4)

MAKE SURE the “Calculation result” is set to “TEXT” and click OK.

That’s it! The first calculation “Phone Numbers Only” will strip out ALL text characters from the phone number (because we set it’s type to NUMBER). So a phone number that is formatted as “% 111###...555,,,1212” will have just the numbers: 1115551212. From there, we take the first three numbers (using the “Left” function) surround them with parenthesis (note the space after the second parenthesis), take the next 3 characters (using the “Middle” function), put a hyphen between them, then take the last 4 characters (using the “Right” function).

Solution 2.

Phone Number=[text]

Phone number formatted=Calculation=
Choose(

Length(Abs(TextToNum(Substitute(Phone Number, ".", "&quot))),

"", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits",

"(" & Local Area Code & &quot " & Left(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 3) & "-" & Right(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 4),

"Too Many or Not Enough Digits", "Too Many or Not Enough Digits",

"(" & Left(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 3) & &quot " & Middle(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 4, 3) & "-" & Middle(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 7, 4),

"Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits&quotChoose(

Length(Abs(TextToNum(Substitute(Phone Number, ".", "&quot))),

"", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits", "Not Enough Digits",

"(" & Local Area Code & &quot " & Left(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 3) & "-" & Right(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 4),

"Too Many or Not Enough Digits", "Too Many or Not Enough Digits",

"(" & Left(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 3) & &quot " & Middle(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 4, 3) & "-" & Middle(Abs(TextToNum(Substitute(Phone Number, ".", "&quot)), 7, 4),

"Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits", "Too Many Digits&quot


Local Area Code= Global = Number

HTH

John