You might look at how the bottom left "search portal" in this demo file works:
If you are using FileMaker 12, open this file from the File menu to produce a copy of the file that is converted into FileMaker 12 format.
Great file, I'll keep that one! But I don't really understand how I'm going to use pattern count() in my context. As a portal filter it works great, but I don't have a portal, just one line of calculation. Or do you mean I should add an invisible portal to a layout? Its a possible solution in a script, but it feels like a filemaker walkaround.
I was thinking of the portal that uses the "starts with" matching logic in light of this sentence in your original post:
Is there a way to match the area code from the separate table to the beginning of the phone number?
But I think I misinterpreted what you want to do here. "matching" to an area code here in the states isn't something you can do reliably just from a phone number that has no area code in it. The same number can exist in each area code region. You'd need additional data to determine which area code applies.
I do have some contact management type systems where the local area code is automatically appended to the beginning of the phone number if the number of digits in the phone number equals 7.
And you may find this expression helpful in counting just the numeric digits by filtering out the non-numeric characters:
Filter ( YourField ; 9876543210 )
I've solved it with a script with a loop. Its maybe not so neat, but it is possible to move it into another database in 10 minutes.
example data; phone number: 0812345678 or 024312345 both 08 and 0243 is in a list with 270 area codes. wanted: 08-12345678 or 0243-12345.
There is no way to post what I did as a text post, so I will make a sample database and link it.
To post a script to the forum:
- You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
- You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
- If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
- If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
Ok, here is the example file, in .fp7 format [download]
I rewrote it in FileMaker 10, I use 12 in normal cases. You need script triggers to do this, so no earlier version than FMP10.
So much hazzle just to know where the dash should be in the phone number. :)