CiviCRM Community Forums (archive)

When sending text/SMS messages from CiviCRM, some(most?) SMS gateways reject the message if the recipient phone number is lacking the international country code.   I created a very simple work-around for the ClickaTell extension for CiviCRM, but it only works for the US and Canada. ( http://issues.civicrm.org/jira/browse/CRM-14160   or  https://github.com/civicrm/civicrm-core/pull/2447)

Lobo requested that I make this solution more general to handle any country, and also store the extra data needed in the table "civicrm_country"    ( FYI: I found a table of all country phone codes at: http://countrycode.org/) 

What I am not sure about is the rule to use to determine if a phone number is lacking the country code or not. In the US/Canada, its easy as the code checks of the length of the numeric portion of the phone number. If the length is exactly 10, then it adds the country code. Otherwise the code does not alter the phone number.      Is there a standard phone number length for other countries?   How should the validation work?   

The SQL attached to the issue at http://issues.civicrm.org/jira/browse/CRM-8002     has errors for various Caribbean and Pacific islands.    For example of an incorrect update:   update civicrm_country set country_code ='1684' where iso_code ='AS'; 

This should be: update civicrm_country set country_code ='1' where iso_code ='AS';

This is because a phone number for American Somoa is formatted/dialed no differently than any other US or Canadian phone number.     

Example of a phone number for American Somoa:   1-684-xxx-yyyy
Example of a phone number for Chicago, US:          1-312-xxx-yyyy
Example of a phone number for New York, US:        1-212-xxx-yyyy

The three numbers after the 1 is what  most people call the area code.  All US/Canadian phone numbers require an area code to be dialed even within the country. (At least where I live, in the Chicago area). In some smaller towns, dialing the area code for local in-town numbers is not required if using a land line. The area code always required when calling from a  mobile/cell phone.   

I added a comment with the full list of SQL changes to your JIRA issue at http://issues.civicrm.org/jira/browse/CRM-8002

FYI: The phone/SMS companies will likely charge higher/different fees for calling/texting a number in a Caribbean island.  But this is outside the scope of CiviCRM.    For example I pay International calling fees when I call Canada from the US, even though the number I  dial is formatted exactly the same as my neighbor's phone number. This can often happen by accident where the caller has no idea they are calling a Canadian phone number and then gets a surprise on their next phone bill.
     

I had another idea: Add a new column called "country_id" to the table "civicrm_phone".  This approach would be consistent with how physical addresses are stored in the "civicrm_address" table.     This could be defaulted to the default country for the installation, just like already happens when someone is adding a new address.

I had another idea: Add a new column called "country_id" to the table "civicrm_phone".

It's the right "technological" approach, but from a usability perspective, it's adding yet another field dropdown and probably too cumbersome.

Might be better to simply use the postal address and assume the country is the same for the phone?

My planned approach to make my changes international is:

1) Populate the existing column  "civicrm_country.country_code"  with the appropriate International country code, as documented at: http://issues.civicrm.org/jira/browse/CRM-8002 

2) Add a new column called "country_id" to the existing table "civicrm_phone"  which will be populated with the country_id of the address of the default organization for the installation.     This is consistent with how the table "civicrm_address" is structured. It will allow my code to know which country the phone number belongs to so that I can validate it. This change will also allow future use of the library at:  https://code.google.com/p/libphonenumber/

At this point in time, I am not planning to change the GUI for editing/adding phone numbers. I think when that is done, it should be as unobtrusive as possible, such as how Google "Contacts" works.