Sage CRM v7.1: Changing code to cope with the effect of normalisation.

Hints, Tips and Tricks

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

Sage CRM v7.1: Changing code to cope with the effect of normalisation.

  • Comments 9
  • Likes

 

Normalisation has been carried on the company, person, phone and email tables in Sage CRM v7.1.  In previous versions the default phone, fax and emails have been copied from the phone and email tables into columns on the company and person tables.

In Sage CRM v7.1 the Phone and Email columns have been dropped from the company and person tables and new link tables (PhoneLink, EmailLink) have been added to make the phone and email work in a similar way to the address and address_link tables.

This new data structure is more flexible in design and opens up the possibility of storing phone and email information against other entities including custom entities.

The changes to the data model are discussed in the Database Changes in Sage CRM v7.1 reference document available to members of the Developer Program.

Generally it is not expected that users will notice any change in the behaviour of the application because the core and system views have been changed to allow the system to function as previously.

There is a tool called the CheckPhoneEmail.exe which can help us work out what needs to be reviewed and I will write about that in another article. It is covered in the What's New in Sage CRM v7.1 Document so if you can't find any other article do read that!

What I want to consider in this article is code that you may have in either script (ASP or Table Level) or in .NET projects.  This is because I have had to rewrite portions of ASP pages that carried out work around the phone and email tables during my revision of my training materials.  And example is the Component available to members of the Developer Program called Component: Sage CRM v7.1, Clone a Person.

Below are two sections of javascript.  The first is from Sage CRM v7.0 and the second is from Sage CRM v7.1

Sage CRM v7.0 Example

[code language="javascript"]
////////////////////////////////////////////////////////
//start copying person phone numbers
////////////////////////////////////////////////////////
strClause = "phon_type in ('Home', 'Mobile') and phon_personid =" + recOldPerson.pers_personid;
var recPhoneNumber = CRM.FindRecord("phone",strClause);
var recNewPhoneNumber;
while (!recPhoneNumber.eof)
{
recNewPhoneNumber = CRM.CreateRecord("phone");
recNewPhoneNumber.phon_personid = recNewPerson.pers_personid;
recNewPhoneNumber.phon_companyid = recNewPerson.pers_companyid;
recNewPhoneNumber.phon_countrycode = recPhoneNumber.phon_countrycode;
recNewPhoneNumber.phon_areacode = recPhoneNumber.phon_areacode;
recNewPhoneNumber.phon_number = recPhoneNumber.phon_number;
 
recNewPhoneNumber.phon_type = recPhoneNumber.phon_type;
recNewPhoneNumber.SaveChanges();
 
recPhoneNumber.NextRecord();
}
////////////////////////////////////////////////////////
//stop copying person phone numbers
////////////////////////////////////////////////////////

////////////////////////////////////////////////////////
//start copying person email
////////////////////////////////////////////////////////
 
strClause = "emai_type = 'Private' and emai_personid =" + recOldPerson.pers_personid;
var recEmail = CRM.FindRecord("email",strClause);
var recNewEmail;
 
while (!recEmail.eof)
{
recNewEmail = CRM.CreateRecord("email");
recNewEmail.emai_personid = recNewPerson.pers_personid;
recNewEmail.emai_companyid = recNewPerson.pers_companyid;
recNewEmail.emai_emailaddress = recEmail.emai_emailaddress;
recNewEmail.emai_type = recEmail.emai_type;
recNewEmail.SaveChanges();
 
recEmail.NextRecord();
}
////////////////////////////////////////////////////////
//stop copying person email
////////////////////////////////////////////////////////
[/code] 

Sage CRM v7.1 Example

[code language="javascript"]
////////////////////////////////////////////////////////
//start copying person phone numbers
////////////////////////////////////////////////////////
 
var strClause = "plink_type = 'Home' and plink_entityid = 13 and plink_recordid =" + recOldPerson.pers_personid;
var recPhoneLinks = CRM.FindRecord("phonelink",strClause);
while (!recPhoneLinks.eof)
{
recOldPhone = CRM.FindRecord("phone","phon_phoneid="+recPhoneLinks.plink_phoneid);
var recNewPhone = CRM.CreateRecord("phone");
recNewPhone.phon_countrycode = recOldPhone.phon_countrycode;
recNewPhone.phon_areacode = recOldPhone.phon_areacode;
recNewPhone.phon_number = recOldPhone.phon_number;
recNewPhone.SaveChanges();
 
var recNewPhoneLink = CRM.CreateRecord("phonelink");
recNewPhoneLink.plink_entityid = 13;
recNewPhoneLink.plink_recordid = recNewPerson.pers_personid;
recNewPhoneLink.plink_type = 'Home';
recNewPhoneLink.plink_phoneid = recNewPhone.phon_phoneid;
recNewPhoneLink.SaveChanges();
 
recPhoneLinks.NextRecord();
}
 
////////////////////////////////////////////////////////
//stop copying person phone numbers
////////////////////////////////////////////////////////
 
////////////////////////////////////////////////////////
//start copying person email
////////////////////////////////////////////////////////
 
var strClause = "elink_type = 'Private' and elink_entityid = 13 and elink_recordid =" + recOldPerson.pers_personid;
var recEmailLinks = CRM.FindRecord("emaillink",strClause);
var recEmail;
while (!recEmailLinks.eof)
{
recOldEmail = CRM.FindRecord("email","emai_emailid="+recEmailLinks.elink_emailid);
var recNewEmail = CRM.CreateRecord("email");
recNewEmail.emai_emailaddress = recOldEmail.emai_emailaddress;
recNewEmail.SaveChanges();
 
var recNewEmailLink = CRM.CreateRecord("emaillink");
recNewEmailLink.elink_entityid = 13;
recNewEmailLink.elink_recordid = recNewPerson.pers_personid;
recNewEmailLink.elink_type = 'Private';
recNewEmailLink.elink_emailid = recNewEmail.emai_emailid;
recNewEmailLink.SaveChanges();
 
recEmailLinks.NextRecord();
}
 
////////////////////////////////////////////////////////
//stop copying person email
////////////////////////////////////////////////////////
[/code] 

The new Link tables will make you have to check your code around these areas.  The same question work apply to any .NET code too.

You will need to check all these different code locations where work is carried out on the affected tables

  • ASP Pages
  • Table Level Scripts
  • Create & Validate Scripts
  • External Scripts (that use an external logon)
  • .NET code
  • Self Service pages

 

Comments
  • Nice article Jeff! Can I just mention that the magic number 13 in your script is the entity id of the Person table - you may want to double check on your install that you have got the right number here as it could be different.

    select bord_tableid from custom_tables where bord_name = 'person'

    Also if you are adding phone or email link records for other entities e.g Company or Account then you need to look up the right entity id for them too.

  • See, that's why you should always state clearly your assumptions!  And I didn't - because the article used this example of was "cloning a person" I assumed that people would recognise 13 as the magic number for the person entity in a default system.

  • Jeff, line 16 and 44 of your 7.1 example references the address_link table. Is that correct?? Should it not read phonelink on line 16 and emaillink on line 44?

  • I think you are correct and I have corrected the code and will now pretend that this never happened.

  • Jeff, is it possible to create PhoneLink and EmailLink using the webservice addrecord method?

  • The PhoneLink and EmailLink tables are not exposed by default to webservices.  Therefore you will not be able to use AddRecord with them.  Better to use the Add() method.

  • Jeff, the last comment by you (11/10/2011 9:54AM)...is this correct?

    We've just added '7.1 service reference' to a new project and neither phonelink nor emaillink are exposed as entities. Further both the email and phone classes still have 'type' properties (as per 7.0), and neither classes have properties to relate them back to their entity.

    Unless I am completely missing something, all this means that you need to use the AddRecord!

  • FYI, there is a little typo: recOldEmail = CRM.FindRecord("email","email_emailid="+recEmailLinks.elink_emailid); won't work, as the field is "Emai_emailid" not "Email_emailid". Thanks Jeff, this saved me having to hand craft this code.

  • Thanks!  I have now further, further edited this code to remove the pesky error. It never happened.