x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

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.


Parents

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

Comment

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

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City