Phone number data-entry format

[BFH]

I've downloaded and studied the Phone_Format demo.

My need is to format several data-entry fields for phone numbers so that no matter how the multiple data entry staff format values that they enter in these fields (i.e., with or without dashes, etc.), the entries will end up with the format

(xxx) xxx-xxxx

The demo provides options for formats.

Can someone please provide an explanation of how to accomplish the above goal? BTW: I'm not a professional FMP programmer.

Many thanks in advance and cheers!

[andygaunt]

Hi,

Create a calc field for phone number display.

Case(

IsEmpty(Phone),"",

Length(TextToNum(Phone)) = 11 and Left(TextToNum(Phone),1) = "1",

"(" & Middle(TextToNum(Phone),2,3) & ") " & Middle(TextToNum(Phone),5,3) & "-" & Right(TextToNum(Phone),4),

Length(TextToNum(Phone)) = 10,

"(" & Left(TextToNum(Phone),3) & ") " & Middle(TextToNum(Phone),4,3) & "-" & Right(TextToNum(Phone),4),

Length(TextToNum(Phone)) = 7,

Left(TextToNum(Phone),3) & "-" & Right(TextToNum(Phone),4),

Length(TextToNum(Phone)) = 8 and Left(TextToNum(Phone),1) = "1",

Middle(TextToNum(Phone),2,3) & "-" & Right(TextToNum(Phone),4),

Phone

)

Where phone is your phone number field.

Place this field on top of your phone number field and turn off allow entry into field from the field format.

So, no matter how your users enter the phone number (with or without spaces, with brackets or without) it will format to (xxx) xxx-xxxx for you.

HTH

[BFH]

Hi!

Many thanks for the very prompt and very helpful reply. I have the following questions.

1. I must not have followed your directions correctly; when I type a value in the Phone field and the click off it, two values display.

I then set the typeface color of the Phone field to white.

After doing so, I found that the calc field was displaying the phone number in scientific notation.

I then went back to Define Fields and set the calc field to display as text rather than as a number.

After doing the above two steps, the number displayed as desired.

Then when I try to type a new value in the field, what I'm typing doesn't display (because the Phone field is set to white). So I set the typeface color back to black.

After typing in a new value, the two fields now display (i.e., the properly formatted value and the improperly formatted one) and it is a jumble.

What have I done incorrectly?

2. Can you please explain to me how this "place the calc field on top of the phone field" trick actually works properly? How is the data-entry person then actually entering data into the Phone field when the calc field is sitting on top of it (in the way)?

3. How did you devise this calculation? I can tell that the first part takes into account the possibility that the data entry person included a 1 and area code (for long distance), the second included only the area code, the third included only seven digits, and the fourth included only a 1 (for local long distance). But I don't understand the rest of the calculation nor the inclusion of Phone at the very end on a line prior to the close parentheses.