+ Start a Discussion
jsymondsjsymonds 

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
AMartinAMartin

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
jsymondsjsymonds

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

Sunshine2Sunshine2
I need some assistance with something in this area as well.  I've created a custom field in the Product object called "Cost of Goods Sold".  I need the cost to roll up to the opportunity product, multiply it by the Quantity sold to give me a Total Cost of Goods Sold.  Then I need to be able to take the total cost and subtract it from the sales price to get the gross margin.  Any ideas??
XMLCimoXMLCimo

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

Sunshine2Sunshine2
No unfortunately I was not able to resolve.  If you figure it out please let me know.  Thanks.
 
Melissa
XMLCimoXMLCimo

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

Jimmy JuaresJimmy Juares
Your solution is elegant but the Proffesional Edition does not support Workflow Rules. Do you have a similar solution for the formula field or an alternative workaround? I have a similar request as described in this recent post below:

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
AMartinAMartin

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

AMartinAMartin

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

AMartinAMartin

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

Jimmy JuaresJimmy Juares
This looks like a great solution. But could you help me extend this function, I think it's a matter of an IF operation. What if I have multiple Products in a Product Family. Is there a way that I can get the formula to identify the Product Family from the Product?
JamesCoopeViconJamesCoopeVicon

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.