There are several Custom Functions available on Brian Dunning's website for formatting phone numbers. I'd suggest starting there and finding one that suits your requirements.
Once you have the Custom Function installed and working (you'll need FileMaker Pro Advanced to create the CF) its a matter of creating a simple script that runs through all of your records and does a Set Field step with a calculation that calls the Custom Function, replacing the existing phone number with the newly formatted one.
I suggest using the Data Viewer in FMP Advanced to test the CF a few times before running your script, just in case something goes wrong and you trash all of your phone numbers. You could also create a new field and put the results there, leaving your original data intact until you're sure you want to change everything. Remember that there is no "undo" for a batch process like this.
How do I start creating custom functions. Is a mod of filemaker pro
advanced (which i do have) or is it already a part of fmp and if so how do
i access it.
The ability to create a custom function is part of FileMaker Pro Advanced. The regular FileMaker Pro can access CF's, but not create them. Look under File > Manage > Custom Functions.
Once entered, the custom function will be available anywhere you can enter a calculation (field defintions, set field, set variable, etc.)
hell yeah thanks mate
@NumbersOnly = Filter(Self; "0123456789");
Left(@NumbersOnly; 3) & "-" &
Middle(@NumbersOnly; 4; 3) & "-" &
Can it really be this simple, i've been gnashing my teeth over this all day.
Here's a cool trick with Jaymo's method - set a script trigger so the script auto-formats the phone number at data entry time.