Looking for Super-Duper International Phone Number Formatting

[dmontano]

Hi all,

 

I have seen a few phone number formatting calcs that format quite nicely. I have used them only to realize how complicated it can be when you take other countries' method of phone formatting into account. The calc I used worked great for USA phone numbers, however, I can not get it to work for other countries. But I believe my thought process in getting what I want is correct, I just cannot get the calc to work.

 

I am fortunate in the fact that I have a separate field that prompts for the country. So, I want to use that "Country" field result to trigger the correct calc line to get the desired result.

 

This is the calc that works for USA phone numbers:

 

Let (

[rawNumber = Filter (employee_phone_cell; "0123456789");

length = Length (rawNumber);

red = RGB (160;0;0);

error = "Must be 10 digits" ;

validNum = "(" & Left (rawNumber; 3) & ") " & Middle(

rawNumber; 4; 3) & "-" & Middle ( rawNumber; 7; 4) &

If ( length > 10; " x" & Middle (rawNumber; 11; length - 10))];

 

If (

length

)

)

 

I found this calc on this forum and it works nicely, but I can not modify it to work when a different country is selected.

 

My thoughts were to have an "IF" type instruction at the beginning so when the country "USA" was selected in a field prior to getting to the phone field, the phone field calc would perform the "country specific calc".

 

Since I do not know how to write it, and I am afraid I am not explaining it well, I will try to "Calc-explain" what I want:

 

If (country="USA" then use calc I just showed above,

 

If(country = "Japan" then use calc like above but with the appropriate modifications to match Japan phone formatting.

 

If(country = "Singapore" then use calc like above but with the appropriate modifications to match Singapore phone formatting.

 

Do I use Case? Let? or just rip my hair out?

 

If anything, I am sure this post has just about confused everyone who happens to read it - including myself. Congrats to those who can decipher what I am trying to say.

 

Thanks in advance.

[AHunter3]

You can use any of those. You can "Let" thestringlength = Case (country="this", 11; country="that", 12") and "Let" theformattedstring = Case (country = "this", ) and so on and then go with a straighforward formula for error versus non-error:

 

Case (rawnumber = thestringlength, theformattedstring, theCountrySpecificErrorMsg)

 

 

Or you can handle it all in Case, if you prefer. Using Let () makes it easier for most people, but some folks actually find it harder to parse, so there's at least a bit of it being a matter of taste.

 

I would not use If (), myself — it won't do anything for you that Case() will not do, and it's klunkier to work in. It's really an obsolete calc function* and I keep expecting FileMaker to end-of-life it and convert all old If functions to Case functions when solutions are converted; but they have not chosen to do so yet.

 

[* NOT to be confused with If [] as a script command, where it is in no fashion obsolete]

[dmontano]

Thanks for the quick reply. Your point about the "IF" becoming obsolete is appreciated - I will avoid it and try to reconstruct using Case. The book I have points that out as well after I referenced it last night.

 

I am sure your advice will work, and I am stubborn enough to try and figure it out and when I do I will post for those to either use, or shoot holes in my mistakes.

 

My problem is I am not a developer, and when I "lift" the calcs or scripts off this site from those that have contributed have helped me learn far more than the books I have. However, this is one calc that supercedes what I have come to generally understand. I will have to work on this one to figure it out.

 

Once again, thanks.

[David Head]

I would not use If (), myself — it won't do anything for you that Case() will not do, and it's klunkier to work in. It's really an obsolete calc function* and I keep expecting FileMaker to end-of-life it and convert all old If functions to Case functions when solutions are converted; but they have not chosen to do so yet.

I used to think that way too. But now I see that the If function will never have an end-of-life. It is there for a reason. Part of that reason is readability. I am a big fan of writing expressions that you can read and understand.

 

For example, which of these is easier to read out loud and understand?

Case ( Length (name) > 10; do this; do that )

If ( Length (name) > 10; do this; do that )

That is a very simple example.

 

This idea of readability extends to the use of custom functions to make this easier to read:

If (not IsEmpty (name); do this )

Note that the If function does not require a null result. But you might put one in for completeness smiley-wink .

 

So with a simple custom function, this can become:

If ( notEmpty (name); do this )

Readability also extends to the use of the Let statement to allow for the use of variable names in calculations.

 

Anyway, just my ramblings of my current state of mind on the If function. So Cool!

[dmontano]

Thanks David for the insight. I am coming to realize that understanding what one is trying to say in the calc is half the battle, the other half seems to be in how to construct it.

 

It is obvious my problem lies in ignorance of calcs and practice. I am working on both. I agree that being able to construct something more clearly is extremely helpful, typically I come back to something I had created a ways-back and due to my infrequency of writing calcs - I get lost in what I did to make it work. This must certainly be the case for many novice users such as myself - and evidence of the value of a developer who creates and reads calcs rather easily and works on the myriad ways of tweeking them for customization or optimization.

 

I am also hyper-detailed so this stuff drives me crazy. I have found myself stuck in trying to get this one calc figured out. I guess my concern is before this thing is unleashed, I have got to create fields that people can enter data in and not make a nightmare for me to provide reports, anaylsis, etc. for other users because the data they entered is formatted in a multitude of ways. I am sure this is a concern for many. My big issue is the fact that I am creating modular pieces that others will enter data into, and these moduels will ultimately be feeding the data needs of other "modules" I am building. So, bad data at the beginning means a lot of dirty laundry to clean later - if ever possible.

 

Unfortunately, I get one thing solved and a day or so later I get stumped again during the development. Obviously having a can-do attitude must be part of the mental-make-up of developers.

 

I will be reading tonight, and poking around on this calc.

 

Thanks again.

[mfif]

Make sure you put in as many comments as you need in your calcs, as "notes to self". You can also comment out bits you're working on and haven't quite fixed yet.

[dmontano]

You should all get a kick out of this...

 

Based on everyone's input I did get something to work. I have many countries to create still, but I got at least 2 countries to work. I am positive that I have Frankenstein'd this beyond all get out. Each of you may see my attempt at your advice only to cringe at my interpretation of it - especially David Head who pointed out the importance of simplicity.

 

By no means do I think I am finished nor that this is perfect - rather, I am hoping one of you can specifically point out where I can cut down the clutter. I will remove the comments and probably place them at the end when I am all finished, but for now, it is the only thing I have to keep my head screwed on when I have to try and figure out what is going on with it.

 

Here's the blood-bath in all its glory:

 

Case(

office_country = "USA"; Let(

[

rawnumber = Filter (office_phone_sales_test_expanded; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error = "Must be 10 digits";

correct_format_phone_number =

"(" //This is the Opening Paren for the Area Code

&

Left (rawnumber; 3) //This is the Area Code Number of the Phone Number

&

") "//This is the Closing Paren for the Area Code

&

Middle (rawnumber; 4; 3) //This is the Exchange Number of the Phone Number

&

"-" //This is the Hypen symbol that goes between the Exchange and Subscriber Numer

&

Middle (rawnumber; 7; 4) //This is the Subscriber Number of the Phone Number

&

If (length > 10; " x" & Middle (rawnumber; 11; length - 10)) //If rawnumber is greater than 10 digits, this formula adds X after the first ten digits and places all digits over 10 spaces after the X

];

 

If (length

 

 

office_country = "Netherlands"; Let(

[

rawnumber = Filter (office_phone_sales_test_expanded; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error = "Must be 9 digits";

correct_format_phone_number =

//There is no Opening Symbol for the Area Code

Left (rawnumber; 2) //This is the Area Code Number of the Phone Number

&

". "//This is the Closing Dot for the Area Code

&

Middle (rawnumber; 3; 3) //This is the Exchange Number of the Phone Number

&

". " //This is the Closing Dot symbol that goes between the Exchange and Subscriber Numer

&

Middle (rawnumber; 6; 4) //This is the Subscriber Number of the Phone Number

&

If (length > 9; " x" & Middle (rawnumber; 10; length - 9)) //If rawnumber is greater than 9 digits, this formula adds X after the first nine digits and places all digits over 9 spaces after the X

];

 

If (length

)

 

This torturous discertation is now temporarily over - until tomorrow.

 

Thanks again in advance.

[HBMarlowe]

You're on the right track here. As far as syntax is concerned, put all of your "Let" definitions for the whole function at the top, then use Case to tell it what to do under various circumstances. It's useful to know that once you have created a definition you can reference it in subsequent definitions.

 

Commenting is great. Using spaces to indent can help make it more legible.

 

Good luck!

[dmontano]

Thanks HBMarlowe.

 

Part of the problem I am having is learning the structure - as you can see. So I should...

 

1. Define the "Let" definitions at top

2. Then use Case to instruct FileMaker what to do under the various scenarios.

 

Am I understanding correctly that:

1. Everything inside the square brackets [] define the "Let"s

2. The semi-colon ; is separating each "Let" definition to stand on its own.

 

I have noticed that there are 2 components (I think) in my "Let" definitions that have to be altered for each country phone formatting I am trying to extract. Those 2 "Let" definitions are: "error", and "correct_format_phone_number".

 

It would appear I can not write these 2 "Let" definitions at the top, as they are modified depending on which country is selected prior to entering the phone field.

 

So, I am guessing that I am to define those 2 "Let" definitions with "Case" Then, finally, "Case" the "Ending" portion of the calc?

 

Then I stare off into space...

 

Thanks for any input.

[HBMarlowe]

Am I understanding correctly that:

1. Everything inside the square brackets [] define the "Let"s

2. The semi-colon ; is separating each "Let" definition to stand on its own.

 

Yes

 

I have noticed that there are 2 components (I think) in my "Let" definitions that have to be altered for each country phone formatting I am trying to extract. Those 2 "Let" definitions are: "error", and "correct_format_phone_number".

 

It would appear I can not write these 2 "Let" definitions at the top, as they are modified depending on which country is selected prior to entering the phone field.

 

Go ahead and write them at the top: error1, error2, etc. And

correct_format_phone_numberThisCountry, correct_format_phone_numberThatCountry, etc.

 

So, I am guessing that I am to define those 2 "Let" definitions with "Case" Then, finally, "Case" the "Ending" portion of the calc?

 

No, all the Let definitions first, then the case statements.

 

Then I stare off into space...

 

This is what I do the most. smiley-wink

[dmontano]

I can not tell you how much I appreciate everyone's help - really. I hope some day to be able to contribute in someone's need.

 

I will work this one calc until my nails are gnawed off. When I revise this I will post. Perhaps someone can use this calc in all its forms of contribution received here to have a pretty decent "international AND domestic" phone format calc. I am quite surprised the deeper I go, the deeper the hole gets. Phone numbers are quite varied and the context in how they are used varies quite a bit.

[David Head]

I'm not sure this is the way I would do it. However, just for the exercise, here is approximately what you have written but restructured as per what HBMarlowe has been saying:

 

Let ([
rawnumber = Filter (phone; "0123456789");
length = Length (rawnumber);
red = RGB (160;0;0);
error = "Unknown country or incorrect length number"
];

Case (

office_country = "USA" and length = 10;
"(" & Left (rawnumber; 3) & ") " & Middle (rawnumber; 4; 3) & "-" & Middle (rawnumber; 7; 4); // format (123) 123-1234

office_country = "Netherlands" and length = 9;
Left (rawnumber; 2) & ". " & Middle (rawnumber; 3; 3) & ". " & Middle (rawnumber; 6; 4); // format 12. 345. 6789

office_country = "Australia" and length = 10;
"(" & Left (rawnumber; 2) & ") " & Middle (rawnumber; 3; 4) & " " & Middle (rawnumber; 7; 4); // format (12) 1234 5678

TextColor ( rawnumber & " " & error ; red )

) // end Case

) // end Let

 

I will think on this some more.Oh Really!

[David Head]

I am quite surprised the deeper I go, the deeper the hole gets. Phone numbers are quite varied and the context in how they are used varies quite a bit.

You are right there. For example, in Australia we have land line and mobile (cell) numbers. Both are 10 digits but have different formatting:

Standard phone = (08) 8981 1144

Mobile phone = 0402 839 829

 

But then there are freecall numbers (and different ones of those):

Freecall = 1300 123 345

Freecall = 13 13 13

 

And what do you do with a landline without an area code? It should be:

8981 1144

 

And all that is just in Australia! So I don't know how possible it is to create a formatter for all situations. I am thinking about a custom function but I will get back to you on that. Oh Really!

[HBMarlowe]

I don't know how possible it is to create a formatter for all situations. I am thinking about a custom function but I will get back to you on that.

 

I'm looking forward to seeing what you come up with, David. Varied phone formats certainly do present a can of worms. In real life I've only had to deal with US numbers.

 

I remember reading a post, from Kjoe, I believe, advocating having the error result merely color the number as entered. That would allow the user to perhaps see where he/she went wrong and modify it without starting from scratch. As I watch users doing data entry I'm considering trying that approach.

[dmontano]

Update:

 

I still need help. I have got what I need to work - but it is ugly, long, and rookie written. I have tried very hard to follow the many great suggestions received on this forum, however, once again, many will cringe at my slop.

 

On the flip side, I am happy that the calc works and much more importantly it is deliverying what I want. I am sure there are many different preferences out there and a slew of things I haven't accounted for (i.e: country calling codes, Area Code not in its own field, etc.) but I realized those can be either incorporated later, or calculated elsewhere. I have no doubts that I will find a wrench in these spokes, but hopefully the calc will be solid and simple enough (after your suggestions and recommendations) to modify and accomodate these instances.

 

It accounts for six different countries and will accomodate as many formats that are out there that I can see. There appears to be many different formats within a country due to telecom networks, but I am content on dealing with those issues as they arise.

 

The help I need is: for anyone to shoot holes in the calc I post, suggestions to simplify and clean up the mess, and to show me the proper way to write what I have written. (I started with "Case", then to "Let", back to "Case", back to "Case".. and I am just not seeing the best and most simplistic way to write this. The overhead is too much because I have many other phone-type fields to paste this calc into and then modify.

 

I would like a "Super-Duper Phone Formatting Calc" that I only have to slightly modify whever I have phone-type fields in any of the dozens of databases I am building. I realize after going through this exercise how helpful this would be since there are soo many phone, cell, fax, toll free, support, tech, etc. phone lines that are out there that I need to format.

 

Here's the calc:

Case(

 

/////CHINA OFFICE PHONE TOLL 1/////

office_country = "China"; Let(

[

rawnumber = Filter (office_phone_toll_1; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error_office_phone_toll_1_china = "Must be 10 digits";

formatted_office_phone_toll_1_china = Left (rawnumber; 3) & ". " & Middle (rawnumber; 4; 3) & ". " & Middle (rawnumber; 7; 4)

];

Case (length = 0; "No Toll Number"; length=10; TextColorRemove(formatted_office_phone_toll_1_china); length > 1

 

/////JAPAN OFFICE PHONE TOLL 1/////

office_country = "Japan"; Let(

[

rawnumber = Filter (office_phone_toll_1; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error_office_phone_toll_1_japan = "Must be 10 digits";

formatted_office_phone_toll_1_japan = Left (rawnumber; 4) & ". " & Middle (rawnumber; 5; 3) & ". " & Middle (rawnumber; 8; 3)

];

Case (length = 0; "No Toll Number"; length=10; TextColorRemove(formatted_office_phone_toll_1_japan); length > 1

 

/////NETHERLANDS OFFICE PHONE TOLL 1/////

office_country = "Netherlands"; Let(

[

rawnumber = Filter (office_phone_toll_1; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error_office_phone_toll_1_netherlands = "Must be 11 digits";

formatted_office_phone_toll_1_netherlands = Left (rawnumber; 3) & ". " & Middle (rawnumber; 4; 4) & ". " & Middle (rawnumber; 8; 4)

];

Case (length = 0; "No Toll Number"; length=11; TextColorRemove(formatted_office_phone_toll_1_netherlands); length > 1

 

/////SINGAPORE OFFICE PHONE TOLL 1/////

office_country = "Singapore"; Let(

[

rawnumber = Filter (office_phone_toll_1; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error_office_phone_toll_1_singapore = "Must be 11 digits";

formatted_office_phone_toll_1_singapore = Left (rawnumber; 3) & ". " & Middle (rawnumber; 4; 4) & ". " & Middle (rawnumber; 8; 4)

];

Case (length = 0; "No Toll Number"; length=11; TextColorRemove(formatted_office_phone_toll_1_singapore); length > 1

 

/////UNITED ARAB EMIRATES OFFICE PHONE TOLL 1/////

office_country = "United Arab Emirates"; Let(

[

rawnumber = Filter (office_phone_toll_1; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error_office_phone_toll_1_united_arab_emirates = "Must be 10 digits";

formatted_office_phone_toll_1_united_arab_emirates = Left (rawnumber; 3) & ". " & Middle (rawnumber; 4; 3) & ". " & Middle (rawnumber; 7; 4)

];

Case (length = 0; "No Toll Number"; length=10; TextColorRemove(formatted_office_phone_toll_1_united_arab_emirates); length > 1

 

/////USA OFFICE PHONE TOLL 1/////

office_country = "USA"; Let(

[

rawnumber = Filter (office_phone_toll_1; "0123456789");

length = Length (rawnumber);

red = RGB (160;0;0);

error_office_phone_toll_1_usa = "Must be 10 digits";

formatted_office_phone_toll_1_usa = "(" & Left (rawnumber; 3) & ") " & Middle (rawnumber; 4; 3) & "-" & Middle (rawnumber; 7; 4)

];

Case (length = 0; "No Toll Number"; length=10; TextColorRemove(formatted_office_phone_toll_1_usa); length > 1

)

 

END OF CALC

 

Sorry so long, but hopefully that's better than not enough info. I have thick skin so feel free to "say-it-as-it-is".

 

Thanks again in advance,

David

[mfif]

I'm probably an idiot, but personally, precisely because I have to deal with so many different countries, I think I would just go for Filter (phonefield ; "0123456789+()- ") as an Auto-Calc, so that any other characters disappear after they're entered, and MAYBE test for an international country code at the start (e.g. +1 for US or Canada, +31 for the Netherlands, etc.) and add it if absent based on office country or home country. I say "add it if absent" because of course you can live in Singapore and have a mobile phone line in France, for instance.

 

People may make typing mistakes, but machines trying to think for people also mess things up. They tend to do it consistently, true, which may or many not help solve the problems that arise, but human life is made of inconsistencies.

 

That said, it's nice to have "pretty-looking" phone numbers, at least for the main countries you're dealing with.

 

Sorry, no input on the calcs. Great calc practice!

[David Head]

OK, it's done! I have written up this technique and the custom function involved as a blog entry at:

 

Telephone Number Formatting

 

There is a sample file that goes with the article but I have also provided it here.

 

Basically, the technique uses a recursive custom function. You supply the CF with a phone number and a format mask, and it returns the properly formatted number. The format mask is a text string that uses # as placeholders for digits and any other required characters and punctuation e.g "(###) ###-####".

 

You can then use the CF in a calculation that conditionally selects a mask format based on the length of the number, the starting digits, the country, or anything else you need.

 

Anyway, those of you following this thread have a look and tell me what you think.So Cool!

[mfif]

Great job, David,

 

Some day I'll finally manage to figure out recursive functions for myself. I still get stuck somewhere.

 

To allow users to enter a country code for international dialling (especially for automation), you could have a separate field for country code and use that as input as well. If empty, use the mask(s) for the default country. If not, just map country code to country, and flag unknown country codes.

 

Or test for a "+" at the beginning of the string, leave it in, and have separate masks for that.

[dmontano]

I have downloaded the calc and will see how I can work with it. From what I gather, I need the Advanced version to create custom functions (??) - I am on FM 8.5 Pro only. I am looking forward to seeing if I can plug this in.

 

To all - thanks much. David - hats off to you - I appreciate your help.

[dmontano]

Maybe I am missing something, but from what I can see, without the Advanced version, I would not be able to use what David created.

[Maarten Witberg]

that's correct. you need advanced to create custom functions. however, once embedded in the solution, they can be used in any version. So you either need to updgrade to advanced or find someone who will paste the cf in your file. If it's any help, filemaker advanced is well worth the money for anyone doing any halfway serious developing.

[David Head]

Maybe I am missing something, but from what I can see, without the Advanced version, I would not be able to use what David created.

I would be happy to create the CF in a file for you. Send me a clone of a file by email. smiley-smile

[dmontano]

Thanks for the offer, David. I just emailed you my file.

[dmontano]

Update for those browsing...

 

David Head's calc works awesome. It is very short, looks simple to edit (you visually can see the pattern of the phone format in the calc and can modify it to suit your "country" format need. I also like how you can decide if the divisor symbols are (), -, ., or blank space, and it is visual.

 

The calc I was tried to create myself I got to work, however, it was like a poorly written novel that had me confused. Also, my calc - while functioning - had a big drawback in that if the user did forget to type in the last number or not the correct amount of numbers, the field would display an error message and the user would have to retype everything back in - quite inconvenient. David's solution allows for the user to fix the missed numebr while displaying what they type in up to that point.

 

Now I could not decipher "how" or construct on my own what he has done due to my level of knowledge, however, like most calcs I find myself trying to learn - it may just take me two or three days of staring at stuff that confuses me but perservence has paid off. It gives me something to research and learn.

 

For the people with all the suggestions - thank you. And to David Head - Thanks for the great solution and for personally putting that calc in my file.

 

Now, if I can just get the time format thing figured out for that other field....

[David Head]

Thank you David for your kind words. I am glad to be of help. Just goes to show what a FileMaker Pro geek I am. And I love it.

[dgrzelak]

On 1/24/08 David Head posted a link to his custom function. That link is now broken. Does anyone know where I can find it or a similar one? I need to auto format phone numbers from several countries and would prefer it if I don't have to recreate the wheel.

 

Thanks.