+ Start a Discussion
Tommy GeorgiouTommy Georgiou 

Roll up summary for a custom field like total price

Hi all,


Is there a way to get the total of a custom field like total price?
I have a custom field for products called cost price. I have created a total cost price on oop products with a formula ( Quantity* Cost Price)

What I want is to create another field that sums up all the totals of total cost price and get a result of grand cost total.

Any ideas?

Thank you in advance
Best Answer chosen by Tommy Georgiou
StephenKennyStephenKenny
Hi Tommy,

thanks - what I was saying in my posts above is still correct. Formula fields simply cannot be used for Rollup summary fields. This is why your formula field is not available in the dropdown list from your screenshot above.

So, if you want the rollup summary field then you will have to create a seperate Number or Currency field, and have this field populated via workflow rule as per my post above:
1. Create a new number field on the Opp Product.
2. Use a workflow rule to calculate the value -  the calculation should be the same as the formula field you have created.
3. On the opportunity, create a roll up summary against your new field.

Once you have done this, you will be able to reference your new field in the rollup summary downdown list. Because your new field will use workflow, you will have to update all of your opp products so as to kick off the workflow for each. Formuals dont require you to do this, but you cant use teh formula for a rollup summary!

Hope this makes sense. In summary, while your existing formual field may work and give you the correct figure. This cannot be used in a rollup summary. The next best way around this is to create a new number field which can be used for roll up summary, and then use workflow to populate this.

Cheers,
Stephen

All Answers

StephenKennyStephenKenny
HI Tommy,

As you are unable to add roll up summary fields to the Opportunity Product, you will have to write a custom trigger to achieve this. Thre trigger would have to be on both the Opp Product and the Producr object as if either record is updated, you would want the totals to recalculate. If you are not very familer with writing and maintaining triggers this could be tricky for you.

Another way to achive this would be to use the standard product cost field as your 'total cost', and use the custom field as regular cost. You could use workflow rules to kepp the standard field up to date - and this would then automatically roll up to your Opp Product total as per standard functionality.

Please remeber to mark this thread as solved with the answer that best helps you.

Kind Regards
Stephen
Tommy GeorgiouTommy Georgiou
Hi Stephen,


The total cost price I want it for the Opportunities. Like where I have a total price that is a roll up summary of opps product total price. I have already created the total cost price for opps product where is a formula field(currency) quantity*product2.cost_price__c

But I cannot roll it up for the Opp because there is a croos object field reference (product2.cost_price__c).

 
StephenKennyStephenKenny
Hi Tommy,

Ok I see what you mean now. You cant do a roll up summary on Formula fields so what you would need to do is:
1. Create a new number field on the Opp Product.
2. Use a workflow rule to calculate the value -  the calculation should be the same as the formula field you have created.
3. On the opportunity, create a roll up summary against your new field.
4. Once this is all in place you can delete your formual field.

I think this should get you teh desired result. Let me know how you get on.

Thanks,
Stephen
 
Tommy GeorgiouTommy Georgiou
Hi Stephen,

Thank you for your interest. 

1. I have created a currency field since it's a price.
2. I have created a workflow rule where the eval criteria is set to everytime created and updated, the criteria met is my field greater/equal to 0. Then I created a field update where I followed your suggestion to use the calculation of formula field quantity*product2.cost_price__c.
3. Then I went to Opps and created a roll up sum on the currency field that I have created.
Result is €0.00 instead of the actual value
StephenKennyStephenKenny
Hi Tommy,

Getting closer. Can you double check that the new field on your Opp Product contains the correct value? If it does, please double check your roll up summary is counting the sum and that there is no criteria in place to filter anything out?

Finnaly, Rollup summary fields can take a lttle time to calculate as it is calculating across the entire org. T,here is usally a little icon next to the field on the page layout to indicate the calculation is in progress. Give it a little while to finish.
Tommy GeorgiouTommy Georgiou
Hi Stephen,

It does not contain the correct value. Actually it's blank. 
Is my workflow rule correct?( step 2 on my previous answer)

Checked to see if calc is in progress but still result is 
 €0.00
 
StephenKennyStephenKenny
Hi Tommy,

Lets confirm your workflow rule is actually working. When you update the Opp Product (click edit then save), does your new field contain the correct value? If not, then perhaps the criteria of your workflow rule needs to be changed. To test this, change the criteria of your workflow rule to 'TRUE', this means it will always fire and we can use this to test the logic. When you have made this change, update your Opp Product again to confirm if your new field now contains the correct value. If it does, then this should roll up to the Opportunity via the roll up summary field.

its worth noting that once you have proved this works, you will have to update all other Opp Product lines so as to kick off the workflow. You could use the data loader to do this or write a quick script of you are familier with code.
 
Tommy GeorgiouTommy Georgiou
Hi Stephen,

Let's start from the beginning. I have already created a Total Cost Price field that works just fine for all of the opps products selected in the Opportunity (if 20 products I do get the Total cost price for each one of them). What I really want is to sum up all the total cost prices to get a result like Total Price where I roll up all the sum of opps products total prices.  I am writing this because you mentioned that I will need to update all the other Opps Product lines. 

Sorry for my poor knowledge but Im still a newbie!! 

 
StephenKennyStephenKenny
Hi Tommy,

Not a problem - sometimes its good to take a step back. It sounds like you are very close here. So just to confirm my understanding:
- You have created a Total Cost Price field - is this a roll up summary field on the Opportunity or is it a formula field on the Opp Product?
- You want to create a rollup summary field on the Opportunity, that sums up a specific field on the Opp Product? Why exactly are you unable to do this, what is the specific problem you are facing?

Thanks,
Stephen
Tommy GeorgiouTommy Georgiou
HI Stephen,

Thank you for your patience and understanding.


1. Its a formula field on the Opp product.
2. Yes I want to roll it up as a sum on the Opp. The problem is the formula for the field created in Opp Products contains a custom field from products  : product2.cost_price__c.
Therefore when I try to sum up the Opp product I only see the following. Please see the picture below 

User-added image

As you can see the Total Cost Price it's not there. After checking a bit by the indication of Geoffrey Flynn I found out that formulas that contain cross object fields (in this case the product2.cost_price__c) cannot be rolled up
 
StephenKennyStephenKenny
Hi Tommy,

thanks - what I was saying in my posts above is still correct. Formula fields simply cannot be used for Rollup summary fields. This is why your formula field is not available in the dropdown list from your screenshot above.

So, if you want the rollup summary field then you will have to create a seperate Number or Currency field, and have this field populated via workflow rule as per my post above:
1. Create a new number field on the Opp Product.
2. Use a workflow rule to calculate the value -  the calculation should be the same as the formula field you have created.
3. On the opportunity, create a roll up summary against your new field.

Once you have done this, you will be able to reference your new field in the rollup summary downdown list. Because your new field will use workflow, you will have to update all of your opp products so as to kick off the workflow for each. Formuals dont require you to do this, but you cant use teh formula for a rollup summary!

Hope this makes sense. In summary, while your existing formual field may work and give you the correct figure. This cannot be used in a rollup summary. The next best way around this is to create a new number field which can be used for roll up summary, and then use workflow to populate this.

Cheers,
Stephen
This was selected as the best answer
Tommy GeorgiouTommy Georgiou
Ok.

I see what you mean. On the workflow rule what rule criteria should I use?
StephenKennyStephenKenny
Set the rule to fire on create an update - for the criteria, kust set it to 'TRUE' for now while you test this does what you want. For the field update, use the same formuala as you have in your original formual field.

Once the workflow rule is in place, update your opp product to check that your new number field contains the correct value.
Tommy GeorgiouTommy Georgiou
Dil all of the steps but still getting zeros
StephenKennyStephenKenny
Hmm on your field update, change the criteria to something simple such as '100' - then save and update the Opp Product again. if the field is updated with 100 then we know that the problem is with the calculation. If the field still doesnt update, then the issue is with the workflow rule itself. Silly question, but have you activated the workflow rule?
Tommy GeorgiouTommy Georgiou
Hi Stephen,

It's working now!!! But it seems that I need to update all opps products. Any suggestions via dataloader?
 
Tommy GeorgiouTommy Georgiou
It seems that it wanted some time to run the calculations
StephenKennyStephenKenny
Excellent - yes with the loader, simply do an export of all Opp Products. Just export the Id field, you dont need anything else. When that has finished, use the data loader to do an update and use the export file you just exported and map the Id col to the Ud field.

That should do it, got there in the end :)
Tommy GeorgiouTommy Georgiou
Thank again for your help and patience