Q: “When was this Salesforce record last worked?”
A: If you mean “when was an Activity last completed for it?”, I got a great solution for you.
But first, make sure NOT to use Salesforce’s built-in “Last Activity” field (API Name: LastActivityDate). It behaves really strangely, like allowing “Last Activity” to occur in the future.1
(I’ve got a full tirade about it. Once you’re done with that, come on back here to implement a Better Mousetrap.)
1) Create a new Activity Custom Field, with Type “Formula (Date/Time)”, named Date/Time MARKED Complete, with the formula
IF(IsTask, CompletedDateTime, IF(ActivityDateTime < NOW(), ActivityDateTime, NULL))
2) Create a new Activity Custom Field, with Type “Formula (Checkbox)”, named “Has Date/Time MARKED Complete?“, with the formula2
3) Create a new Activity Custom Field, with Type “Formula (Number(0))”, named Days Since MARKED Complete, with the formula2
IF(Has_Date_Time_MARKED_Complete__c, NOW() - Date_Time_MARKED_Complete__c, 999)
4) In a Report that has both Activities and the object you’re rolling up to (like Accounts), create a Custom Summary Formula (I named it Days Since Last Activity MARKED Complete) with this formula
IF(Activity.Has_Date_Time_MARKED_Complete__c:MAX = 1, Activity.Days_Since_MARKED_Complete__c:MIN, NULL)
You’re done! The Custom Summary Formula field enables reporting when someone last completed an Activity for an Account, Contact, Opportunity, Lead, Case, etc. . .
Q: How can Last Activity Date occur in the future?
A: Salesforce Help explains that Last Activity Date is either
- The latest Due Date of the Closed Tasks on a record, or
- The latest Date of Events on a record.
Out-of-box, Salesforce allows both
- completed Tasks to have Due Dates in the future or
- Events to have Dates in the future
. . . so either Tasks or Events can render, say, an Opportunity’s “Last Activity” in the future. wtf.
Q: Why are these fields needed?
A: Two reasons:
- Reports can’t roll dates up to parent records, so we need to convert “Date MARKED Complete” into the numeric “Days Since MARKED Complete”.
- Reports’ Custom Summary Formulas treat nulls as zeroes, so we need an arbitrary, large error value–I’m using 999 here–to disqualify null “Days Since MARKED Complete” from MIN calculations.