What are the steps to format all phone numbers in a field so that they all look the same? I want them all to have this pattern: (###)-###-####. Every time I try to use the formula Pattern(phonenumber,"(###) ###-####") it just wipes out all the numbers.
Use the formatphone( function.
For wiping numbers, try putting a str() around your formula.
If your phonenumber field is numeric, your formula won’t work because the pattern( function outputs text, which can’t be stored in a number.
If your phonenumber field is text, your formula won’t work because the pattern( function doesn’t work starting with text, only numbers. You could use a variation like this:
pattern(val(striptonum(phonenumber)),"(###) ###-###")
However, Bruce’s suggestion is the best. First, make sure that the field is a text field. Then use this formula:
formatphone(phonenumber)
That’s it!
If you want to do the entire field, you probably want to use the formulafill statement, like this:
field phonenumber
formulafill formatphone(phonenumber)
Here’s the help for formulafill.
Once you’ve formatted all your numbers you can set the input pattern under Field Properties in the data sheet. Then every time you enter a number it will format the way you want.
By the way, it would be handy to be able to set Field Properties while in graphics mode on a form instead of having to constantly switch to the data sheet!
Field Properties Panel needs to know which field is active and to have a way of changing to other fields when desired. Both easy on the Data Sheet, but not so easy on most forms. Unless I’m overlooking something, a generic form could be made to pick active fields and display/edit their properties. You might even be able to re-order, add or delete fields from it. Once made it could easily be copied to other databases via View Organizer…
Getfieldproperties( and Setfieldproperties would do most of the work. I think there may be a few things that can’t be done without an open Data Sheet which has no hidden fields. I’d advise verifying those two work from forms alone before investing much time in the project. All the contents of the Panel should be doable with standard form tools. Making sure to offer all the field options would provide a good review and the code to tie it all together would provide good practice. You might find the project an excellent learning experience.
Didn’t work, Bruce. I am working with the Data Sheet. I have imported a .csv file which comes from customer input. So the format of the phone numbers varies considerably. When I go to the data sheet field and put in this function:
formatphone((Mom’s Cell Phone) (__) -)), I get this error message: Expression contains an operand when an operator was expected
I’ve tried it with and without the extra parentheses, with quotes around the field name instead of parentheses, etc, but still the same result. Thanks for taking the time to answer though.
Thanks, cmcl! Unfortunately, it didn’t help.
The note didn’t transfer my formula the way I entered it: It should read
phoneformat((Mom’s Cell Phone)(__) -))
It auto corrected it again, and it still doesn’t look like I typed it in. sorry. I’ll give up.
Simply use this formula if the field is a text field containing the variously formatted phone numbers:
formatphone(«»)
Make sure you are in the desired field when you enter this in Fill dialog.
Whenever you are trying something like this, try it in the Formula Workshop, in the Help menu, so you can fiddle with it to make sure it is correct.
It worked! It worked!
Thanks!
Glenda K
I’ve tried everyone’s suggestions, but I get this error message each time: Expression contains an operand when an operator was expected.
This is what I did: Field > Morph > Fill with Formula
I entered formatphone (Primary family home phone)
Then I hit FILL. That’s when I get the error message.
Thanks so much for any help.
Glenda K
Did you have the space between formatphone and the parenthesis?
Bruce is right, and if the source field has a name with spaces in it you additionally need chevrons around it:
formatphone(«Primary family home phone»)
For some reason Discourse seems to have posted Glenda’s last two posts out of order. Her last post by date indicates that she got it working.