Problem
You want to count the number of duplicate Opportunities on an Account so you can either clean up unneeded Opportunities, or find out what’s happening in the sales process to cause duplicates to happen.
Solution
You will need to create two rollup settings, two number fields, plus a formula field.
Create two number fields on Account. One called “Total Number of Opportunities” and one called “Number of Unique Opportunities.”
The first rollup will be to get the Total Number of Opportunities.
- Select Account as the target object
- Use the new number field “Total Number of Opportunities” as the target field
- Use Opportunity as the Source Object and the standard AccountID field as the Relationship field
- The Rollup Type is Count, the source field is left as the Default IsDeleted.
- There are no filters or other configurations to the setting.
- Save and run your setting
The second rollup will be to get the Number of Unique Opportunities. It determines the uniqueness of the Opportunity based on its Name, but if necessary more complex criteria could be added.
- Select Account as the target object
- Use the new number field “Number of Unique Opportunities” as the target field
- The Source Object is again Opportunity, and relationship field is AccountId.
- The Rollup Type is Count, however this time change the Source Field to be Name, and select the Unique values only Checkbox as well. This will exclude any duplicates from being included in the count.
- There are no filters or other configurations to the setting.
- Save and run your setting
The third field used in this use case is a formula field that returns a number called “Number of Duplicate Opportunities” on Account. The formula for this field is as follows:
Total_Number_of_Opportunities__c - Number_of_Unique_Opportunities__c
By subtracting the Total Number of Opportunities from the Number of Unique Opportunities, we are able to determine the Number of Opportunities that are duplicates.
