phone number with hyphens only

[harry8]

How can make a phone number with default settings of hyphens only, e.g., 333-222-4444? It would convert a number that uses parens, etc., sort of like how you can choose to have a date field with numbers and slashes only.

thanks,

Harry

[falkaholic]

From Specal Edition Using FileMaker Pro 5:

Make a calculation field that does this:

formatedPhoneNumber =

If( Lenght(phoneNumber)=7, Left(PhoneNumber),3 ) & "-" & Right(PhoneNumber,4),

if( Length(PhoneNumber) = 10, "(" & Left(PhoneNumber,3) & ") " & Middle(PhoneNumber,4,3) & "-" & RIght(PhoneNumber,4), "Not vaild"))

This should format it good.

[jasonwood]

Note that the above will convert 5555555 to 555-5555 or 9055555555 to 905-555-5555

In other words, it is designed to take input from a field which only allows numeric entry, and display it as formatted. It will not interpret (905) 555-5555.

The way you would employ this is by putting the input field behind the output field on the layout, and set the output field as "Disallow Data Entry into field" so that when you click on it the cursor goes into the background.

For you to use a setup like this you would first have to strip your phone numbers of all formatting, (brackets, dashes, spaces). I'm not sure exactly how to do that.

[spb]

If you have a lot of old data with parentheses & hyphens, you could do a one-time cleanup this way:

Make a new number field, NewPhone. Highlight it & choose Replace from the top drop-down, then click "with calculation". A calculation window will appear and you define your calc as:

Substitute (Substitute (Substitute (OldPhone, "(", ""), ")", ""), "-", "")

This will take all data in your original phone fields (OldPhone), strip parentheses and hyphens, then stick the result in NewPhone. You could remove OldPhone from your layout (leave it archived on some other layout) or you could just delete the field and make NewPhone take its place henceforth.

Doing a replace with calculation has the advantage of not leaving you struck with a calc field that insists on calculating forever. It's a wonderful function for basic cleanup.

If you have unwanted characters besides parentheses and hyphens, just extend the substitute nest further.

Steve Brown

[jasonwood]

Would there be a way to simply strip out anything non-numeric? could texttonum(OldPhone) work here?

[BobWeaver]

Normally you can use the TextToNum function to strip out non-numeric characters, except the hyphens that may be there will be interpreted as minus signs. So you could try:

Abs(TextToNum(PhoneNumber))

[harry8]

Doesn't work -- any advice? Calculation warns "field does not exist", referring to "formattedPhone_hyphen" -- even with a space.

thanks, Harry

formattedPhone_hyphen =

If( Length(Phone_hyphen)=7, Left(Phone_hyphen),3 ) & "-" & Right(Phone_hyphen,4),

if( Length(Phone_hyphen) = 10, "(" & Left(Phone_hyphen,3) & ") " & Middle(Phone_hyphen,4,3) & "-" & RIght(Phone_hyphen,4), "Not valid"))

[harry8]

I tried this, I get a 'circular reference' message.

Abs(TextToNum(Phone_hyphen))

[BobWeaver]

formattedPhone_hyphen is not part of the formula.

[harry8]

this doesn't work either:

If( Length(Phone_hyphen)=7, Left(Phone_hyphen),3 ) & "-" & Right(Phone_hyphen,4),

if( Length(Phone_hyphen) = 10, "(" & Left(Phone_hyphen,3) & ") " & Middle(Phone_hyphen,4,3) & "-" & RIght(Phone_hyphen,4), "Not valid"))

thanks

[BobWeaver]

Have a look at the attached file.

PhoneFormat.zip

[harry8]

your attachment is garbled on my Mac -- can you paste it into a message?

thanks,

Harry

[jasonwood]

Harry,

If the file didn't decompress (download.php), try dragging it onto stuffit expander.

If the resulting file doesn't open, try dragging the file onto the FileMaker pro icon.

[BobWeaver]

Here's a stuffit version which should automatically unstuff.

[harry8]

I already posted this but I don't see it on the list. The Date format file works great. But I don't really want separate entry and display fields. How can I make it so it is one, or looks like one field?

thanks,

Harry

[jasonwood]

From my first post:

The way you would employ this is by putting the input field behind the output field on the layout, and set the output field as "Disallow Data Entry into field" so that when you click on it the cursor goes into the background.

by "background" I mean the field behind.