Capturing Last Activity Date in Microsoft CRM 4.0

A frequent request that we get from customers is the ability to capture the date of the last activity associated with a contact or account.  This can be very useful to identify which of your contacts or accounts are not being touched very frequently.

Thanks to the improvements in Microsoft CRM workflow in version 4.0, this is now easier than ever.  Here's how:

1.  Create a date attribute on the contact called "last activity date"

2.  Create a new workflow in CRM workflow associated with one of the activity types, such as phone call.  Make this workflow a create workflow under automatic workflow options.

3.  Insert a step to update records.  For the record type to update, select regarding (contact). Click set properites to open the record configuration screen.  This will open the contact form.

4.  Select the last activity date field.  Using the form assistant, in the "look for" section, select phone call and actual end (since this is a phone call activity, we want to record the date the call was completed, not the date it was created, as phone calls are frequently scheduled before they are performed.

5.  Save and close your new workflow, and don't forget to publish it.

Now follow steps 2-5 for the other activity types (e-mail, task, activity, etc). 

To make it more interesting, add a last activity date field to the account.  Then create a workflow that updates that field whenever the last activity date field on one of its associated contacts is updated.  Now your account can reflect the "true" last activity date, rolled up from all of its associated contacts.

Comments

[John Ball]

Just one Comment

Prior to updating the Regarding Entity I would check for a field on that entity containing data (e.g. CreatedOn)

By peforming this check you avoid the workflow rule waiting if for instance a phone call is made against another entity than the one you are updating

[Al]

Suppose my phone call involves 2 people? The regarding field can only hold one name... how do I update the other contact?

[Joel Lindstrom]

Al,

Good question--you can only set an activity regarding one contact. You can specify multiple contacts in the recipient field; however, you cannot update the contact based on them being in the activity recipient field.

You could, however, do it with a report. Basically you would need to join filteredactivityparty to contact id to get the list of activities that the contact is a recipient in, and also join it to FilteredActivityPointer and select either the Max(createdon) or Max(modifiedon) to get the most recent activity date.

Customer Effective can assist with custom reporting for Microsoft Dynamics CRM. Visit our web site at http://www.customereffective.com/index.asp?n=14&p=0&s=14 for contact information.

[Hann]

This is a very helpful and simple to use workflow. I was able to get the Last Activity Date field working okay for Contacts, but I cannot seem to figure out how to create the workflow to work for Accounts also.

Can you please start me off in the right direction? Thanks!

Hann

[Joel Lindstrom]

What I would do is also create a last activity date field on the account and make a workflow that runs on update of the contact last activity field that updates the last activity field on the account with the date from the contact. This will roll the date from contact to the account.

Then do a workflow like the contact workflow with a check condition steps to see if activity the accountid contains data and if so, update the account last activity field.

[Hann]

Thank you so much! That worked perfectly :)

[Karen]

Hi,

I'm hoping you can help. I've set up the Last Activity date attribute, put it on the contact form and created the workflow as described. The workflow succeeds but the field does not get updated on the form. It should be SO simple and it is driving me crazy! (Possible hint...the "regarding contact" does not show any workflow against it)

Any ideas?

[Joel Lindstrom]

OK, so just to confirm, your workflow is set to run against an activity entity (phone call, email, task, etc) upon create of the record and update the last activity date on the regarding contact, right?

Also, you are including the contact in the regarding field? It doesn't work if the contact is just a recipient.

The regarding contact would not show any workflow against it--the workflow is running against the activity.

[Kenny]

This post was very helpful for appointments and phone calls. However, one hurdle I am trying to overcome is that emails do not have the regarding set by default. Ideally, emails would automatically trigger the workflow and update the lead/contact. This becomes tricky when you must set the regarding properly before sending the email, and you have to set the regarding to the lead/contact you want to update (an email to a lead regarding a product wont update the lead).

Any ideas to help improve emails updating the Last Activity Date?

[Alex Nosiara]

If you want to update Last Activity Date when an email merge occurs, it works but runs very slowly. An email merge that used to takes minutes now takes hours to complete! Any thoughts on why this is slow?

[Joel Lindstrom]

Alex,

I suspect what you are running up against is a bottleneck in the asynchronous processing service. Both workflows and direct email use the asynchronous processing service, and if you are doing a bulk email to hundreds or thousands of people, there will be a large number of workflows and emails in the pipe. Before you had just the direct emails going out, now for each one you have an email and a workflow.

The good news is that this is scalable--you can do things like load balancing multiple crm servers, or if you have the enterprise version, separate the asynchronous service on its own server to increase the speed of the asynchronous processing service.

Or you could look at a different way of determining last activity date, such as a SSRS report.

[David]

What do you do with PhoneCalls created by campaigns?
The regarding would no long then be a Contact?

[Joel Lindstrom]

good question David. Campaign e-mails are not set regarding contacts, they are set regarding the campaign. So no, this approach would not be optimal if you have a lot of campaign e-mails and want to update a last activity date on the contact.
What I would do if that is your scenario is to do a SQL query that joins the activitypointer to activity party and gets the last activity per contact (since activityparty would include the e-mails where the contact is a recipient but not the regardingobject). Then I would have a data update job using scribe, SSIS, import utility, or some other data update tool, update the contacts with the last activity date.