Telephone field layout

I have some imported data that needs to be reformatted. It is phone numbers in Australian format. The current data is 712345678. The result should be (07) 1234 5678.

I can easily morph the data to first remove the first digit leaving 12345678. I can also add a prefix (07) (with a space after the parentheses)that now gives me (07) 12345678.

I can’t find how to put a space between the 4 and the 5. I am sure this is easy but it eludes me at the moment. Please point me in the right direction.

pattern(val(“712345678”),"(##) #### ####")

Sorry, I don’t get this. The formula you have given me may change one field only. What I want to do is change the layout of dozens of records at once. I thought this could easily be done using Morph.

I think I found the solution. I used “Insert Text at Spot” and it worked. I simply placed a space at the 10th spot.

and recalculate field

Glad you got it solved! My reply was intended to illustrate the pattern() function, which is a workhorse for converting numbers into formatted text. In actual use you would have the name of the field containing the raw phone number where I entered your example number. I now realize this might not have been where your particular glitch was.

Yes, that was my thoughts as well. The formula would work for each record. But when it comes to converting multiple records, a different approach may be better. Izno said to recalculate the field. Again, that would need to be done for each record. I am very new to Panorama so using formulae and procedures is a bit grey at the moment. I am changing over from Filemaker and this is all a bit of a learning curve.

To change all of the selected records in a field, right-click in the field NAME (not a data cell) and choose Morph>Fill with Formula …, enter the formula pattern(val(PhoneNumber),"(##) #### ####") and click on the Fill button. Replace PhoneNumber with the actual field name.

If you already have parentheses in the field, remove them first.

To remove those parentheses, follow the same steps Michael described, but use a formula of

striptonum(PhoneNumber)

Again, you would replace PhoneNumber with the actual name of the field.

I"m thinking turning a string that is not a number into a number for the purposes of accomplishing a pattern is a recipe for problems. First amongst them would be losing leading zeros. Strings are strings and numbers are numbers and they really should not be confused. There are plenty of string functions that can pattern a text string without bringing in additional issues.

This isn’t a problem at all for the pattern( function. pattern(7,"###") will return 007. He started with a missing leading zero, so part of the task was to add it, and the pattern Bill suggested does just that.

And lest there be any doubt, it can all be done in one step:

pattern(val(striptonum(PhoneNumber)),"(##) #### ####")

I’m in same situation after 25 years in filemaker.

Dave, I didn’t leave out a zero. I am using the Australian area code format and it only has 2 zero’s.

James, I tried this but it won’t work. I may have something wrong with the way the field is setup. The field I am using is called “Phone 1”. It is formatted as text. From my understanding (very limited) your formula will result in a number. The pattern has brackets on it and I thought a number format would not display the brackets?? I think the formula may need to be modified to return the number to text before applying the pattern change??

Kaiviti, here is a breakdown of how it works…

stripToNum(PhoneNumber) will remove any characters other than numbers from a text item.

Val(StripToNum(PhoneNumber)) will turn that ‘string’ of digits into a real ‘number’

Pattern(Val(StripToNum(PhoneNumber)),"(##) #### ####") will take the ‘real number’ and convert it back to a string of characters using the ‘#’ placeholders for the digits to fall into. The quote characters are merely the beginning and end notators of the pattern.

James’ formula will require you replacing the words ‘PhoneNumber’ with your ‘Phone 1’ text so that it can grab the proper data.

Here is a demonstration of the formatting on an example of your original text data using the formula:

pattern(val(striptonum(«»)),"(##) #### ####")

Note here that the chevrons («») are a shortcut way to indicate the current active field and in the demo’s case this would be the Phone 1 field. So here is an example of changing an entire field’s records using the Morph dialog.

I didn’t say you left it out. I said it was missing.

The “current data” does not have a leading zero. The desired result does. Therefore, part of the requirement is to add a leading zero.

Hey Gary, That is a fantastic way of showing how to do something. How did you get a video in the post?

Also, your formula now works in my datasheet. Before I was continually getting an error and trying to figure it out was driving me crazy. The reason was that none of the other examples showed the chevrons to indicate the “Phone 1” was a field. Your example has the chevrons and as soon as I saw that, I realised that what the problem was.

Thanks to everyone for your input. This has helped me heaps.

That is an animated GIF. I can’t speak for Gary, but I have created animated GIF screen recordings with Claquette (in the Mac app store) and with ScreenFlow.

The chevrons are only necessary because your field name has a space in it. If your field name was Phone1, the chevrons would not be required (though you can always use them if you want). For all the details, see this help page.