Roll-up summary fields are a very useful Salesforce feature, but you may come across a situation where you need to rollup the value of a formula field. Here is an example: You have added a custom field to the Product object to record the cost of a product. You want the total cost of all products added displayed on an opportunity. In the screenshot below you can see Widget A has a cost of $50.
When we add a product to an opportunity, we want the product cost displayed. So we add it to the related list.
There is a solution, however it may not apply to all situations. To be able to rollup the value of the cost field, we need to copy it to a custom currency field on the opportunity product record. Then we can use that field for the rollup summary.
Now with the three widget products added to the opportunity, the opportunity value is automatically calculated and displayed in the opportunity value field.
So, we now create a roll-up summary field and try to add our Product Cost field. However, as the formula field is referring to a field in another object (e.g. cross object reference), it is not available for use.
There is a solution, but it will only apply in certain situations. To be able to rollup the product cost field, we need to copy the value from the formula field to a custom currency field on the opportunity product object. We can then use that field for the rollup summary. To do the copy we use a workflow rule and field update.
First we create the custom currency field on the opportunity product object.
Next we create the workflow rule and field update.
So for testing purposes, we add the new field to the related list.
At this point, there is no value in our new field, as the workflow rule did not exist when we added the products. To get the value populated, we need to remove the products and add them again. Now the workflow rule runs when we add the product and you can see below that the field has been populated.
Now we can go back to adding the roll-up summary field. Our custom field is available to select.
And finally, here is the sum of the product cost on the Opportunity Layout.
Now as mentioned earlier, this solution may not work for all situations. The reason being, that the value of the formula field is copied when the opportunity product record is created or edited. That means, for example if the product cost is updated after the product is added to the opportunity, the formula field will show the updated value, but the custom field that we added for use in the rollup summary will not.
That would be preferable in our case, as we would want the cost of the product when it was added to be recorded on the opportunity and used for the rollup. If the cost was updated months later, we wouldn’t want that change reflected on our previous opportunities and this is a simple solution that doesn’t need code and uses standard Salesforce workflow. There maybe other cases where the current value is always required, or required for any open opportunities. In that case, you would need a trigger to update the opportunity product record when a change is made to the object that the formula sources its value from.