Number of Open Projects on an Opportunity
There are situations where actionable data is required within Salesforce to drive workflows, increase user productivity or to aid in reporting. The solution is sometimes that a roll-up summary field is needed, but that is not always possible. Some scenarios where roll-up summary fields can not be used include:
- Custom objects with lookup relationships
- Filter by Formula rather than Static Criteria
An example of this would be the need to count the number of open Projects (custom object) on an Opportunity that have not yet begun but have a start date (kickoff) that is less or equal to Today.
Use Lookup Helper to automatically populate the Opportunity lookup field on Project for data accuracy as well as user productivity, then use Rollup Helper to count the number of open Projects on the Opportunity.
This use case is using a custom object, Project with the Project Name being the same as the Opportunity Name for simplicity. Depending on your use case, the lookup setting may be a bit different. For assistance finding matching fields for your use case, please contact: firstname.lastname@example.org
A bit of setting up:
- Both Rollup Helper and Lookup Helper are needed for this use case. If you have not already done so, please install the Free Edition of each app from the AppExchange:
- Create a lookup relationship on Project to Opportunity and assign it to your layout(s) if you don’t already have one, and add the Project related list to Opportunity.
- Create a number field on Opportunity, Number of Open Projects and add it to your page layout(s).
Create the Lookup Helper Setting
- Select Child Object: Project
- Select/Create Lookup Field to be Used:
- Maintain Existing Lookup Relationship
- Select Lookup Helper Setting Type
- Field Matches Name/Id
- Project Name
- Save and Run
- Enable the real-time trigger for Project in the Realtime Enablement tab.
Create the Rollup
For this use case, we would like to count the number of open Projects that have not yet begun. There are several ways to create the filter or fields/criteria that could be used depending on fields that are in your environment. One example is being given.
- Select Destination Object: Opportunity
- Select Target Field: Number of Open Projects (the number field you created)
- Select Source Field:
- Child Object: Project
- Make sure your desired relationship field is selected - for this use case the relationship field that was created for the lookup from Project to Opportunity is being used.
- Select Type: Count
- A default field will be selected to use for the count - you can change it if you would like by using the Change Source Field check box.
- Create New Filter
- Kickoff or Start Date <= Today
- Actual Finish Date = Null
- Save the Filter
- Recommended - Schedule the rollup. Even though rollups fire in real-time, they have to be triggered by a change. When using date filters, it is a best practice to also use a schedule because it is possible that if a child record wasn’t edited, the rollup results wouldn’t be updated on the parent and may no longer fit your date criteria.
- Run the rollup.
- Enable Real-Time for Project
Please contact email@example.com for assistance with your use case.
Stay up to date - join our Helper Suite Success Communities:
Reference Links - Salesforce Ideas:
- Roll-Up Summary Fields: Formulas Rather than Static Criteria
- Eliminate Need for Master-Detail Relationship for Roll-ups
What custom objects would you like to be able to count records of - or extensions of this use case do you have? Please let us know by commenting below.
|Monthly or YOY Reporting in Salesforce with Rollup Helper & Lookup Helper||Rollup Helper Use Case Library||Roll-ups Using Formulas Rather than Static Criteria|