Apex Code Development You need to sign in to do that
Don't have an account?
jsymonds Adding a Product field to Opportunity Product items
I would like to add a custom field from the Products object to the Opportunity Product object. For example, for each product we specify a custom field call Product Family. I would like to include that field in the product records listed in an Opportunity such that when I add/update an Opportunity Product item the correct Product Family value is displayed for the selected Product.
So far, I've added a Product Family field to the Opportunity Product and I've setup a workflow rule to update the field on new/update of records. To fill-in the value, I've tried to use ISPICK() and CASE() but I've reached a dead end. Any help would be *much appreciated*.
Jeff
Hi Jeff,
If you created the custom Product Family field on the Opportunity Product object as a Picklist, you'll need to create a rule and action for each value that you have for the Product Family field. For example, this checks to see if the product family field on the product record is "Services". If yes, it triggers a field update.
workflow rule formula:
IsPickval( Product2.Family , "Services")
field update:
Object = "Opportunity Product"
Field to Update = "Product Family"
New Field Value = "Services"
If you created the custom Product Family field on the Opportunity Product object as a text field, you should only need one rule/action.
workflow rule formula:
product_family__c = "" 'checking to see if product family field on new opp product record is blank.
field update:
Object = "Opportunity Product"
Field to Update = "Product Family"
Update Formula: if product family field is "Services", opp product family field is "Services". If it's "Products", set the field to "Products". Otherwise, default it to "Labour".
if( ispickval(Product2.Family, "Services"),"Services",
if(ispickval(Product2.Family, "Products"), "Products",
"Labour"
)
)
hope this works for you.
I've basically done the same thing and now I can create useful opportunity rollup summary fields.
Aiden
Message Edited by AMartin on 04-25-2008 02:48 PM
That worked great Aiden. Thanks very much for your help. I'm going to be using the field for rollups as well.
Best Regards,
Jeff
Sunshine,
Were you able to resolve this issue? I am trying to do the exact thing. You can call me at 770-855-3244 or email ron@ad-venture.us. Thanks!
Ron
Aiden,
I created a custom field in Products called equipment_cost_per_item. I also created a currenty field in Opportunity called Direct_Equipment_Cost. I want to be able to Roll Up all equipment_cost_per_item into the opportunity.
This thread kind is close to what I want to do but I cant figure out how to do the roll up. Can you please contact me?
Thanks,
Ron
I need to create a custom formula field for an Opportunity Product called "Product Family Class" to describe the Product Family. That is, if I have 3 Families (F) with 2 respective Products (P) each for a total of 6 products (F1P1, F1P2, F2P3, F2P4, F3P5, F3P6) how can I return a value in the custom field to recognize that the selected Product belongs to it's associated Product Family.
Message Edited by Jimmy Juares on 10-24-2008 08:01 PM
Hi Jimmy,
Since the Product family field is a picklist and your custom opportunity product field "Product Family Class" is most likely a formula text field, you just can't reference the cross object formula " PricebookEntry.Product2.Family ". Changing data types requires a little extra work.
You will have to use a function like IF or CASE to set the value of "Product Family Class based upon the value of the Product Family field.
Here's how I would do it:
case( PricebookEntry.Product2.Family,
"Data", "Data",
"Toll", "Toll",
"Voice", "Voice",
"Wireless", "Wireless",
"Ebusiness", "Ebusiness",
"Unknown"
)
Basically, you are just returning a text value for the value of the picklist.
Unfortuneately, it's not currently possibe to use the Product Family Class field with an Opportunity Roll-up Summary. Roll-up summary filters cannot reference cross object formula fields. Which is why I had to use the workflow rules and field updates to do this.
Sorry
Aiden
Hi Ron,
Have you managed to set the value of the an Opportunity Product field with the equipment cost per item? You can't use a cross-object formula to do this as the roll-up summary won't reference a field based on a cross-object formula.
To create a roll- up summary field:
On the Opportunity Custom fields page, click the "New" button...
Step 1 Select "Roll-Up Summary" option...Click Next...
Step 2. Name the field...Click Next...
Step 3.
Select "Opportunity Product" from the Summarized Object dropdown,
Select a Roll-up Type of "Sum"
Select the "Equipment Cost" field from the "Field to Aggregate" dropdown.
If necessary, set a filter.
Because you can't use a cross-object formula field as the field to aggregate or as a filter field, the only way I know how to do this is to use a workflow rule and field update to set the value of a field on the Opportunity Product object when a product is added to the opportunity. This is however, Enterprise functionality so if you have Professional edition, I don't know how to help.
hth.
Aiden
Hi Sunshine,
To create a new custom field on the Opportunity Products object called "Total Cost of Goods Sold"...
Step 1. Select Formula as the field type
Step 2. Name it "Total Cost of Goods Sold", select the return type as Currency.
Step 3a. Insert field....Opportunity Product > Product Book Entry>Product>Cost of Goods Sold. The resulting field shows up as "PricebookEntry.Product2.Cost_of_Goods_Sold__c" in the formula window.
Step 3b. Insert Operator... * (multiply)
Step 3c. Insert field...Opportunity Product > Quantity
To create a new custom field on the Opportunity Products object called "Gross Margin"...
Step 1. Select Formula as the field type
Step 2. Name it "Gross Margin", select the return type as Currency.
Step 3a. Insert field....Opportunity Product > Sales Price
The resulting field shows up as "UnitPrice" in the formula window.
Step 3b. Insert operator... * (multiply)
Step 3c. Insert field...Opportunity Product > Quantity
Step 3d. Place brackets around that part of the formula "( UnitPrice * Quantity) "
Step 3e. Insert operator... - (subtract)
Step 3f. Insert field...Opportunity Product > Total Cost of Goods Sold
The resulting formula should look like:
" ( UnitPrice * Quantity) - Total_Cost_of_Goods_Sold__c ".
Because the Total Cost of Goods Sold formula is a cross object formula (it references another table), you won't be able to use either of these fields in a Roll-up summary. Please note that if you change the value of your cost of goods sold on a product, the Gross Margin on existing opportunity products will reflect the new value and you will lose the historical and true value.
If you have Enterprise edition, you may want to use a workflow rule and field update to set the value of a Cost of Goods Sold field on the Opportunity Product object instead of using a cross object formula. The COGS field will then be static and will not change if you update the value on the Product record.
hth.
Aiden
I have used a work flow rule to set the cost and family on the opp line item and a roll up summary for Cost on the opp.
I have seen some of my line item family values set to 0 (the default for product family) and hence a 0 opp cost.
The family is used to filter the roll up summary.
I have also seen times the cost appears to be doubled!
It does not seem reliable.
If I recalculate all values they are corrected but I cannot find what is causing the values to be reset or doubled.