Roll-ups Using Formulas Rather than Static Criteria

Problem

There are many scenarios in Salesforce where formula fields are required. Having to create Roll-up Summary fields without the ability to also use formula fields in your criteria presents a challenge. One of the big limitations with the native Roll-up Summary fields is that they can only be created with certain types of formula fields applied in the roll-up criteria. If there are any formula fields that contain a reference to date functions within them, they will automatically be filtered out of the available list of fields to use in your roll-up summary criteria. For example, date-based criteria such as Today, Last N Days, or This Year help pinpoint specific results but can not currently be used in Roll-up Summary field criteria. According to this Idea, “Roll-Up Summary Fields: Formulas Rather than Static Criteria”, the status is “No Plans to Implement”, so you will need to find an alternative solution to get past this limitation.

Solution

Fortunately, Salesforce gave us the AppExchange, where you can find pre-built solutions. Rollup Helper can be used to roll-up information using date-based criteria, included any criteria already applied in an existing formula field. One of the use cases in the Idea mentioned above is to “Create a Roll-Up Summary field on Opportunity which provides a COUNT of all related Projects where Start Date is less than or equal to TODAY and Actual End Date is NULL (blank)”.

Let’s walk through setting up this use case with Rollup Helper.

If you have not already done so, install Rollup Helper from the AppExchange.

First, create a custom number field on Opportunity, “Number of Open Projects”. This can either be done in Setup or from within the Rollup Helper app.

On the Rollup Helper tab, click “Create a New Rollup”

1. Select the object where you would like to see the results of the roll-up: Opportunity
2. Select the field you created on Opportunity: Number of Open Projects
3. Define the roll-up
1. Which object would you like to get data from: Project
2. Verify the defaulted relationship being used or select a different one (if you have more than one available)
3. Select the type of roll-up you would like to do: Count
1. Optionally change the source field. For count roll-ups, Rollup Helper defaults to the “IsDeleted” field because it is guaranteed to be populated for every record. A filter is automatically added to the roll-up so that any records in the recycle bin will not be counted.
4. Click “Create New Filter”
1. Give the filter a meaningful name: Open Projects
2. Since this is a count rollup, no sort order is needed
3. Specify filter criteria
1. Kickoff (Start Date), less or equal, Today, Submit
2. Actual Finish Date (Actual End Date), equals, Null/Empty, Submit
5. Save the filter
6. Apply a schedule to run the rollup daily since the criteria is based on “Today” and your newer projects in the future may eventually be in the past. This will ensure that all Project records will still be accurately included or excluded in the rollup results.
7. Give the roll-up a meaningful name: Number of Open Projects on Opportunity and enter a description.
8. Save and Run the rollup.
9. Enable Real Time
1. From the Enable Real Time tab, select Project
2. Click Deploy (your trigger name)
3. Remain on the page until the trigger deploys

Need assistance with this, or any other use case? We are happy to help.

Schedule a free one-on-one work session.