Script for inserting hyphens in phone number field

[isavant]

Hello,

I am fairly new to the world of Filemaker and as a longtime lurker, this forum has already helped me immensely.

However, there is one issue that I am currently having for which I cannot seem to find a solution. Any tips would be well appreciated.

I have a large database of contacts with the phone numbers in the format 1234567890. I would like to insert hyphens so that the phone numbers appear as 123-456-7890.

There are over 50 000 records, so doing this manually is out of the question.

Can someone please point me in the right direction to writing a simple script that would automate this procedure?

Any help is greatly appreciated.

Thank You.

John

[efen]

If there are always 10 digits (and without parentheses or other characters) in the number then use script

pseudo code

Show all records

Goto first record

Set field phone number; Left ( phone number;3 ) & "-" & Middle ( phone number;4;3 ) & "-" & Right ( phone number;4 )

Loop

Goto next record, exit after last

Set field phone number; Left ( phone number;3 ) & "-" & Middle ( phone number;4;3 ) & "-" & Right ( phone number;4 )

End loop

[Lee Smith]

Hi John, and Welcome to the Forum,

This topic comes up from time to time.

Questions, Is this going to be a reoccurring problem?

If so, you will want to use find a cure before it gets into the database, or fix it as it is entered.

One way to fix it is to create an Auto Enter for the field, or create a new calculation field so that you do not have to use a script.

If you perform a search of the Forum for [color:blue]+phone +number +Format (use the pluses as shown), you will get over a hundred hits.

Since you have Advance, you can also use a Custom Function for this, take a look at Link and do a search for for [color:blue]Phone.

[color:red]back up your file before you make any changes!

My approach would be to use one of the Custom Functions or Normal Calculation that you find, in an [color:blue]Auto Enter >> [color:blue]Calculated Value, be sure to deselect the "[color:red]Do not replace existing value of Field (if any)" just below the [color:blue]Calculated Value.

Next, I would change the type of field to a [color:red]Calculation (the custom function or calculation will show in the Specify Calculation box and you will get a warning about this, but it should not effect the field content (since you are using a copy it won't hurt). Click [color:blue]Okay, and go into Browse Mode.

Then return to the Manage Database, select the field, [color:blue]change it back to [color:blue]Text, and then click on [color:blue]Options. Select the [color:blue]Auto Enter option of Calculate Value, and that should do it.

Verify the data and let us know if you find a problem.

HTH

Lee