Counting Tasks with DLRS

      48 Comments on Counting Tasks with DLRS

ObHow many tasks have been done in X time frame is a common ask from marketing or sales.  Before it would require a report to answer but now with DLRS we can have that answer live on the record itself.  In this example I’ll go over Task Counting for Leads.

The theoretical ask this DLRS can solve for is this.  Someone would love to know how many tasks in the last week have been done on a Lead.   Even better they would love to get a quick count of how many tasks are on that Lead that are not from some sort of automated system.  Well with DLRS you can accomplish that pretty easily.  The goal is so an SDR can create a List View that only pulls in leads that have not been contacted by a person more than 2 times in the last week.

First step is on the Lead lets create a numeric field and name it Completed Tasks In The Last Week.  Lets make the API name of the field DLRS_Completed_Tasks_In_The_Last_Week.  Next lets go and create our DLRS.  I’ll use the Custom Metadata layout as that seems to be the new hotness and most likely the future of DLRS.

  1.  This is the Parent Object as we want to roll the count up to the Lead Record
  2. Activities/Task live on the Task Object
  3. WhoID is the field that relates Tasks/Activities to the Lead Object.  It is a strange field here is a data map of it
  4.   This is where all of our business logic will live.  We will go through these step by step for this use case.
    1. For this use case we only want to select Tasks that have been completed.  So we say that IsClosed must be TRUE.
    2. We also don’t want to count ones that our Marketing Automation system creates automatically but WE DO want to count those that are created when a rep sends an email through in this case Marketo Sales Insight.  So we know that when a Rep creates a task via MSI Marketo creates it but ASSIGNS it to the Rep.  When Marketo creates a Task from a marketing email or activity it assigns the task to itself.  In this case the Assigned To field is OwnerID so we say that the OwnerID is NOT EQUAL to the ID of our Marketo user.
    3. We don’t want to count things that were set as complete further in the past then last week so we say ActivityDate >= LAST_WEEK.  We also don’t want to count things in the future where someone might have entered in the wrong value so we use ActivityDate <= TODAY.  We also don’t want to count things where there was no Date filled in so you can’t see it in the screenshot but there is ActivityDate != Null also.  (If you wanted to get fancy you could set up a workflow rule to stamp the date when an activity is completed and use that as your criteria so you could have people leave the due date in the future to show they completed something early).
    4. When we put it all together the field ends up reading as so
      IsClosed = TRUE AND OwnerId != ‘a145000000CDmlQ’ AND ActivityDate >= LAST_WEEK AND ActivityDate <= TODAY AND ActivityDate != Null
  5. This is where we put in all of the fields we use in #4 on separate lines, in this case IsClosed, ActivityDate and OwnerID
  6. We just want a count of the records so we just can put ID here and it will count how many ID’s are returned.
  7. We want to Count how many records are found
  8. This is where we want to store the results on our Lead Record.  In this case we will use the field we just created DLRS_Completed_Tasks_In_The_Last_Week__c
  9. I want this to run in realtime
  10. I want this to bypass any sort of task privacy I might have set up in my org so I have it set to run as System

Once you have this record saved, you can deploy the child trigger via the manage child trigger button then go back and activate the rollup.  Now you will also want to click Calculate to backfill all the records, this might best be done at night as it will take a while to run through all your lead records.  Then you can build a Lead List view for the SDR’s where the DLRS_Completed_Tasks_In_The_Last_Week__c <= 2.  As this is a realtime Rollup that has a date field as a criteria you might want to set up a schedule Calculate to re-run all leads where that value is above 2 every Sunday night so come Monday it is a fresh value for the SDRs to work.

 

48 thoughts on “Counting Tasks with DLRS

  1. ErikN

    Can you count tasks by Type? Our Marketing dept, would like to see how many total lead touches occurred but also see a count by type.

    Reply
    1. Dan Post author

      You sure could, you would just add to the relationship criteria the type of task you would like to count. So say you have 3 types you’d have 3 DLRS’s one that counts each different type.

      Reply
  2. Mark Deuel

    You aren’t able to report on tasks after a certain amount of time. Would the ability to do a DLRS on tasks have the same limitation?

    Reply
    1. Dan Post author

      Yup with the checkbox All Rows it will grab archived tasks just be warned that checkbox will also pull up things in the trash bin.

      Reply
  3. Mark Deuel

    Sorry Dan, so DLRS will NOT be able to roll-up tasks past a certain time period? Currently our’s is set to 365 days.

    Reply
  4. Rajn Bhagta

    Can we user developer mode insteand of real time mode , actully when i am using real time it is showing some error with meta data api . so i dont want to click on deploy trigger,it tried with developer mode but but every time i need to click on calulate then only it is calculating new job is it possible to make it automatic.how schedule mode is working?

    Reply
    1. Dan Post author

      Developer Mode is another way to have it run in real time. If you want to run recalculates you’ll need to use the schedule calculate mode.

      Reply
  5. Rajn Bhagta

    @dan Thanks for your reply.

    i am not getting option to schedule calculate mode . can you please help me how i can schedule calculate mode.do we need to write batch apex class

    Reply
    1. Dan Post author

      You shouldn’t need to write batch apex at all. There is a button on the DLRS record named “Schedule Calculate”, if you press that button you can set up a custom time frame org wide recalculate. Also if you want to run real time but are running into the Metadata API issue you can just deploy the trigger in Sandbox and then move the trigger over via a changeset and it will still find all active DLRS records that are pointed at that child object.

      Reply
  6. Rajn Bhagta

    Unfortunately i can’t see schedule calculate button on my record.as task is very frequent activity for our org so we cant use real time because it will execute trigger every time when task will be added or deleted it will be really great if you can help me to schedule DLRS .FYI i am using 2.9 version of DLRS.

    Thanks

    Reply
      1. Rajn Bhagta

        Hi Dan ,

        i am using 2.9 version of DLRS , can you please confirm on which version schedule calculate button will be there.can you please help me to setup in developer mode.

        Thanks

        Reply
  7. Jordan

    I’m running into an issue with Shared Activities. I’ve created a DLRS to display the most recent activity on the contact record (the standard Last Activity date field doesn’t meet my needs). However, it appears that DLRS only counts tasks on which the contact is the primary contact. Is there any way to have secondary contacts included as well? Otherwise, I will have to ask my users to duplicate their tasks to get my workflows to trigger correctly.

    Thanks for the help!

    Reply
    1. Dan Post author

      For just moving a single field over on something as heavily used as Tasks I’d recommend using Process Builder. Also the way that Tasks are related to multiple contacts if I recall is a bit strange but I haven’t played around with it much.

      Reply
  8. Leigh-Anne Nugent

    Just discovering DLRS and seeing it’s potential. Thank you for writing such detailed (and current) notes on this!
    Have you come across a use case to identify the most recent Task by type? The end result is to be able to pull a report of all of the lastest Tasks for all Accounts, without having to pull all of the tasks and sorting by date and ignoring all of the older records. Considering this will have to constantly updated as new records come in, is this an APEX solution or could DLRS help here?

    Reply
    1. Dan Post author

      Do you just want the most recent task date on the account or the task id? What is your end goal of the report?

      Reply
  9. Rajn Bhagta

    Hi Dan ,

    i am using 2.9 version of DLRS , can you please confirm on which version schedule calculate button will be there

    Reply
  10. Rajn Bhagta

    hi dan,

    i am getting “Attempt to de-reference a null object
    An unexpected error has occurred. Your solution provider has been notified. (dlrs) ” error when i am trying to click on rollupscheduledcalculate vf page preview option. can you please help me with above error

    Reply
  11. Scott Condello

    Hi Dan,

    Campaign Attendance Rollup on Contact. How would I map this correctly?
    The board is trying to show membership engagement for workforce development purposes for either monthly and Year to Date purposes.

    Reply
    1. Dan Post author

      What field on the Campaign Member tracks Attendance? Youd want to Sum the Id of Campaign Members, use the ContactId as the relationship field. Parent Object would be CampaignMember and child would be Contact. You’d want your relationship criteria to filter for those who have attendance as attended however you track that on the object.

      Reply
  12. Paul Jackson

    HI Dan, do you know if it’s possible to reference another field in the relationship criteria, instead of a static value? For example, could you count only the Tasks that have been created by the current record owner? Something like [Account].OwnerId = WhoId? I don’t think so, but figure I’d ask.

    Reply
    1. Dan Post author

      Yeah bring over a formula field that either holds the OwnerId and then do ThatField__c = WhoId.

      Reply
  13. Dennis

    I am trying to count the activity types for my sales team; call, voicemail, email, text, etc. I am running into the issue where I am exceeding the character count under the Relationship Criteria field.

    IsClosed = TRUE AND (Activity_Type2__c = ‘Call’ OR Activity_Type2__c = ‘Voicemail’ OR Activity_Type2__c = ‘Text’ OR Activity_Type2__c = ‘Email’ OR Activity_Type2__c = ‘Connect Negative’ OR Activity_Type2__c = ‘Connect Positive’ OR Activity_Type2__c = ‘Booked Demo’).

    How should I revise the field in order to get it under 255 characters?

    Reply
    1. Dan Post author

      You could use an in statement but it sounds like you are on an older version. One of the more recent updates moved that field to be a long text field that supports more characters I think.

      Reply
    1. Dennis

      Still getting an error

      Error:
      Relationship Criteria: Relationship Criteria ‘IsClosed = TRUE AND “Activity_Type2__c” IN (‘Call’, ‘Voicemail’, ‘Text’, ‘Email’, ‘Connect Negative’, ‘Connect Positive’, ‘Booked Demo’)’ is not valid, see SOQL documentation http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_conditionexpression.htm, error is ‘line 1:105 no viable alternative at character ‘“”

      Reply
    1. Dennis

      I dropped the ” from the field name and am still getting the same error

      Error:
      Relationship Criteria: Relationship Criteria ‘IsClosed = TRUE AND Activity_Type2__c IN (‘Call’, ‘Voicemail’, ‘Text’, ‘Email’, ‘Connect Negative’, ‘Connect Positive’, ‘Booked Demo’)’ is not valid, see SOQL documentation http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_conditionexpression.htm, error is ‘line 1:127 no viable alternative at character ‘‘”

      Reply
      1. Dan Post author

        try this

        IsClosed = TRUE AND Activity_Type2__c IN (‘Call’, ‘Voicemail’, ‘Text’, ‘Email’, ‘Connect Negative’, ‘Connect Positive’, ‘Booked Demo’)

        looks like you have extra ‘ at the start and end

        Reply
  14. Dallis

    Thanks so much for this doc! I’ve been using it a lot to get DLRS set up on our org.
    Would it be possible to roll up the most recent “Last Contacted Date” (a custom field) from all contacts up to an account level using this?

    IE each contact has a “Last Contacted Date” (June 1 2018, June 30 2018 and June 16 2018 for 3 different contacts). I want a field on the Account Level that would show June 30 2018 as that one is the most recent date. Is that possible using this?

    Reply
    1. Dan Post author

      Yup you should be able to do this no problem. A rough outline for you to get started with

      Parent Account
      Child Contact
      Last Contacted Date
      Order By Last Contacted Date
      Last

      Relationship Criteria – Last Contacted Date != ”
      Relationship Criteria Field – Last Contacted Date

      Reply
    1. Dan Post author

      Press the calculate button. If you want it to do an entire recalculate on a schedule use the schedule calculate button.

      Reply
  15. Austin Marcus

    Hello,
    First of all, thank you for making this app. I am trying it out and still going through the learning curve, I am excited about seeing the result and making this work.
    I have managed to activate a DLRS without errors: basically counting the number of Tasks based on two Types Call and Call without VMail, and then rolling it up on the Lead object, however, I am not seeing any results in the field on the Lead object. Is there a waiting time after trigger is deployed?
    I would like to paste a snapshot but not able to do so :-/

    Reply
    1. Dan Post author

      Hi Austin, I didnt create the app, just a happy supporter of it. Andrew Fawcett created the app.

      Once you deploy the trigger when the data will be updated is based on a few things. 1)Is it set to realtime? 2)Did you back calculate old data? 3)Is it working? 😉

      I’d recommend coming to the SFDC Community page on DLRS for more specific troubleshooting help.
      https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000009O5p

      Reply
  16. Mitch

    Hi Dan,

    Having some problems with the Relationship Criteria. We want to exclude the tasks that are both (TaskSubtype = Cadence AND Subject begins with “Call:”) but still count these if only one of the criteria apply.

    Any ideas on how to write this?

    Reply
    1. Dan Post author

      You might want to reach out to the Community, not sure SOQL supports begins with, you might need to use LIKE and some wildcards

      Reply
  17. Jeanne Busch

    Hi Dan,
    I am trying to use your excellent article to do a variation on counting — that is, finding the last completed task and transferring a field (called Next Steps) to the parent.

    In this case the parent is Account, so I’m using WhatID. The only criteria is that it’s a completed task so I’m using IsComplete = TRUE

    I’m aggregating the “next steps” field on the child object and aggregating by ActivityDate. Choosing “Last”. Putting the result (i.e. the Next Steps field on the child) into the DLRS Next steps field on the parent. Or at least, that’s what I think I’m trying to do. It doesn’t seem to be working.

    Do you see any immediate problems?
    Thanks so much. Generally I love this app and love your help with it!

    Reply
    1. Dan Post author

      Hey Jeanne id post to the sfdc community for troubleshooting help where you can post your dlrs and the error your getting.

      Reply
  18. Randy Tung

    Hi Dan,

    Are we able to use fields created in Activity as Relationship Criteria Fields?
    I want to use some custom fields as criteria to filter the records I want.

    Reply

Leave a Reply to Dennis Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.