Validating Phone Numbers and Email Addresses

4 minute read time.
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.

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 (apos3 || lastpos-dotpos
{
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.


  • 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.

  • 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+"]
    ";

    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?

  • 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

  • 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

  • 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.