I saw this request come up again on the Salesforce Community last week and thought that there must be a way to solve it. This is something I see all the time and is a common request.
I believe that much of what I’ve done here can be completed using the Process Builder but I decided to use Andy Fawcett’s Declarative Lookup Rollup Summaries instead because as of right now that’s what I’m more comfortable with. For those that want to try this with Process Builder and Flows, take a look at Michael Gill’s solution and see if you can get a hybrid version going. Hopefully I’ll find some more time soon and can take a stab at that as a follow-up post to bring the solution completely in-house
Those of you that have ever tried will know that you can’t do Time-Based Workflow Rules on the Last Activity Field. This comes through the community all the time, people wanting to automate follow-up emails and task alerts 30 days after the last activity, that kind of thing. It was bugging me so I decided to try and see if I could solve it declaratively. One of the big problems is that Activities are notoriously picky when it comes to Roll-Ups because the “Name” and “Related To” fields are polymorphic. Name can be a Lead or a Contact, and Related To can be all sorts of things.
I’ve already talked about time based workflows a little bit and how they can solve the issue of birthday reminders here. They are basically a way to queue a workflow activity at a future date. In the birthday example, it would be to remind me in 360 days time that I really need to buy my mother a gift before her birthday this year, instead of buying and putting it in the mail the day of, or a few days after.
I’m also using an object called Test Object as my master in this case. Another useful thing about Dev Orgs is you aren’t trying to get items to work in a fully functional and customized object. You can quickly create a basic object like this, get it working, and then all you have to do to take it live is troubleshoot any other customizations that might get in the way. At that point you have proven that it can work, you may just need to adapt it for your production / work environment.
What is Last Activity Date
The actual definition is described here
Basically the MAX of Closed Tasks and any Events for a particular record. You can’t show it on the Page Layout but you can create a quick formula field to show it. I created one called fLastActivityDate just to monitor what the system was calculating and what I was able to calculate to make sure that they stayed in line with each other. Some people don’t even consider this field to be accurate but whatever it is, people quickly start to rely on it in reports and inevitably want to be reminded of things to do based on it.
This is another fun field because it appears as a date, is actually a Date/Time, and can’t be used in SOQL queries for a MAX calculation.
For Tasks – It’s the Due Date of the task
For Events – It’s the start date of the event.
Realistically, this is what I want to take the MAX of for both Closed Tasks and Events for any activity record to get to Last Activity Date.
To do this I had to create a new field called GeoffDate that equals the ActivityDate and is populated on Activity Create or Edit using a Workflow Field Update. All it does is populate with the ActivityDate. For each of these items that I’m creating, I’m using Field Updates instead of formula fields because I need hard-coded values for Time-Based Workflow. There are some exceptions where you can use formulas but in general try to stick with real values.
Rolling Up Activity Date (aka GeoffDate)
For this I used the Declarative Lookup Rollup Summary tool. I touched on this a little bit during my Wine Cellar building (which I promise I’ll get back to again) and it really is a great tool for doing roll-ups in cases where Salesforce doesn’t let you do them natively such as though Lookup relationships, and with Activities where the fields are polymorphic.
First up I had to create two fields on my parent object (I just called it Test Object) which are both Date fields
I then set up my Lookup Rollup Summaries to summarize for Tasks and Events.
Another challenge with doing things like this for Activities is that you often have to do it twice, once for Tasks and once for Events. Bear with me though, we’ll get through this.
Event Rollup (ruEventTestObject)
For this I’m saying roll-up the MAX of GeoffDate__c from Events to Test Object (my master object) based on WhatID as the relationship field (which is the API Name for Related To)
Task Rollup (ruTaskTestObject)
For this I’m saying roll-up the MAX of GeoffDate__c from Tasks but only when IsClosed=TRUE. Remember that Last Activity Date only cares about Closed Tasks. For those of you that haven’t seen IsClosed before, it appears in a few places like Tasks, Opportunities, Cases, as a quick way to show something is closed instead of having to worry about getting the name of the picklist value exactly right. It’s really great for reporting, especially in Opportunities where you will have multiple Closed stages.
You can get really close with ActivityDate and then it puts a hard stop on the Roll-Up which is why I had to create the GeoffDate field to get around that limitation. This now gives me two tangible fields on my Test Object records that I can use to build out my custom LastActivityDate.
Calculating MAX of Rollups in Workflow Rule
The trick here is to get these into a single LastActivityDate field. I called it dlrsLastActivityDate for continuity but it actually has nothing to do with the DLRS tool, it’s just another Workflow Field Update. In this case the formula is:
AND(ISBLANK(dlrsMaxEvent__c), ISBLANK(dlrsMaxClosedTask__c)), NULL,
IF(dlrsMaxEvent__c > dlrsMaxClosedTask__c, dlrsMaxEvent__c, dlrsMaxClosedTask__c
Which is saying:
- If both the Rollup fields are blank, leave the field blank
- Otherwise if the Event Rollup is blank, take the Task Rollup
- Otherwise if the Task Rollup is blank, take the Event Rollup
- Otherwise if they are both populated, check which is bigger and use that
- Otherwise just use the Task Rollup
#5 is the key where we’ve covered all other avenues, so logically the only option left is that they are equal. At that point who cares which one you choose.
Putting it all together
So by the time you get that all running, you are left with this:
I put the X to signify that the Open Task isn’t being calculated. I also pointed out that fLastActivityDate is really just there for reference. When you are trying to build something like this, it’s always nice to have a field showing to make sure you are doing it right.
When you try to create a Time-Based Workflow rule you’ll notice that you can reference the dlrsLastActivityDate.
You cannot reference Last Activity Date (aka the reason I bothered to write this in the first place)
You can reference the fLastActivityDate but this is really a bit of a glitch. It will only catch it when the rule resets itself (again subsequently meets criteria), but will not “move the needle” every time the Last Activity Date gets pushed out. The dlrsLastActivityDate however will do that which is really what you want. That last one is tricky and I encourage people to try it out to see what I mean:
- Create a Time-Based Workflow that you can get to easily meet and un-meet criteria – use a checkbox or something.
- Create two Time-Based Actions 30 and 60 days out, one from fLastActivityDate and another from dlrsLastActivityDate
- Trigger the workflow so that the actions will hit the Time-Based Workflow queue
- Add a new Event to push out the Last Activity Date and therefore also your two custom fields
- Refresh the Queue
- Notice that the action based on your dlrs field moved with the change, but your fLastActivityDate did not.
This is what I used. The Number1__c can be adjusted to get the Workflow to reset each time as needed.
This seems like a lot of work but it really isn’t that bad and pretty much takes care of itself moving forward. It’s a great way to practice Workflows, Rollups, and gaining a better understanding of Time-Based Workflow rules. This is a request that I see often and also a great way to drive adoption with Activity reporting by end users. Being able to tell them when a client has been dormant for numerous months is a powerful way to remind people that maybe a quick touchpoint call is in order.