Monday, May 26, 2014

Last Event or Task Date vs. Last Activity Date

Salesforce gives users the ability to run reports on the Last Activity Date for all objects, out of the box. But what if users wanted to distinguish between Last Event Date vs. Last Task Date? Or break up the data by other criteria?

Luckily, the Declarative Rollups for Lookups package (thanks to Andy Fawcett) can solve this problem, with only a few minor tweaks to work around a Salesforce limitation: field ActivityDate does not support aggregate operator MAX.

The general idea is to create two custom fields on the object of your choice: Last Event Date and Last Task Date. Follow these up with two Lookup Rollup Summaries, and you can now easily split your activity data by Tasks vs. Events. The end result? Reports like the following.


Follow this tutorial and give these new fields a try! Please add a comment below to let me know whether this tip works for you.

6 comments:

  1. Marty, this work flawlessly, thank you. Been waiting for something like this for a few years, and in the past few weeks, I've checked off about 10 things on the long-term to-do list thanks to awesome people like you and Andy Fawcett!

    ReplyDelete
  2. Marty, I've uncovered a slight issue...

    If you attempt to create a new Event record and mark it as an all day event, you receive the following error:

    Error: Invalid Data.
    Review all error messages below to correct your data.
    Apex trigger dlrs_EventTrigger caused an unexpected exception, contact your administrator: dlrs_EventTrigger: execution of BeforeInsert caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.dlrs_EventTrigger: line 9, column 1

    Must have something to do with the fact that all-day events don't have a start/end time. I have verified that you can create an Event with a start/end time, then update it to an all-day and no problem. But you can't create an all-day event.

    This is an issue because we sync our users' Outlook calendars, and syncs on new all-day events are generating this trigger issue. Can you suggest a solution/workaround? Thanks you!

    ReplyDelete
    Replies
    1. Hello, Tommy, thank you very much for the feedback, and I apologize for the delayed response. I think updating the Event trigger to handle situations where ActivityDateTime is blank will do the trick. Would you let me know whether this updated code sample works for you?

      Delete
    2. Thanks for your reply, and no worries on the delay. In your absence, I tried out the following code and it seems to work. However, I am not a developer by any means. Would your code be preferable for any reason (like performance, error handling, etc.)?

      if (Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)) {
      for (Event eachEvent : Trigger.new) {
      if (eachEvent.IsAllDayEvent == true){
      eachEvent.Activity_Date_Proxy__c = eachEvent.ActivityDate;
      }
      else{
      eachEvent.Activity_Date_Proxy__c = eachEvent.ActivityDateTime.dateGMT();
      }
      }
      }

      Delete
    3. Hello, Tommy, I think both your approach and mine will achieve the same end result. I'm glad you were able to devise a workaround. :-)

      Delete
    4. Hi Marty,

      I have used this package for count total events on Opportunity. I created a count formula field return type number. Create a number field on opportunity "total Events". It is calculating events counts but with invitees. If an event have 2 invitees so event count showing as 3 but actually it is only one event with 2 invitees so count should be 1.

      Delete