Save Me from Last Activity Date

posted Dec 30, 2019, 7:30 AM by Ezra Kenigsberg   [ updated Jan 2, 2020, 9:18 AM ]

Q:    How do I determine the last time a user completed an Activity tied to a record?


A:    DO NOT USE Salesforce’s built-in Last Activity Date. Last Activity Date can occur in the future.¹

INSTEAD: Define better Custom Fields on Activity, and create a Report from a Custom Report Type to roll that field up to the desired object. Perform these five steps:


  1. Create the Custom Date Formula Field “Date MARKED Complete” on Activity:

DATEVALUE(IF(IsTask,

             CompletedDateTime,

             IF(ActivityDateTime + DurationInMinutes / 1440 < NOW(),

                ActivityDateTime + DurationInMinutes / 1440,

                Null)))


  1. Create the Custom Checkbox Formula Field “Has Date MARKED Complete?” on Activity²:
    NOT(ISBLANK(Date_MARKED_Complete__c))

  1. Create the Custom Number Formula Field “Days Since MARKED Complete” on Activity²:

IF(Has_Date_MARKED_Complete__c,

   TODAY() - Date_MARKED_Complete__c,
  999)

  1. Create a Custom Report Type that summarizes your object (I'm using Opportunities in this example) with and without Activities:


  1. In a Report built from that Custom Report Type, create a Custom Summary Formula called “Days Since Last Activity MARKED Complete”.

    If you want the Report to return a Null if no Activity has been marked Complete for this Opportunity, use this formula:

IF(Activity.Has_Date_MARKED_Complete__c:MAX = 1,
  Activity.Days_Since_MARKED_Complete__c:MIN,
  Null)


If you want the Report to return a 999 if no Activity has been marked Complete for this Opportunity, use this formula:

Activity.Days_Since_MARKED_Complete__c:MIN




¹ 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 make an Opportunity’s Last Activity Date return a future date.


² Q:   Why are these fields needed?


A:   Two reasons:
  1. Reports can’t roll dates up to parent records, so we need to convert “Date MARKED Complete” into the numeric “Days Since MARKED Complete”.

  2. 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.

Comments