Apex Code Development You need to sign in to do that
Don't have an account?
Tommy 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
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
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
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
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).
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
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
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.
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
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.
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!!
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
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
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
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
I see what you mean. On the workflow rule what rule criteria should I use?
Once the workflow rule is in place, update your opp product to check that your new number field contains the correct value.
It's working now!!! But it seems that I need to update all opps products. Any suggestions via dataloader?
That should do it, got there in the end :)