Easily Create a Custom Last Activity (Task/Event) Date
Problem
Salesforce has a Last Activity field available, but it is not usually configurable to most customers needs.
Solution
Use Rollup Helper to aggregate latest completed task or event date for any object into a new Last Activity Date field.
In order to do this in Rollup Helper, first you will need to to note that in Salesforce an Activity consists of 2 different objects:
- Task
- Event
Before getting into Rollup Helper first you will need to create 3 fields on your target object:
- A date field to hold the Last Task Date
- A date field to hold the Last Event Date
- A formula field to return the most recent date of the 2. This formula will return a date value and can be called Last activity date. The formula will be as follows:
AND(
ISBLANK(Last_Task_Date__c),
ISBLANK(Last_Event_Date__c)
),
NULL,
DATE(1900, 01, 01) +
MAX(
BLANKVALUE(Last_Task_Date__c , DATE(1900, 01, 01)) - DATE(1900, 01, 01),
BLANKVALUE(Last_Event_Date__c, DATE(1900, 01, 01)) - DATE(1900, 01, 01)
)
)
The above formula will return the larger of the 2 date values and will take into consideration blank values in case there is not a task or event attached to the record.
Once you have your fields created, you can create your Rollup Helper Settings. The process will be the same for both objects, the only different will be the source object selected:
- Select Target Object, will be the object you are rolling up to, where you created your fields from step 1. For this example we can use Account
- Select Target Field, will be either the Last Task Date, or Last Event Date field depending on which object you are rolling up
- Select Source Object, will be either Event, or Task depending on which object you are rolling up
- If rolling up to Account, the relationship field would be Accountid, if its Contact you can use WhoId
- The rollup Type will be Max. This will return the most recent value.
- The Source Field can be ActivityDate, or whichever date field makes sense for your use case
- If you only want to include closed activities, phone call, emails, etc you can use the create new filter button and define filter criteria in Step 3 of the filter
- If you only want to include Activities in the past, you can use a filter criteria like where ActivityDate < Today. When a date based filter like this is used, you will need to have the rollup run on a schedule in addition to real-time since the passing of time will not fire an Apex Trigger.
Once your filter criteria has been completed you can Save and Run your setting in order to update all historical records in your org. The Task and Event objects have a trigger in the package, so they will run in real-time without having to deploy a trigger. If a relative date was used in the filter like ActivityDate < Today you will also want to schedule the rollup at this time.
You can hide the 2 Rollup result fields from your page layout if your users do not need to see them, and just have your formula field shown on the page layout if desired.
Calculating Dates Automatically | Rollup Helper Use Case Library | USE CASE: Creating Account Hierarchy Rollups without Coding |