Going Beyond Native Roll-Up Summary Fields

Are you running into limitations with the native Roll Up Summary fields that Salesforce provides? Do you want to include dynamic criteria in a Roll Up Summary field’s filter? Have you been trying to create a report like ‘Accounts with more than 5 open Cases per month‘  with no native workaround whatsoever? Have you voted on the 8 year old idea to no avail? If yes, then I have the perfect solution for you – The Declarative Roll Up Summary Tool developed by Andrew Fawcett (a Salesforce MVP) which lets you use dynamic filter criteria like relative dates (TODAY, THIS_MONTH etc.), create Roll Up Summary Fields for objects connected via a lookup relationship, perform scheduled calculations, use Developer API to call out the Rollup Engine and embed calculations in existing Apex Triggers, perform operations like Count Distinct, Concatenate, Concatenate Distinct, First and Last and provides support for the Process Builder! Isn’t that too much awesomeness in one single tool?

I have used the Declarative Roll Up Summary Tool (DLRS) a ton of times to accomplish requirements that couldn’t be met using the native Roll Up Summary (RUS) fields in Salesforce and as you must have guessed by now – without writing a single line of code (unless you want to refer to a partial SOQL query as code). Being active on the success community makes me aware of scenarios that are most common among multiple other use cases that cannot be met using the native RUS fields. I see at least 1-2 questions everyday concerning business use cases which calls for a versatile and extremely useful tool like DLRS. Based on my research about what people most frequently ask for, I am laying out the five business requirements that DLRS can prove to be useful for:

  1. Roll up Activities to Account

    Use Case : Find the number of Open tasks at the Account level set to ‘High’ Priority
    Success community question How can I count activities for a lead that meet certain criteria?
    Lookup Rollup Summaries record setup :

    High priority Open tasks

    High priority Open tasks

  2. Roll up Notes to Lead

    Use Case : Find the number of notes at the Lead level that contain the word ‘Critical’ in their title
    Success community question : How to count the number of notes and attachments with a record?
    Lookup Rollup Summaries record setup :

    Notes marked Critical

    Note: For counting Notes and Attachments both, you can create another Roll Up field with Attachment as the Child object and then add up the Notes and Attachments rollup fields, if needed. Unfortunately, Salesforce doesn’t let you create triggers on the NoteAndAttachment object so you can’t create a consolidated rollup field to include both.

  3. Roll up Cases to Account

    Use Case : Create a report that shows only those Accounts that have more than 5 open cases in the current month
    Success community question : Filtering a Report
    Lookup Rollup Summaries record setup :

    Open Cases this month

    Open Cases this month

    Note: Once you have done the above, you can then create a report to show Accounts that have more than a certain number of Open Cases per month. You will need to set the Calculation Mode to ‘Scheduled‘ in order to re-calculate bulk records in a batch.

  4. Roll up Contacts to Account

    Use Case : Allow a user to edit a custom field on an Account only if no Contacts exist for the Account OR all Contacts for this Account are inactive
    Success community question : Validation rule using child records
    Lookup Rollup Summaries record setup :

    Number of Inactive Contacts

    Number of Inactive Contacts

     

    Total Contacts

    Total Contacts

    Note: This is what the validation rule will look like:

    AND(
       ISCHANGED(Primary_Domain__c),
       OR( 
          Total_Contact_Count__c= 0, 
          Inactive_Contact_Count__c = Total_Contact_Count__c
       ) 
    )
  5. Roll up Opportunities to Account

    Use Case : Find the number of Opportunities at the Account level that were set to Closed Won today
    Success Community Question : I couldn’t find the question that I recently replied to but trust me, there was one!
    Lookup Rollup Summaries record setup :

    Today's Closed Won Opportunities

    Today’s Closed Won Opportunities

    Note: Yet again, the use of a ‘Scheduled‘ Calculation Mode is called for to run a batch update job every night.

 

So install this incredibly useful package in your Salesforce instance and start reaping the benefits of instant rollup analytics that are an integral part of almost every organization’s data! If you have used this tool for scenarios other than the ones I listed above, please feel free to share them below with a comment.

 

 

  • Balaji Srinivasan

    Hi Mayank, in the validation rule, why do you have this condition? ISCHANGED(Primary_Domain__c) — Wasn’t able to relate to the original qstn.

    • Mayank

      Balaji, the question (click on the success community link) starts with the following statement:
      “We have a custom field called Primary Domain on the Account object. My boss wants me to write a VR that would allow users to edit this field ONLY if….”
      That is why it is a part of the validation rule.

      • Balaji Srinivasan

        Gotcha !!! Thank you.

  • Kelly Armenta

    Hello Mayank,

    If I want to use this with relative date fields, (LAST QUARTER) should I set the Calculation Mode to ‘Scheduled’? One issue I had with this ‘LAST QUARTER’ scenario was that the roll-up worked fine all quarter, but when the quarter changed, the data in the roll-up was not updated–it kept referring to what would then be two quarters ago. If I recalculated the roll-up, it would update to last quarter.

    Thanks,

    Kelly

    • Mayank

      Hey Kelly, this is where the ‘Schedule Calculate’ button comes to the rescue. Use it to recalculate records every night. You can find more info here: http://andyinthecloud.com/

      • Kelly Armenta

        This is great! Thank you for getting back to me.

  • Passage Technology

    Thanks for putting this together Mayank! Great use cases! Don’t forget about another great product, Rollup Helper. Rollup Helper allows you to do all the rollups described in this great blog post + more. The product is also supported. Rollup Helper is also available on the AppExchange here: https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI There is a free version and paid version depending on your needs.

  • Ron

    Hi Mayank
    I’m trying to do exactly what you wrote on the first point (Roll up Activities to Account), but instead of Account–> Lead:Roll up Activities to Lead. But the Declarative didn’t recognize “Leadid” as a relationship field. Can you help me with that?

    Thanks!!

    • Mayank

      For Lead, you would have to use WhoId. Contacts and Leads are referred to as Who ID’s for Activities.

CONTACT US

We're not around right now. But you can send us an email and we'll get back to you, asap.

Sending

© 2017 Succeed With Salesforce | Website by Tonevski.com

Log in with your credentials

or    

Forgot your details?