Roll-up Solution When a Roll-up Summary Field Can't be Used



Free Salesforce business guides to help admins/developers, project managers, sales managers, and decision makers. Download the free PDF series now: Reinventing Your Business, Reimagining Your Salesforce®.

In Salesforce, many businesses need to see aggregated data from child records on a parent record. For example, to show the sum or average of closed won Opportunities on a Custom Object, like Industry. They may always want to choose the first or last child record, and from that selection, display a key piece of text, such as the first Campaign that triggered a response at an Account. This sounds like it should be straight-forward to put together, but it could be more difficult to create than you'd think.

Roll-up Summary fields are great for rolling up data, but according to Salesforce’s help documentation, roll-up summary fields have limited functionality such as:

  • Lookup relationships are not supported.
  • Date Literal filters can't be added.
  • Campaign member custom formula fields that reference fields derived from leads or contacts are not supported.
  • Summary types are limited to Count, Sum, Min, and Max. Text, Average, and Mode roll-up types are not supported.
  • When you delete a child record on a Roll-up Summary field, Salesforce doesn’t automatically recalculate the value of the field. 
  • You can’t use long text area, multi-select picklist, description fields, system fields like Last Activity, cross-object formula fields, or lookup fields in the field column of roll-up summary filters.
  • Roll-up summary fields are not available for mapping lead fields of converted leads.
  • If a roll-up summary field contains cross-object field references or functions such as NOW or TODAY, it can not calculate the values of the formula fields.
  • If your organization has advanced currency management enabled, currency roll-up summary fields are invalid if they are on accounts and summarizing opportunity values, or on opportunities and summarizing custom object values.
  • You can’t create a COUNT or SUM roll-up summary field that pulls data from a lookup field without selecting an option to disallow the deletion of the lookup record that is part of the lookup relationship.
  • The value in a roll-up summary field changes when the values in the detail records change. So, validation errors can display when saving either the detail or master record.
  • Because roll-up summary fields are not displayed on edit pages, you can use them in validation rules but not as the error location for your validation.
  • Automatically derived fields, such as current date or current user, aren’t allowed in a roll-up summary field. Forbidden fields include formula fields containing functions that derive values on the fly, such as DATEVALUE, NOW, and TODAY. Formula fields that include related object merge fields are also not allowed in roll-up summary fields.
  • When you refer to a roll-up summary field in a list view or report, you can’t use certain qualifiers, including:
    • Starts with
    • Contains
    • Does not contain
    • Includes
    • Excludes
    • Within

With the creation of tools on the platform like Flow, customers have started to attempt to simulate roll-up functionality. There are a couple issues that arise here. First, Flow can be complicated to use and take a significant investment in time to learn and troubleshoot. It's also inefficient to create flows for rollups as there's a lot of extra work and duplication of efforts. You need to create two flows for every rollup to ensure data stays accurate: one to trigger when records are created or updated, and another for when records are deleted. Plus, you can’t have multiple run modes at the same time without having duplicated flows (e.g., you can't have real time, invocable, and scheduled runs at the same time).

So, what’s an admin to do?

Install Rollup Helper for free! With Rollup Helper, you can roll up all data types and can use either a Master-Detail or Lookup relationship through an easy to use interface. Filters can be quickly created with Rollup Helper’s filter builder that includes date literal filters. It takes just a few seconds to create a rollup and start using it in a list view, report/dashboard, page layout, validation rule or other process/workflow. You can even use cross-object references and any type of formula field in either the filter part of the roll-up or as the source of the roll-up. With Rollup Helper, the limitations you see with the standard master-detail rollups in Salesforce or other rollup solutions simply do not exist.

Rollup Helper Example

Still looking for a better idea of how you can use Rollup Helper and rollups in your org?

Let’s say you have a Custom Object, Industry, and would like to have an Average Deal Size This Year field available on each Industry record to be used in list views to increase user productivity. This field could be used for Lead Scoring or Lead Assignment Rules, or possibly even to segment customers for Account-Based Marketing. Then, let’s say that you then have an Industry Lookup Field on Opportunity (this can be populated automatically with another Helper Suite app of ours, Lookup Helper).

To set up this scenario with Rollup Helper:

  • Install Rollup Helper for free on the AppExchange.
  • Create a custom currency field on your Industry custom object. Call it ‘Average Deal Size’ (Did I mention this works with advanced currency management enabled?)
  • Create the rollup by opening the Rollup Helper application and performing these steps:
    • Rollup Helper Step 1) Select Destination Object: Industry
    • Rollup Helper Step 2) Select Target Field: The field you created (Average Deal Size)
    • Rollup Helper Step 3) Select Source Field
      • Which object would you like to use as the source of your rollup? (Industry Child)
        • Child Object: Opportunity
        • Relationship: Industry - Opportunity.Industry__c
      • Which field on Opportunity should we use as a source for your rollup?
        • Select Type: Average
        • Field: Amount
    • Create a Filter
      • Step 1. Enter Filter Name
        • Filter Name: Won
        • Limit this rollup to __ child records per parent: Blank
      • Step 2. Specify Sort Order
        • Remove Default Sort Order
      • Step 3. Specify Filter Criteria
        • Select Opportunity Field: Won
        • Select Operator: equals 
        • Select Criteria: True
        • Select Opportunity Field: Close Date
        • Select Operator: equals
        • Select Criteria: Current Year
        • Submit
      • Step 4. Save and add a schedule. Please note, it is a best practice to add a schedule to rollups with rolling date criteria in addition to real-time processing. This is because if a record has not been modified to cause the real-time trigger to fire, the date criteria won’t be automatically re-evaluated. Adding a schedule that coincides with the date filter will ensure data accuracy.
        • Check the Active box.
        • Run Every 1 Years
        • Check Run for all records or select the number of records to process.
        • Next Run: January 1 of the next year.
    • Save and Run the roll-up.
    • Enable real-time for Opportunity if you haven’t already done so.

Rollup Helper's User Interface makes creating rollups in Salesforce a breeze.

Rollup Industry Average Deal Size This Year 

Instead of laboring over trying to reinvent the wheel with Flow, why not save yourself time and frustration and give Rollup Helper a try?

Passage Technology News & Updates – Winter 2024 Passage Technology Blog Ready to Implement Salesforce?