Summary:

By default, if you file an email from the Outlook Exchange plugin to CRM without linking it to a Person or a Company record, this record will become an orphan, and will be difficult to find in CRM.

This KBA outlines a method for
a) Making the orphaned emails easier to find
b) Linking them to Person records

Symptoms:

This is a 2 step process - first, we create an Interactive Dashboard gadget to display the records, and then create a field and table level script to link the record.

An enhancement request has been raised to include this functionality in the product.

Work around / Resolution:

WARNING: The techniques or examples contained in this KBA are for illustrative purposes only, and have not been tested in a production environment. The techniques or examples used are not necessarily supported features of Sage CRM, and may not have been previously tested or implemented on a production environment. Before attempting to implement any of these techniques or examples on a production environment, they should be tested thoroughly, as use of these techniques may cause unexpected behaviour. SAGE can accept no responsibility for any issues arising from the use of these techniques. Please ensure that you back up your database and application fully before implementing any changes of this nature.

1: Create a new view that will filter on the orphaned Communication records, and make it available as a Reports View:

create view vCommOrphanedEmail as
select * from vcommunication
where comm_type = N'Email' and Comm_Action = N'EmailIn'
and CmLi_Comm_PersonID is null and CmLi_Comm_CompanyID is null
and Comm_Deleted is null
and CmLi_Comm_UserID is not null

2: In Reports, create a new report called Orphaned Emails, using vCommOrphanedEmail as your source view. Filter it by the current user (and possibly the user's team), and add whatever columns you want visible on the dashboard (Subject, From and To would be a start).

3: Create a new List gadget on Communication, basing it on your Orphaned Emails report. Select the Drill Down as Go to Summary Screen.

At this point, you are somewhat restricted in terms of what can be done with the communications. You cannot generate a new comm_link for the person record (should one be created from within CRM) from the email filing screen. 

A suggestion at this point is to either adding a check box to the Email-In screen (e.g. Link Orphan e-mail), or adding a new SSA field to EmailFilingBox that will act as a flag on the email. You can then run a table level script against the Communication entity that checks if the flag is set. If the flag is set, then we could link the email to a Person or Company record, assuming that one exists in Sage CRM.

Each approach would be different – if a check box is used, the script can choose whether to link the email to a Person or Company record. If you use an SSA, you could decide what record to link, but it would have to be a Person OR Company.

Here's how you could do this using an SSA:

1: Create a new SSA on the Communication entity called comm_linkorphan, and point it at the Person entity.

2: Add the field to EmailFilingBox, and use the following Create script to hide the field, for emails that aren't orphans:

if ((CRM.GetContextInfo("Communication","comm_type")=='Email')
&& (CRM.GetContextInfo("Communication","Comm_Action")=='EmailIn')
&& (CRM.GetContextInfo("Communication","CmLi_Comm_PersonID")=="")
&& (CRM.GetContextInfo("Communication","CmLi_Comm_CompanyID")=="")) {

Hidden = false;
}
else Hidden = true;

This Create script does essentially the same thing as the view earlier.

3: Next, we add a table level script on Communication that checks whether this field is set. If it's set, then we update the primary Comm_Link record for the communication, linking it to the Person entity, and clear the existing comm_linkorphan value.
function UpdateRecord () {
if (IsValid(Values("comm_linkorphan"))) {
var PrimaryCommLink = CRM.FindRecord("Comm_Link", "CmLi_Comm_Communicationid=" + WhereClause.replace(/comm_communicationid\=(\d*).*/i,"$1") + " and CmLi_Comm_UserId is not null");
PrimaryCommLink.item("CmLi_Comm_PersonId") = Values("comm_linkorphan");
PrimaryCommLink.SaveChanges();

Values("comm_linkorphan") = "";
}

}
function IsValid(str) {

str = str + "";

if ((str!="undefined") && (str != "null") && (str != "")) {

return true;
}
else return false;
}