Telephone Numbers

[dparkins]

I have a table with a telephone number field in it. Currently, the numbers do not have any dashes in them. The field itself is a character field. Is there a way to run an operation to update the numbers to have dashes in them? ex:
from 1234567890
to 123-456-7890

Thank you,
Dave

[Al Buchholz]

RE: Telephone Numbers

Dave

Here is a similar question that was posted before.

http://msgboard.alphasoftware.com/alphaphorum/read.php3?sortby=lastreply&direction=desc&num=11&id=9032&thread=9032

[Thomas Henkel]

RE: Telephone Numbers

A bigger question here is why store the dashes at all??

On all of your forms, reports and letters, you can format the numbers in whatever way you would like.

Tom

[davej]

RE: Telephone Numbers

Dave,

In the field properties of the field in question, you can set a mask for the telephone number so that the dashes are in the appropriate position AND you can have the field only accept numerics even though it is a character field.

Dave

[Mario Prieto]

RE: Telephone Numbers

The easiest way to do what you need is to go to the field rules for the table where you have the phone number field.
Then under tab "Data Entry" look for Mask style and choose Simple. A pop up screen will come up and you can choose a pre-built mask or you can just create yours by typing :
000-000-0000 on the mask.
Hope this helps.

The Mexican

[dparkins]

RE: Telephone Numbers

Thank you all for your replies. Here's the thing, There is a mask in the field rules now but there wasn't to begin with. So, I have around 5000 records where the telephone# is without dashes. We do view these records on forms but we also use them for mailings which is why formatting the field on the form itself would not solve my problem.

Al pointed me to the following calc which I'm trying now.
left(tel,3) + "-" + substr(tel,4,3) + "-" + substr(tel,7,4)


Thanks,
Dave

[dparkins]

RE: Telephone Numbers

By the way, some numbers are 7 characters long and some are 10. Is there a way to state I only want to run this calc when the length is 7 and run the other calc when the length is 10?

left(printtel,3) + "-" + substr(printtel,4,3) + "-" + substr(printtel,7,4)

Thank you,
Dave

[Thomas Henkel]

RE: Telephone Numbers

I would probably do as "the Mexican" says, except make the mask conditional on the lenght of the data in the field. The mask is used in all forms, reports, labels, etc. and DOESN'T need to be stored in the data itself.
see attached:

Tom

[Al Buchholz]

RE: Telephone Numbers

Dave

Since the choice happens when the area code is missing, use alltrim and padl functions to make a string that is either

padl(alltrim(phonefield),10," ")
to get:

' 5551212'
or
'6085551212'

Then the function should work and give you

( ) 555-1212
or
(608) 555-1212

[dparkins]

RE: Telephone Numbers

This is what I've got for an operation but it's not quite right. Can you see what's wrong? It works until I add the or condition.

if(len(trim(printtel))=10,
left(printtel,3) + "-" + substr(printtel,4,3) + "-" + substr(printtel,7,4),"").or. if(len(trim(printtel))=7,
left(printtel,3) + "-" + substr(printtel,4,3),"")

[Al Buchholz]

RE: Telephone Numbers

Thomas

It requires less storage to not store the mask in the data. The disadvantage is that an export operation outputs the unmasked data. If the mask is stored, the exported data contains the mask.

All you can do is give the client the options and let them decide.

[drgarytraub]

RE: Telephone Numbers

Replace the .or. with a comma.

Read the syntax as "else" instead of "or"

[CALocklin]

RE: Telephone Numbers

The dashes also create problems when doing searches. If the dash is included in the data, the user needs to include the dash when searching and vice versa.

To solve the issue in one of my apps, I created a function called Phone_dash() that adds dashes to create a format such as either:
123-4567
or
123-456-7890

By using this in the CanWrite event, the phone number is ALWAYS formatted correctly regardless of parenthesis, spaces, or a leading "1" that the user might try to input.

I then use 'scripted finds' in this application for phone number searches. The user is prompted for a phone number using ui_get_text() and the number is converted in the script. Something like this:

phone_in = ui_get_text("ENTER PHONE","Enter....")
phone_in = phone_dash( phone_in )
parentform.find(phone_in)

The function can also be used in a global update to convert existing numbers.

Here's the function for you to cut-and-paste. (The method I'm using to retain the indents may cause double line spaces. If so, I assume you can find a way to remove them!)

[code]
FUNCTION Phone_dash AS C (Phone_in AS C )
'Description:Adds dashes to phone numbers as necessary and removes leading "1"s. Assumes 333-444-1234 output format will always be valid.
DIM phone_w as C
phone_w = remspecial(phone_in) 'Define the 'working' number.

IF phone_w = ""
Phone_dash = phone_w
EXIT FUNCTION
END IF

'Remove any leading 1 that might be for long distance.
'Left the ability to start an area code or exchange with "1" because of a
' conversion routine I use to "encrypt" phone numbers. (Although, it would be
' better to change the "encryption" too so all beginning 1s becomes 2s...)
IF left(phone_w,1)="1" .and. len(phone_w)=11
phone_w = substr(phone_w,2)
END IF

phone_w = stuff( phone_w, 4, 0, "-" )
IF len(trim(phone_w))>8
phone_w = stuff( phone_w, 8, 0, "-" )
END IF

Phone_dash = phone_w

END FUNCTION

[CALocklin]

RE: Telephone Numbers

Sorry, I forgot there was a long line in the function that would force the wide message.

[Willard]

RE: Telephone Numbers

Cal,
This is quite brilliant and if you read your comments closely one will learn a tremendous amount. Thanks for taking the time to comment.