Activity coding in Dynamics CRM

At a previous job, we had used GoldMine (version 6.7 was where we parted ways) as our CRM system before moving to Dynamics CRM. At the time - maybe it's still done this way - GoldMine allowed you to code activities with a hierarchy of references, codes and results. We used reference to separate activities either by department or broad functional grouping (e.g. "service" was a separate reference although there was no separate "service" department). Codes were the specific kinds of activities or client touch points grouped under a reference, and the results indicated how the activity concluded.

The reference/code/result structure allowed us an incredible level of granularity for tracking all the times we either interacted with an external stakeholder (client, prospect, partner, external service provider) or did something on its behalf. We were able to compile detailed "production" reports for our call center reps, and the activities' structure gave them a framework for approaching their routine daily responsibilities.

Here's a simple reference/code/result example:

  • Reference - Sales
  • Code - 1PC//Prospecting call_ (In GoldMine everything before the // was treated as the picklist entry's primary key, so the 1PC would be stored in the database. We would then speak of 1PC calls as a shorthand.)_
  • Result - 1LM//Left message

We tracked everything this way. Despite us still using GoldMine 6.7 in some parts of our business all the way into 2008, activity tracking was great except for two problems. First, although the codes existed in our processes as a logical hierarchy, GoldMine didn't understand that. If you picked the "sales" reference, you still had to weed through all the codes and results that corresponded to all the other references. Second, adding new reference/code/result entries in a supported manner took a lot of time.When we started considering the move to CRM 3 in 2005, I found the default activity entity structure to be something of a letdown, so I brought the reference/code/result framework over when we actually did move. Here's how I did it.

First, I created three separate picklist attributes in CRM on the six main activity entities we used - appointment, e-mail, fax, letter, phone call and task. Let's call them lucasreference, lucascode and lucas_result. I then populated them with the existing references, codes and results from GoldMine. The following are not the actual codes, but they'll give you a decent idea of how things were structured.

References (lucas_reference)
Sales
Service

Codes (lucas_code)
1CD//Contract discussion
1PC//Prospecting call
1PE//Prospecting e-mail
1FC//Follow-up call
1FE//Follow-up e-mail
1SC//Suspect call
2CI//Service call inbound
2EI//Service e-mail inbound
2FU//Service follow-up

Results (lucas_result)
1CF//Contract finalized
1UP//Understands product
2CS//Customer satisfied
2CU//Customer unsatisfied
2IE//Issue escalated
2IR//Issue resolved
CRS//Call rescheduled
CMP//Completed
EMI//E-mail in
EMO//E-mail out
PCI//Phone call in
PCO//Phone call out
LVM//Left message

In this example, codes and results starting with 1 are children of the "sales" reference. Codes and results starting with 2 are children of the "service" reference. Codes and results starting with letters are general options available to any reference.

At this point, allow me to point out a few things. First, we didn't use the existing CRM service activity, and we rarely (practically never) used the fax or letter entities. Why we didn't is beyond the scope of this blog post.Second, the reference/code/result picklist entries were duplicated across all six activity entities even if they didn't make sense. That is, you could have a task with a result of "PCO//Phone call out." Because we had several hundred options, it was easier to make sure every activity had everything than to weed out the ones that didn't fit. Also it made report writing simpler.Third, the general options were handy for when we didn't have a firm process that applied to the activity. For example, sometimes on the service side, all we could do was note that a call came in.Fourth, reference and code were business required. Result was not because you obviously wouldn't know a scheduled activity's result at the point in time when you created it.

Once I created and populated the picklist attributes, I added them to the entities' forms, which was simple enough, but you'd still have the GoldMine problem of every code and result being available for any reference. I took care of that with a bit of JavaScript.The following example is based on something I picked up from "Working with Microsoft Dynamics CRM 3.0" by Mike Snyder and Jim Steger.

var objRef = crmForm.all.lucas_reference;  
var objCode = crmForm.all.lucas_code;  
var objRes = crmForm.all.lucas_result;  

//get the currently selected code and result values so we don't lose data if we're editing an activity instead of creating a new one  
var curcode = objCode.DataValue;  
var curresult = objRes.DataValue;  

//set the lucas_code and lucas_result picklist options to empy arrays for now  
var oTempArray = new Array();  
objCode.Options = oTempArray;  
var oTempArrayRes = new Array();  
objRes.Options = oTempArrayRes;  

//if a reference is selected  
if(objRef.SelectedText!="")  
{  
//get prefix of codes we're going to enable  
var codePrefix = "";  
var resPrefix = "";  
switch(objRef.SelectedText)  
{  
case "Sales":  
codePrefix = "1";  
resPrefix = "1";  
break;  
case "Service":  
codePrefix = "2";  
resPrefix = "2";  
break;  
}  

//enable the correct code codes  
if(codePrefix != "")  
{  
//loop through the original lucas_code options  
for (var i = 1; i < objCode.originalPicklistOptions.length; i++)  
{  
//if the lucas_code option begins with the codePrefix, add it to the holding array  
if(objCode.originalPicklistOptions[i].Text.indexOf(codePrefix)==0)  
{  
objCode.AddOption(objCode.originalPicklistOptions[i].Text, objCode.originalPicklistOptions[i].DataValue);  
}  
}  

//make sure the picklist is enabled  
objCode.Disabled = false;  

//set the picklist's selected value to what we stored earlier  
objCode.DataValue = curcode;  
}  
else  
{  
//disable the field  
objCode.Disabled = true;  
}  

//enable the correct result codes  
if(resPrefix != "")  
{  
//loop through the original lucas_result options  
for (var j = 1; j < objRes.originalPicklistOptions.length; j++)  
{  
//if the lucas_result option begins with the resPrefix, add it to the holding array  
if(objRes.originalPicklistOptions[j].Text.indexOf(resPrefix)==0)  
{  
objRes.AddOption(objRes.originalPicklistOptions[j].Text, objRes.originalPicklistOptions[j].DataValue);  
}  
//handle some general cases  
if(objRes.originalPicklistOptions[j].Text.indexOf("CRS")==0)  
{  
objRes.AddOption(objRes.originalPicklistOptions[j].Text, objRes.originalPicklistOptions[j].DataValue);  
}  
if(objRes.originalPicklistOptions[j].Text.indexOf("CMP")==0)  
{  
objRes.AddOption(objRes.originalPicklistOptions[j].Text, objRes.originalPicklistOptions[j].DataValue);  
}  
}  

//make sure the picklist is enabled  
objRes.Disabled = false;  

//set the picklist's selected value to what we stored earlier  
objRes.DataValue = curresult;  
}  
else  
{  
//disable the field  
objRes.Disabled = true;  
}  
}  
else  
{  
//no selected reference, no displayed codes or results  
objCode.Options = oTempArray();  
objRes.Options = oTempArrayRes();  

//disable the fields for good measure  
objRes.Disabled = true;  
objCode.Disabled = true;  
)

Add that to the onLoad event for each activity form and the onChange event for the reference field on each activity form, and you're good to go.

Happy activity documenting!

comments powered by Disqus