Validating Phone Numbers and Email Addresses

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Validating Phone Numbers and Email Addresses

  • Comments 13
  • Likes
If you have explored this idea in Sage CRM you will probably have discovered that the peculiar structure of the phone and email screens means that the regular options for screen based rules are not open to us. 

The phone number table is invisible in Meta Data - it just doesn't appear in the list of Entities and the Email table only offers a single filter screen. There seems to be no place that we can put Create scripts, onChange Scripts and Validation scripts to manage phone and email details.

And that is true. We can't use normal field level scripting with Phone and Email to ensure integrity. We will have to use some thing else.

Before we start "hacking" it is worth reminding ourselves what validation there is in place already.

Consider the screen below:


We can see that there is some basic Email syntax checking that takes place clientside. This limited check ensures that a user is aware of any issue with an email address before it is saved - but it still leaves the final decision to the user.

Note: This basic email check is absent when inserting a new Company and Person. The check is only relevant for updating email addresses. We would therefore need to check email addresses when the person or company is created.

There is no check for the phone number. The phone number fields can take alphanumeric characters - a mix of numbers, letters and symbols like "-".

Telephone numbers such as "1 800 TRAINME" may mean something to humans but if we are using CTI then this will cause problems. The following is taken from the CTI documentation:

"Users can enter phone numbers in Phone Number fields in any format. CTI ignores all non-numerical characters and spaces when the hyperlinked number is selected by the user and dialed from CRM."

So for CTI purposes we would only be left with "1800" as the number dialled.

When entering a phone number the user should type the "simple" number—we can include spaces but it is best to exclude symbols. For example 44 161 5527789.

Server Side Validation of Phone Numbers using an Entity Level Script

If there is no Field Level validation script of the phone numbers how can we add a check?

We can use an "Entity Level script with Rollback". Entity Level with Rollback scripts can be used when we want to stop an action happening if there is a validation error or other error with the script.

Entity Level Scripts can be defined on:

  • Company
  • Person
  • Address

and significantly for this example

  • E-mail

The scripts are invoked from only the following standard CRM screens when the final save is clicked.


The Values() and FormValues() collections are the mechanisms we can use in an Entity Level script to access data subittted from the screen.

The Values() collection will allow you to access the data within the fields associated with the EntryBlock (screen) used to build the edit screen for the record. So when inserting a company a script created against the company that used the Values() collection would only be able to access the fields of the CompanyBoxLong screen.

The FormValues() collection will allow you to access the data from within the whole of the HTML form submitted. This means in a screen like the new Company screen when you are actually inserting data to several tables (Company, Person, Address etc) then you can access any of the submitted data. So in an InsertRecord event function of an Company Entity script we could access the submitted phone number information:

var strphon_numberbusiness = FormValues("phon_numberbusiness");

or

var strphon_numberhome = FormValues("phon_numberhome");

Building the Entity Scripts

We can use FormValues() to access the telephone numbers and email addresses. We can validate these as the Company and Person records are created using an InsertRecord() event function in an "EntityLevel script with Rollback".

Also when we update Phone Numbers and Emails we will be able to use an Entity Level script with Rollback created for the email table. The check would need to be in the UpdateRecord() event function.

Example Script

To validate phone and email address added when a new company is inserted, we need to navigate to

Administration > Customization > Company

under the TableScripts tab we can create a new Tablescript of type "Entity Level with Rollback". (We could use exactly the same code under the person entity to ensure phone numbers and email addresses are check when new contacts are added.)

Note: The example code below makes use of two helper functions to check the email addresses and phone numbers. These are added to the Entity Level script above the event functions. I have only given the InsertRecord() event function here. Although the other event functions are empty they should be left within the script definition.



/////////////////////Helper Functions Start///////////////////////
function emailvalidation(x,y)
{
if (x.length>0)
{
apos=x.indexOf("@");
dotpos=x.lastIndexOf(".");
lastpos=x.length-1;
if (apos<1 || dotpos-apos<2 || lastpos-dotpos>3 || lastpos-dotpos<2)
{
Valid = false;
ErrorStr = CRM.GetTrans("colnames",y)+ " ["+x+"] "+ CRM.GetTrans("GenCaptions","BadMailAddress");
}
}
}

function phonevalidation(x,y)
{
re =/[^0-9]/;
r = x.match(re);
if (r)
{
Valid = false;
ErrorStr = CRM.GetTrans("colnames",y)+ " ["+x+"] "+ CRM.GetTrans("Errors","InvalidIntegerValue");
}
}
/////////////////////Helper Functions End///////////////////////
function InsertRecord()

{

// Handle insert record actions here
emailvalidation(FormValues("emai_emailaddressbusiness"),"emai_emailaddressbusiness");
emailvalidation(FormValues("emai_emailaddresssales"),"emai_emailaddresssales");
emailvalidation(FormValues("emai_emailaddresssupport"),"emai_emailaddresssupport");

phonevalidation(FormValues("phon_countrycodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));
phonevalidation(FormValues("phon_areacodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));
phonevalidation(FormValues("phon_numberbusiness"),CRM.GetTrans("Link_CompPhon","Business"));

phonevalidation(FormValues("phon_countrycodefax"),CRM.GetTrans("Link_CompPhon","Fax"));
phonevalidation(FormValues("phon_areacodefax"),CRM.GetTrans("Link_CompPhon","Fax"));
phonevalidation(FormValues("phon_numberfax"),CRM.GetTrans("Link_CompPhon","Fax"));

phonevalidation(FormValues("phon_countrycodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));
phonevalidation(FormValues("phon_areacodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));
phonevalidation(FormValues("phon_numberisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

phonevalidation(FormValues("phon_countrycodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));
phonevalidation(FormValues("phon_areacodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));
phonevalidation(FormValues("phon_numbermodem"),CRM.GetTrans("Link_CompPhon","Modem"));

phonevalidation(FormValues("phon_countrycodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));
phonevalidation(FormValues("phon_areacodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));
phonevalidation(FormValues("phon_numbertoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

phonevalidation(FormValues("phon_countrycodehome"),CRM.GetTrans("Link_PersPhon","Home"));
phonevalidation(FormValues("phon_areacodehome"),CRM.GetTrans("Link_PersPhon","Home"));
phonevalidation(FormValues("phon_numberhome"),CRM.GetTrans("Link_PersPhon","Home"));

phonevalidation(FormValues("phon_countrycodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));
phonevalidation(FormValues("phon_areacodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));
phonevalidation(FormValues("phon_numbermobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

}




Validating Changes to Existing Phone and Emails

We can validate updates using an "Entity Level script with Rollback" defined on the email table. See the listing of secondary entities within the Administration>Customization area

The helper functions would need to be included as the above example.

The UpdateRecord() event function can be then defined as:



function UpdateRecord()

{

// Handle update record actions here
emailvalidation(FormValues("emai_emailaddressbusiness"),"emai_emailaddressbusiness");
emailvalidation(FormValues("emai_emailaddresssales"),"emai_emailaddresssales");
emailvalidation(FormValues("emai_emailaddresssupport"),"emai_emailaddresssupport");

phonevalidation(FormValues("phon_countrycodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));
phonevalidation(FormValues("phon_areacodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));
phonevalidation(FormValues("phon_numberbusiness"),CRM.GetTrans("Link_CompPhon","Business"));

phonevalidation(FormValues("phon_countrycodefax"),CRM.GetTrans("Link_CompPhon","Fax"));
phonevalidation(FormValues("phon_areacodefax"),CRM.GetTrans("Link_CompPhon","Fax"));
phonevalidation(FormValues("phon_numberfax"),CRM.GetTrans("Link_CompPhon","Fax"));

phonevalidation(FormValues("phon_countrycodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));
phonevalidation(FormValues("phon_areacodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));
phonevalidation(FormValues("phon_numberisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

phonevalidation(FormValues("phon_countrycodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));
phonevalidation(FormValues("phon_areacodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));
phonevalidation(FormValues("phon_numbermodem"),CRM.GetTrans("Link_CompPhon","Modem"));

phonevalidation(FormValues("phon_countrycodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));
phonevalidation(FormValues("phon_areacodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));
phonevalidation(FormValues("phon_numbertoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

phonevalidation(FormValues("phon_countrycodehome"),CRM.GetTrans("Link_PersPhon","Home"));
phonevalidation(FormValues("phon_areacodehome"),CRM.GetTrans("Link_PersPhon","Home"));
phonevalidation(FormValues("phon_numberhome"),CRM.GetTrans("Link_PersPhon","Home"));

phonevalidation(FormValues("phon_countrycodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));
phonevalidation(FormValues("phon_areacodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));
phonevalidation(FormValues("phon_numbermobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

}



This will now ensure that Phone Numbers may only be integers.


Comments
  • Hi Jeff,

    I tested the above on SageCRM7c on the update record part on phone numbers. With entity level woth roll back, nothing happen. With table level, after saving changes to phone number, will have the expected error message BUT in person summary. The phone number is still saved with the wrong format.

  • I have tested the script using Sage CRM 7c and there is no problem.  You do need to make sure that the script has been fully entered with the help function, the insertrecord function and updaterecord function correctly defined.  The full "entity level with rollback" script would look like

    ////////////////////Helper Functions Start///////////////////////

    function emailvalidation(x,y)

    {

    if (x.length>0)

    {

    apos=x.indexOf("@");

    dotpos=x.lastIndexOf(".");

    lastpos=x.length-1;

    if (apos<1 || dotpos-apos<2 || lastpos-dotpos>3 || lastpos-dotpos<2)

    {

    Valid = false;

    ErrorStr = CRM.GetTrans("colnames",y)+ " ["+x+"] "+ CRM.GetTrans("GenCaptions","BadMailAddress");

    }

    }

    }

    function phonevalidation(x,y)

    {

    re =/[^0-9]/;

    r = x.match(re);

    if (r)

    {

    Valid = false;

    ErrorStr = CRM.GetTrans("colnames",y)+ " ["+x+"] "+ CRM.GetTrans("Errors","InvalidIntegerValue");

    }

    }

    /////////////////////Helper Functions End///////////////////////

    function InsertRecord()

    {

    // Handle insert record actions here

    emailvalidation(FormValues("emai_emailaddressbusiness"),"emai_emailaddressbusiness");

    emailvalidation(FormValues("emai_emailaddresssales"),"emai_emailaddresssales");

    emailvalidation(FormValues("emai_emailaddresssupport"),"emai_emailaddresssupport");

    phonevalidation(FormValues("phon_countrycodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));

    phonevalidation(FormValues("phon_areacodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));

    phonevalidation(FormValues("phon_numberbusiness"),CRM.GetTrans("Link_CompPhon","Business"));

    phonevalidation(FormValues("phon_countrycodefax"),CRM.GetTrans("Link_CompPhon","Fax"));

    phonevalidation(FormValues("phon_areacodefax"),CRM.GetTrans("Link_CompPhon","Fax"));

    phonevalidation(FormValues("phon_numberfax"),CRM.GetTrans("Link_CompPhon","Fax"));

    phonevalidation(FormValues("phon_countrycodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

    phonevalidation(FormValues("phon_areacodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

    phonevalidation(FormValues("phon_numberisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

    phonevalidation(FormValues("phon_countrycodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));

    phonevalidation(FormValues("phon_areacodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));

    phonevalidation(FormValues("phon_numbermodem"),CRM.GetTrans("Link_CompPhon","Modem"));

    phonevalidation(FormValues("phon_countrycodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

    phonevalidation(FormValues("phon_areacodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

    phonevalidation(FormValues("phon_numbertoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

    phonevalidation(FormValues("phon_countrycodehome"),CRM.GetTrans("Link_PersPhon","Home"));

    phonevalidation(FormValues("phon_areacodehome"),CRM.GetTrans("Link_PersPhon","Home"));

    phonevalidation(FormValues("phon_numberhome"),CRM.GetTrans("Link_PersPhon","Home"));

    phonevalidation(FormValues("phon_countrycodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

    phonevalidation(FormValues("phon_areacodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

    phonevalidation(FormValues("phon_numbermobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

    }

    function PostInsertRecord()

    {

    // Handle post insert record actions here

    }

    function UpdateRecord()

    {

    // Handle update record actions here

    emailvalidation(FormValues("emai_emailaddressbusiness"),"emai_emailaddressbusiness");

    emailvalidation(FormValues("emai_emailaddresssales"),"emai_emailaddresssales");

    emailvalidation(FormValues("emai_emailaddresssupport"),"emai_emailaddresssupport");

    phonevalidation(FormValues("phon_countrycodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));

    phonevalidation(FormValues("phon_areacodebusiness"),CRM.GetTrans("Link_CompPhon","Business"));

    phonevalidation(FormValues("phon_numberbusiness"),CRM.GetTrans("Link_CompPhon","Business"));

    phonevalidation(FormValues("phon_countrycodefax"),CRM.GetTrans("Link_CompPhon","Fax"));

    phonevalidation(FormValues("phon_areacodefax"),CRM.GetTrans("Link_CompPhon","Fax"));

    phonevalidation(FormValues("phon_numberfax"),CRM.GetTrans("Link_CompPhon","Fax"));

    phonevalidation(FormValues("phon_countrycodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

    phonevalidation(FormValues("phon_areacodeisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

    phonevalidation(FormValues("phon_numberisdn"),CRM.GetTrans("Link_CompPhon","ISDN"));

    phonevalidation(FormValues("phon_countrycodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));

    phonevalidation(FormValues("phon_areacodemodem"),CRM.GetTrans("Link_CompPhon","Modem"));

    phonevalidation(FormValues("phon_numbermodem"),CRM.GetTrans("Link_CompPhon","Modem"));

    phonevalidation(FormValues("phon_countrycodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

    phonevalidation(FormValues("phon_areacodetoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

    phonevalidation(FormValues("phon_numbertoll_free"),CRM.GetTrans("Link_CompPhon","Toll_Free"));

    phonevalidation(FormValues("phon_countrycodehome"),CRM.GetTrans("Link_PersPhon","Home"));

    phonevalidation(FormValues("phon_areacodehome"),CRM.GetTrans("Link_PersPhon","Home"));

    phonevalidation(FormValues("phon_numberhome"),CRM.GetTrans("Link_PersPhon","Home"));

    phonevalidation(FormValues("phon_countrycodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

    phonevalidation(FormValues("phon_areacodemobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

    phonevalidation(FormValues("phon_numbermobile"),CRM.GetTrans("Link_PersPhon","Mobile"));

    }

    function DeleteRecord()

    {

    // Handle delete record actions here

    }

  • Hi Jeff,

    Would this same code apply to 7.1C?

  • Gavriel

    Yes.  This works fine in Sage CRM v7.1

  • Hi Jeff,

    i have this Tablelevelscript on Person Entity:

    function mailOrPhoneRequired(field,Translation,type)

    {

    if(field.length==0)

    {

    Valid=false;

    var message = (type==1) ? "Phone" : "E-Mail ";

    message += Translation +" is required";

    ErrorStr= message;

    }

    }

    function InsertRecord()

    {

    mailOrPhoneRequired(FormValues("phon_countrycodebusiness"),CRM.GetTrans("Link_CompPhon","Business"),1);

    mailOrPhoneRequired(FormValues("phon_areacodebusiness"),CRM.GetTrans("Link_CompPhon","Business"),1);

    mailOrPhoneRequired(FormValues("phon_numberbusiness"),CRM.GetTrans("Link_CompPhon","Business"),1);

    mailOrPhoneRequired(FormValues("persEmai_EmailAddressBusiness"),CRM.GetTrans("Link_PersEmai","Business"),2);

    }

    function UpdateRecord()

    {

    mailOrPhoneRequired(FormValues("phon_countrycodebusiness"),CRM.GetTrans("Link_CompPhon","Business"),1);

    mailOrPhoneRequired(FormValues("phon_areacodebusiness"),CRM.GetTrans("Link_CompPhon","Business"),1);

    mailOrPhoneRequired(FormValues("phon_numberbusiness"),CRM.GetTrans("Link_CompPhon","Business"),1);

    mailOrPhoneRequired(FormValues("persEmai_EmailAddressBusiness"),CRM.GetTrans("Link_PersEmai","Business"),2);

    }

    If i now edit a Person i get still a validation error which says that i have to put something in Mailaddress. How could that come? The person is linked to a company which have phone and mail, also the person has a phone number and email address.

  • Ah okay i forgot that UpdateRecord Scripts should be added on E-Mail Entity Level script! Now it works as promised.

  • Excellent!

  • Hi Jeff!  Is your original advice - for v6.2 - still valid for 7.1 SP1 with the new changes?  "When entering a phone number the user should type the "simple" number—we can include spaces but it is best to exclude symbols. For example 44 161 5527789."

    I have a client that wants to have dashes auto fill in the phone fields, as their ERP system does.  In addition, they are looking to go to CTI in the future, so I don't want to put anything in place that would compromise that.  Thanks.

  • My advice would still stand.  But it absolutely depends on the CTi system that you are using and the symbols that it supports.  For example if you are using the QGate intelliCTi system to manage the integration of Sage CRM with your PBS then you need to check their documentation.

  • Jeff,

    I assume this script will NOT work with 7.2.f.1.  I put it in place and it has no effect whatsoever.  Can validation of emails and phones be done in 7.2?  I have a client that wants to make some of them required.

    Dana

  • Dana

    This does work!  It works on Sage CRM 2017 R1 too.  

    For example  if you are working on the validation of the 'update' of phone numbers.  Then

    1) Create an Entity Level Script with Rollback under the Email secondary entity

    Administration -> Customisation -> Email

    2) Add the Helper functions at the top of the Script.  Make sure you level in all the four existing event functions  BUT

    3) Change the UpdateRecord event function to the code given above.

    The code will then work whether you are changing the email in the context of the company or of the person.

    An alternative approach is actually to use the Client Side API.  If you have development partner rights you can download a code library that shows hoe to do that here:  community.sagecrm.com/.../default.aspx

  • Hi,

    I tried to use this code to prevent duplicated emails on persons.

    So I replaced the emailvalidation function by this one:

    /////////////////////Helper Functions Start///////////////////////

    function emailduplicatecontrol()

    {

    var cEmail="";

    var CNameE="";

    cEmail=new String(FormValues("emai_emailaddressbusiness"));

    if(cEmail=="null" || cEmail=="undefined" ||cEmail=="")

    {

    cEmail=new String(Values("emai_emailaddressbusiness"))

    if(cEmail=="null" || cEmail=="undefined" ||cEmail=="")cEmail="";

    }

    //for Email Id

    var Email="select RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName from vpersonEmail(nolock) left outer join Person(nolock) on ELink_RecordID=Pers_personId where Emai_EmailAddress='"+cEmail+"'";

    selectquery = eWare.CreateQueryObj(Email);

    selectquery.SelectSql();

    if(!selectquery.eof)

    {

    CNameE=new String(selectquery("pers_fullname"));

    if(CNameE=="null" || CNameE=="undefined" ||CNameE=="")CNameE="";

    Valid = false;

    var Message="";

    Message+="This Email address is already used for another contact  ["+CNameE+"]<br />";

    ErrorStr+=Message;

    }  

    }

    ////////////////////Helper Functions End///////////////////////

    function InsertRecord()

    {

    emailduplicatecontrol();

    }

    function UpdateRecord()

    {

    emailduplicatecontrol();

    }

    When editing an email, I always get the error message, even if the email address does not exist. It seems CNameE is the current person record, even if the query shouldn't get a result ( because the email address does not exist)

    When creating a new person with a duplicated email the Entity level script does not fire

    Any ideas why it doesn't work?

  • You need to make sure that you have code added to the correct Entity and that it is defined as an Entity Level Script with Rollback.  The Event Function to use is the insert record NOT the post-insert event function.