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.
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”
Need assistance with this, or any other use case? We are happy to help.
Schedule a free one-on-one work session.