One of the first things I noticed that infuriated me about CRM was trying to figure out when one of the real estate agents in our brokerage actually contacted a lead or account.
You would think the obvious place to look would be the last modified date of the lead or account. This does work if modified a field on these entities directly. But what about completing a task, completing a phone call or any other other activities that you can do in relation to the lead/account? Those each have their own last modified date that is updated seperately that has no correlation to their parent entity.
There may be an easier solution (I didn’t find one!) but how I solved the problem was using a workflow.
To use this solution you will need to add the following attribute to the following entities: lead, account, contact and opportunity. Note: These are the only entities we’re currently tracking. You will need to tweak this for your own environment.
Attribute: Last Activity Date, (new_lastactivitydate), datetime
This is the attribute that our workflow will update whenever anything happens on our tasks, faxes, letters etc.. that are related to this entity.
The next thing you will need to do is create a custom activity using Visual Studio for CRM (Instructions: http://msdn.microsoft.com/en-us/library/cc151142.aspx).
[CrmWorkflowActivity("Update Last Activity", "CRM Fixes")]
public partial class UpdateLastActivity : SequenceActivity
{
private CrmService crmWS = null;
private CrmDateTime currentDT = new CrmDateTime();
public UpdateLastActivity()
{
InitializeComponent();
}
public static void LogError(String ErrorMessage, String context)
{
EventLog el = new EventLog(“Application”);
el.Source = “your-error-source”;
el.WriteEntry(ErrorMessage + “\n Context: ” + context, EventLogEntryType.Error);
}
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
IWorkflowContext context = contextService.Context;
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { “new_lastactivitydate” };
ColumnSet colsRegarding = new ColumnSet();
colsRegarding.Attributes = new string[] { “regardingobjectid” };
ColumnSet colsCustomerID = new ColumnSet();
colsCustomerID.Attributes = new string[] { “customerid” };
ColumnSet colsParentCustomerID = new ColumnSet();
colsParentCustomerID.Attributes = new string[] { “parentcustomerid” };
currentDT.Value = DateTime.Now.ToString();
crmWS = CRMServiceManager.GetCRMService();
crmWS.Url = “http://url-to-your-crm-site/MSCrmServices/2007/CrmService.asmx”;
switch (context.PrimaryEntityImage.Name)
{
case “lead”:
{
lead currentLead = (lead)crmWS.Retrieve(“lead”, context.PrimaryEntityId, cols);
currentLead.new_lastactivitydate = currentDT;
crmWS.Update(currentLead);
break;
}
case “account”:
{
account currentAccount = (account)crmWS.Retrieve(“account”, context.PrimaryEntityId, cols);
currentAccount.new_lastactivitydate = currentDT;
crmWS.Update(currentAccount);
break;
}
case “opportunity”:
{
opportunity fopp = (opportunity)crmWS.Retrieve(“opportunity”, context.PrimaryEntityId, colsCustomerID);
if (fopp == null || fopp.customerid == null)
break;
if (fopp.customerid.IsNull == false)
{
// Retrieve the account for the opportunity
account currentAccount = (account)crmWS.Retrieve(“account”, fopp.customerid.Value, cols);
currentAccount.new_lastactivitydate = currentDT;
crmWS.Update(currentAccount);
}
break;
}
case “contact”:
{
contact fcon = (contact)crmWS.Retrieve(“contact”, context.PrimaryEntityId, colsParentCustomerID);
if (fcon == null || fcon.parentcustomerid == null)
break;
if (fcon.parentcustomerid.IsNull == false)
{
// Retrieve the account for the opportunity
account currentAccount = (account)crmWS.Retrieve(“account”, fcon.parentcustomerid.Value, new AllColumns());
currentAccount.new_lastactivitydate = currentDT;
crmWS.Update(currentAccount);
}
break;
}
case “email”:
{
// Update regarding
email fem = (email)crmWS.Retrieve(“email”, context.PrimaryEntityId, colsRegarding);
// if they didn’t set regarding we do not know who it is
if (fem.regardingobjectid == null)
break;
UpdateParentEntity(fem.regardingobjectid.Value, fem.regardingobjectid.type, context);
break;
}
case “task”:
{
// Update regarding
task ft = (task)crmWS.Retrieve(“task”, context.PrimaryEntityId, colsRegarding);
UpdateParentEntity(ft.regardingobjectid.Value, ft.regardingobjectid.type, context);
break;
}
case “fax”:
{
// Update regarding
fax ff = (fax)crmWS.Retrieve(“fax”, context.PrimaryEntityId, colsRegarding);
UpdateParentEntity(ff.regardingobjectid.Value, ff.regardingobjectid.type, context);
break;
}
case “letter”:
{
// Update regarding
appointment fl = (appointment)crmWS.Retrieve(“letter”, context.PrimaryEntityId, colsRegarding);
UpdateParentEntity(fl.regardingobjectid.Value, fl.regardingobjectid.type, context);
break;
}
case “appointment”:
{
// Update regarding
appointment fa = (appointment)crmWS.Retrieve(“appointment”, context.PrimaryEntityId, colsRegarding);
UpdateParentEntity(fa.regardingobjectid.Value, fa.regardingobjectid.type, context);
break;
}
case “phonecall”:
{
// Update regarding
phonecall fp = (phonecall)crmWS.Retrieve(“phonecall”, context.PrimaryEntityId, colsRegarding);
UpdateParentEntity(fp.regardingobjectid.Value, fp.regardingobjectid.type, context);
break;
}
case “annotation”:
{
ColumnSet colsObjectCols = new ColumnSet();
colsObjectCols.Attributes = new string[] { “objecttypecode”, “objectid” };
annotation fa = (annotation)crmWS.Retrieve(“annotation”, context.PrimaryEntityId, colsObjectCols);
UpdateParentEntity(fa.objectid.Value, fa.objecttypecode.Value, context);
break;
}
default:
{
break;
}
}
return ActivityExecutionStatus.Closed;
}
private void UpdateParentEntity(Guid regardingID, String regardingType, IWorkflowContext context)
{
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { “new_lastactivitydate” };
switch (regardingType)
{
case “lead”:
{
try
{
lead currentLead = (lead)crmWS.Retrieve(“lead”, regardingID, cols);
currentLead.new_lastactivitydate = currentDT;
crmWS.Update(currentLead);
}
catch (Exception exc)
{
LogError(exc.Message, “UpdateParentEntity – lead”);
}
break;
}
case “account”:
{
try
{
account currentAccount = (account)crmWS.Retrieve(“account”, regardingID, cols);
currentAccount.new_lastactivitydate = currentDT;
crmWS.Update(currentAccount);
}
catch (Exception exc)
{
LogError(exc.Message, “UpdateParentEntity – account”);
}
break;
}
case “opportunity”:
{
try
{
opportunity opp = (opportunity)crmWS.Retrieve(“opportunity”, regardingID, cols);
if (opp == null || opp.customerid == null)
break;
if (opp.customerid.IsNull == false)
{
// Retrieve the account for the opportunity
account currentAccount = (account)crmWS.Retrieve(“account”, opp.customerid.Value, cols);
currentAccount.new_lastactivitydate = currentDT;
crmWS.Update(currentAccount);
}
}
catch (Exception exc)
{
LogError(exc.Message, “UpdateParentEntity – opportunity”);
}
break;
}
case “contact”:
{
try
{
contact con = (contact)crmWS.Retrieve(“contact”, regardingID, cols);
if (con == null || con.parentcustomerid == null)
break;
if (con.parentcustomerid.IsNull == false)
{
account currentAccount = (account)crmWS.Retrieve(“account”, con.parentcustomerid.Value, cols);
currentAccount.new_lastactivitydate = currentDT;
crmWS.Update(currentAccount);
}
}
catch (Exception exc)
{
LogError(exc.Message, “UpdateParentEntity – contact”);
}
break;
}
}
}
// The code to actually create the CRM web service in my own hacky way
internal class CRMServiceManager
{
public static CrmService GetCRMService()
{
String UserAccount = “crmaccount”;
String Password = “crmpassword”;
String Domain = “domain”;
CrmDiscoveryService disco = new CrmDiscoveryService();
disco.Url = “http://url-to-your-crm-site/MSCRMServices/2007/SPLA/CrmDiscoveryService.asmx”;
disco.UseDefaultCredentials = true;
//Retrieve a list of available organizations from the CrmDiscoveryService Web service.
RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
// Substitute an appropriate domain, username, and password here.
orgRequest.UserId = UserAccount;
orgRequest.Password = Password;
RetrieveOrganizationsResponse orgResponse = (RetrieveOrganizationsResponse)disco.Execute(orgRequest);
//Find the target organization.
OrganizationDetail orgInfo = null;
foreach (OrganizationDetail orgdetail in orgResponse.OrganizationDetails)
{
if (orgdetail.OrganizationName.Equals(“crm”))
{
orgInfo = orgdetail;
break;
}
}
// Check whether a matching organization was not found.
if (orgInfo == null)
throw new Exception(“The specified organization was not found.”);
//Retrieve a CrmTicket from the CrmDiscoveryService Web service.
RetrieveCrmTicketRequest ticketRequest = new RetrieveCrmTicketRequest();
ticketRequest.OrganizationName = orgInfo.OrganizationName;
ticketRequest.UserId = UserAccount;
ticketRequest.Password = Password;
RetrieveCrmTicketResponse ticketResponse = (RetrieveCrmTicketResponse)disco.Execute(ticketRequest);
//Create the CrmService Web service proxy.
CrmAuthenticationToken sdktoken = new CrmAuthenticationToken();
// 0 ad
// 1 passport
// 2 spla
sdktoken.AuthenticationType = 2;
sdktoken.OrganizationName = “crm”;
sdktoken.CrmTicket = ticketResponse.CrmTicket;
CrmService service = new CrmService();
service.CrmAuthenticationTokenValue = sdktoken;
service.Url = “http://url-of-your-crm-site/MSCrmServices/2007/CrmService.asmx”;
service.Credentials = new System.Net.NetworkCredential(UserAccount, Password, Domain);
return service;
}
}
Once the activity is compiled and registered within CRM you will need to create a new CRM workflow for each and every activity you want to track (task, email, fax, letter etc.. etc..).
For each workflow add the newly created activity. No other conditions/actions are necessary.
The goal of the workflow is when a new activity is created the workflow will run, lookup the “regarding” or parent entity of the letter and then update the last activity date field.
Not a simple solution by any stretch of the imagination. However, with CRM’s architecture of even the simplest things (notes) being completely seperate entities it is the only solution I could find.
Good luck!
P.S. If you are looking for Frisco Real Estate or Homes in McKinney Texas let us know.
We also have a Houston site that services Spring Texas Real Estate and Cypress Homes for Sale.
