I have a telephone number field in many of my layouts. I would like the phone number autoformatted to (xxx) xxx-xxxx after its entered. Preferably in whatever way its entered to have the numbers formatted as above. Sorry if this is simple and I'm missing it. I'm new to FM.

Take a look at this thread

Using the calculation from one of John Mark Osborne's tip files (FieldFilter7.fp7) from his website Databasepros.com, you can format your phone number field with this Auto Entry calculation:

Let(

NumbersOnly = Filter(Your_Phone_Field; "0123456789");

"(" & Left(NumbersOnly; 3) & ") " &

Middle(NumbersOnly; 4; 3) & "-" &

Right(NumbersOnly; 4))

His calculation strips the phone number of all existing format so there are just numbers and then reformats it the way you want it.

After you finish the calculation, copy it to your clipboard. Make sure you uncheck the "Do Not Replace Existing Value, If Any" checkbox before you exit the auto entry formatting screen.

Then do the "Replace Field Contents" for your phone number field and choose By Calculation and paste your calculation in. Since it will do all the records in the found set, you should probably just have one record in the found set to make sure it works right.

This is for a phone number with 10 digits so you would need to add other conditions to your calculation.

If anyone knows how to add a condition for an extension, let me know (e.g., 321-555-7777 x 213). I can't figure it out, and using the above calculation, I'd get (321) 555-7213. (And I'm changing my level from intermediate to beginner because I really need to learn scripting better!)

Well the calcualation could be modified, but I think it is better if you store the extention in another field.

Try something like:

Let ( [

preprocess = Substitute ( Filter ( Phone ; "0123456789x" ) ; "x" ; " " ) ;

main = LeftWords ( preprocess ; 1 ) ;

ext = MiddleWords ( preprocess ; 2 ; 1 )

] ;

Replace ( Replace ( Replace ( main ; 7 ; 0 ; "-" ) ; 4 ; 0 ; ") " ) ; 1 ; 0 ; "(" )

& Case ( ext ; " x" & ext )

& Case ( Length ( main ) ≠ 10 ; TextColor ( " too few or too many digits" ; 16711680 ) )

)

EDIT:

I forgot to add that I too believe the extension belongs in a separate field (and perhaps the area code deserves one as well).

Edited January 22, 200718 yr by Guest

That's the way it used to be and management had me change it because everyone prefers to enter them into the same field (it's on the web). But now they also want the field standardized since some people insist on putting in dashes, etc.

I'd go back to a separate field for extensions if I could figure out how to write the script that says extract the numbers to the right of the little x -) There are probably about 300 contacts with extensions and they're not all 3-digit.

I need to take some classes in scripting. I used to be smarter.

I didn't even see that you posted something for me to try. Thanks. I'll work on it tomorrow.

Stephie

I need to take some classes in scripting.

Lesson One: these are calculations, not scripts.

Yes, I realized that after I posted, but I need lessons in both so what the heck.

I'd go back to a separate field for extensions if I could figure out how to write the script that says extract the numbers to the right of the little x -) There are probably about 300 contacts with extensions and they're not all 3-digit.

Let([

PosX = Position ( Tel_EntryField ; "x" ; 1 ; 1);

Ext = Right(Tel_EntryField; Length(Tel_EntryField) - PosX);

NumbersOnly = Filter(Ext; "0123456789")];

NumbersOnly)

Thank you everyone... This has been very helpful.

Just in case someone needs it, I needed a calculation that automatically assumed an area code if I entered only 7 digits...

I thought I'd share:

Let (

//define variables:

[

rawNumber = Filter (Cell; "0123456789") ;

length = Length (rawNumber);

//set error flag for a phone number that's too short

error = If ( length < 7 ; "error"; " " )

];

//now apply the phone formatting and return results

If ( error ≠ " "; error;

If ( length = 7 ; "(205) " & Left (rawNumber; 3) & "-" & Middle (rawNumber; 4; 4) ;

" (" & Left (rawNumber; 3) & ") " & Middle (rawNumber; 4; 3) &

"-" & Middle (rawNumber; 7; 4) &

//This condition tests for extra digits

//that well treat as an extension

If ( length > 10; " x" & Middle (rawNumber; 11; length - 10); " ")

)

)

)