[SalesForce] Creating a Last Activity Date Formula Field

I'm looking to use Last Activity Date for a report that I am creating for my Sales team, but it does not appear to populate correctly. At least not according to what I've read in the literature posted above. That being the case, I need to write a formula field that pulls the last date an Activity Log was created. But I'm not entirely sure I know how to write that logic. 

I tried a simple formula to just include the 
Activity_Log__r.Call_Date__c
But that does not populate. Unless I need more parameters. I would like it to choose the most current of potentially multiple dates. 

Any help with this would be appreciated!

Best Answer

I'd like to preface my answer with a warning that this is probably not going to be possible. That being said, here's some assumptions I've made (please correct if required) and an explanation for why it's not possible (without code):

Assumptions

  • You're trying to create 'Last Activity Date' on Account
  • Activity Log is some custom object that replaces the standard Task object
  • Activity Log has a lookup or master-detail field to Account

If you are using Activity Log for activities, this is why 'Last Activity Date' is not working. It's configured to look at the standard object, not the 'Activity Log' object that you're using, and this can't be changed.

The function 'Last Activity Date' performs is quite special, and can't be copied with a formula field. Formula fields allow us to look 'up' our data schema (e.g. display information from an Account on an Opportunity page, by using 'Account.Name' in a formula field on Opportunity) but not 'down'. This is because looking upwards points directly to one record, or none if the field is null. Looking down is problematic because there could be one, none, or many records that are children of 'this' record. In the case of your Activity Logs, there could be one, none, or many (hopefully) logs against your Accounts - and the formula field doesn't know which one you want to take data from.

The one hope you have is that the relationship between Activity Log and Account is a master-detail (if you don't know how to check this, or perform any of the actions I'm about to describe, please reply and I can assist). If it is, you can create a roll-up summary field on Account that looks at all Activity Logs and performs one of four options (count, sum, min, or max). The operation you're looking for is 'MAX'. This allows you to pull the 'highest' number or date from all child records (Activity Logs) and display it on the parent record (Account), and as you're looking for the most recent activity, that means you want the MAX of Activity_Log__c.Call_Date__c.

Good luck!

Related Topic