function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
JMoretzJMoretz 

Roll-up Summary and filtering on Custom Formula Field

I've searched around, but I can't find a solution to this:

In an effort to bypass the fact the Opportunity Products cannot be referenced in a formula, I'm attempting to use a Roll-Up Summary field, but its not working as I had hoped. My steps are:

1) In "Products", there is a Customer Checkbox labeled "Beta"

2) In "Opportunity Products", there is a Custom Number (0 decimal places)  Formula field named "Betalook". The formula states:

IF(PricebookEntry.Product2.Beta__c,1,0)

 So, any "Opportunity Product" entry whose Price Book item has the "Beta" box checked will show a 1 in this field, and a 0 otherwise.

 

In "Opportunities", I want to create a Custom Roll-Up Summary field ("Betacount") that will count the number of Opportunity Product entries that have a 1 in the "Betalook" field.

1) I start a new Custom Field in Opportunity, choosing "Roll-Up Summary" as the type

2) I name it "Betacount"

3) I choose "Opportunity Product" as the Summarized Object and "COUNT" as the Roll-Up Type.

4) I choose the "Only records meeting certain criteria..." radio button.

The problem is that in my choices for "Field" in the criteria, the "Betalook" field is not available. It is also not an available field in the "Field to aggregate" picklist if I choose SUM, MIN, or MAX. The Roll-Up Summary help files states: "Roll-up summary fields can calculate the values of formula fields if they do not contain functions that automatically derive values on the fly, such as NOW or TODAY." So, why can't I filter on the field? Any help would be greatly appreciated.

 

 

Best Answer chosen by Admin (Salesforce Developers) 
rockchick322004rockchick322004

Your formula field has a spanning (cross-object) reference in it, which is why it is not available.  I think that info is in the help doc in the Cross-Object Formula topic, but not the Rollup Summary Fields topic.  I will log a doc bug to update the Rollup Summary Fields topic.

 

I know that this is a common business need, to summarize by Products, and that we have reports to provide this data analysis.  Due to the nature of the complexity of the relationship, this is something that cannot be supported with Rollup Summary Fields.

All Answers

rockchick322004rockchick322004

Your formula field has a spanning (cross-object) reference in it, which is why it is not available.  I think that info is in the help doc in the Cross-Object Formula topic, but not the Rollup Summary Fields topic.  I will log a doc bug to update the Rollup Summary Fields topic.

 

I know that this is a common business need, to summarize by Products, and that we have reports to provide this data analysis.  Due to the nature of the complexity of the relationship, this is something that cannot be supported with Rollup Summary Fields.

This was selected as the best answer
JMoretzJMoretz
Thank you for the quick response. I guess I'll have to hope Salesforce pick up my "allow Opportunity Products to be referenced via formula" Idea.
gailhgailh

Can you request again that this get added to the rollup summary help topic - that would have saved myself and SFDC support a lot of time.

beezbeez
I had a similar problem and solved it using workflow rules.  instead of making the field "Betalook" a formula, make it a text field.  Then use workflow rules to do a field update into "Betalook" using your formula.     You will need to use the data loader to reset the value for BetaLook on any existing opportunity product records, but for new records that are added, the value will be set automatically in "BetaLook".  Since "BetaLook" field type is now text instead of formula, you can use it in your roll-up summary criteria.
faquino316faquino316

Seems like so much work for something that should be standard. I had to do the same.