+ Start a Discussion
sfdclearnsfdclearn 

Filter Criteria in a Roll up summary Field

We have a Roll Up Summary field in the Account level that calculates SUM(Opportunity) with the following filter criteria.

1. Stage equals Closed/Won,Implementation.

I would like to add another filter criteria here which is

2. Product not equal to 'test'

In this scenario product is a related list to opportunity as its not a field i don't think i can add this filter criteria there.

But i am just wondering if there is anyother ways to do this like creating any formula field or something.

Please advise.

Thanks!

bob_buzzardbob_buzzard

The way I've handled this type of thing before is to:

 

(1) Create a formula field on the product that is 0 if the product isn't 'test' and 1 if it is.

(2) Create a roll up summary field on the opportunity that gives the max of the formula field on the related products (named something like HasTestProduct)

(3) Set the filter criteria on the account roll up summary to only include records where the value of HasTestProduct = 1

 

I don't recall handling this exact problem before though, so caveat emptor!

sfdclearnsfdclearn

I actually tried to create a formula field in opportunity product as when I created the formula field in Product i was not able to summarize the product object when i created the Roll up summary field in opportunity.

 

 Now the problem is when i try to create roll up summary field to find the Max of product  in opportunity with 'Master Object' as Opportunity and

summarized object as 'Opportunity Product'  the custom formula field that in the opportunity product does not show up in the 

field to aggregate.

 

Please advise.

 

Thanks!!!

bob_buzzardbob_buzzard

What is the return type of the formula field?  

bob_buzzardbob_buzzard

Also, what does the formula field do - if it goes cross object (i.e. pulls in information from the product) then it won't be available as a roll up summary field.

Jerun JoseJerun Jose
So I guess we could do a custom field on Opp Line item object and populate it with a workflow field update on record create. The product reference in a OLI cannot change and I suppose they shouldnt be changing the product names, so the approach will still work ..
bob_buzzardbob_buzzard

Spot on.

sfdclearnsfdclearn

I created a workflow rule of opportunityproduct which is

 

    when createddate!=Null

   do a field update ProductCopy(CustomField)= product Name.

 

And after this the formula field gets populated with product name when a new product is added to opportunity. But for existing opportunity product the formula field does not get populated.

 

 

 

Jerun JoseJerun Jose
Yeah. Since we are now following a workflow route, it will need an update event to start off the whole thing. If it is not too much trouble, you could just update all the OppLine item records and that should do.

P.S. I would setup the workflow with a better condition, something like when ProductName = 'Test Product'. Coz we dont want to have unncessary updates right!
sfdclearnsfdclearn

I am updating in the custom text field in opportunity product called product name and that will just update the custom field and not anything else with the product name.

But how can i make this workflow trigger all the time.

Right now its triggering only for the records new opp product records that is created. but i want this workflow to update for the existing records.

I assume the created date will not be null for all the records and it should update it easily.

Jerun JoseJerun Jose
You have to remember that workflows, validation rules and apex triggers will only kick in when there is database save.

So in order for your workflow to fire (which will then populate the custom field) there will need to be save on those records. Even if you gave a valid workflow criteria, the criteria will only be checked upon the record save.

Bottom line - Update the opportunity line item records to get the solution working for existing data.
sfdclearnsfdclearn

How about if i create custom text field in opportunity product and mass update this field with the product name using data loader instead of writing a workflow?

Jerun JoseJerun Jose
Without the wokflow logic your attempt will work with existing records but there is no scope for handling new additions to the system.

And if you are planning on a data loader update, you might as well fire a dummy update on the opp line items with the workflow. That should take care of things.