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.
Being the ultra frugal skinflint that I am, I opted to go the freebee route. I use Smart Recorder Lite to make the movie and Drop to Gif to convert the .mov file to an animated .gif file. Smart Recorder Lite is free at the Mac App store. Once you make the movie it will open in QuickTime Player where you can view it and do any cropping that might be needed. I usually save the movie under a relevant name and then drag it into Drop to Gif to convert it to an animated gif file. Drop to Gif is not available at the App store but can be directly downloaded at https://github.com/mortenjust/droptogif/releases/download/1.28/Drop.to.GIF128.zip.