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

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

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