Opportunity Products available to ALL opportunities, imported via the data loader?

[Autologigal]

hello community!

I need your help figuring something out. 

a) Can I import Opportunity Products via the data loader in a .csv file if I want them to be available on ALL opportunities?

If so,

b) what do I specify as the Opportunity Id in my Opportunity Product insert file? It demands it, and won't let me use a wildcard. 

Any feedback would be great!

Autologigal

[Jakester]

Opportunities have the following one-to-many relationships:

Opportunity-->Opportunity Details (aka Opportunity Products)-->Pricebook Entry-->Product2 (not a typo - there used to be a table named Product and it has been retired, but they still use the Product2 name for their actual Product table now)

So, it can be a challenge to import opportunities with line items. The hard part is the Pricebook Entry table. it's been a while, but as I recall what we did was manually create the first entry for a given product on an opportunity, which will make a pricebook entry that links the opportunity product to the Product2 table, and then I think you can use that existing Pricebook entry line for other entries.

[Stevemo]

When you say "I want them to be available on ALL opportunities" do you mean that you want to give the opportunity owner the option to add any product to any opportunity?

or you would like all of your opportunities automatically have all of the products slected and associated to them? 

If it's the former, then all you have to do is set up a Price Book that includes all products, and make it available to all users, and you're good to go.  

If it's the latter,then you will need tohave an Opportunity ID for every Opportunity Product entry.  Also, there are a few other things you need to consider.  In order to associate each Product to every Opportunity, then you need to create an Opportunity Product (or Line Item) entry, and each one of them will also need to have a Line Item Quantity and Sales Price associated to it (and the quantity must be > 0). 

I'm not sure why you would want to do that, unless with your business model every sale really does include every product that you offer.  

[Autologigal]

thanks Jakester and stevemo, 

Here is the long version so you understand what I am trying to accomplish. 

I need to create roll-up summary fields - or some custom fields of some sort - that will calculate subtotals of my expected revenue grouped according to the Product Family they are in.  (so if I buy apples, oranges, bread and milk, the Fruit custom field wlil display the price of the apples and oranges and any other fruit I would have selected from the price book. 

I have already:

a) created the pricebook (and export/report to obtain Pricebook IDs)

b) imported all the products (and export/report to obtain Product Ids)

c) created the pricebook entries for said products... (and export/report to obtain Pricebook entry IDs...)

Now I have to...

??? d) create opportunity products by importing via the data loader, right? BUT... if I want these Opportunity Products to be available on every single opportunity, not just a single one, what is the Opportunity ID value that I need to specify in my .csv file so that the Opportunity Product insert/creation is successful?

I am getting the error "Opportunity ID is mandattory" if I try to leave it blank or leave it out altogether, and if I enter a wildcard it gets rejected as invalid. I thought an Opportunity ID was opportunity-specific. 

I think I might just totally misunderstand what an Opportunity Product actually IS. 

thanks if you read this far, thoughts?

[Stevemo]

I think I might be missing something,

If all you want to do is have your Roll-Up fields display count/amount of Fruit, Veggies, Meat, Dairy, etc...  then you should be good to go.  

Opportunity Products are Products that have been added to an Opportunity (aka "Opportunity Line Items").  So unless someone is actually buying Apples, Oranges, etc. there is no need to add those Products to an Opportunity.   

If you want to attach every product to every opportunity, then "yes" each Opportunity Product will need to have a unique Opportunity Id

There are instructions for creating Opportunitiy Line Items using the ApexDataLoader here>

https://na2.salesforce.com/help/doc/user_ed.jsp?body=%2F_ui%2Ftraining%2Fhelp%2FCombinedSearchPage&s...

Sorry, crappy link...

Questions this solution will help answer: 
- Step 1: How do I create and activate a Product Price Book?
- Step 2: How do I load Products via the Data Loader?
- Step 3: How do I load Products into Standard Price Book and Custom Price Books via the Data Loader? 
- Step 4: How do I load Opportunity Line Items via the Data Loader? 

See attached PDF file (for formatted - easy to read version of solution) 

The following steps need to be taken in the exact order: 

_______________________________________________________________________________________________
How do I create and activate a Product Price Book?

Step 1 - SET UP Price Books in Salesforce CRM
Usually there are only a small finite set of price books, therefore this action can be done directly in the application. If you choose to use the Data Loader, use the "PriceBook2" table.

1. In salesforce click on the Products Tab. 
2. In the bottom right-hand corner, click on "Manage Price Books"
3. Add as many Price Books as necessary. 
4. The Standard Price Book MUST be active. 

_______________________________________________________________________________________________
How do I load Products via the Data Loader?

Step 2a - PREPARE Products file
(See attached template "Products_INSERT_Template_Step2a.csv")

1. Make sure all Products have a Product Name 
-This is a required field when loading Products into the "Products2" Table
2. If you use Record Types, you will need the "RecordTypeID" for the insert file
-EXPORT "Record Type" table via the Force.com AppExchange Data Loader (you will need to check "Show all Sforce Objects"). This export will provide the 18 digit ID, which is needed for the insert.
3. Make sure your column headers in the file exactly match fields in SFDC

Step 2b - INSERT Products to the "Products2" Table. 

1.Open Data Loader 
2. Insert to "Products2" table
3. Save success and error files in a folder for this project, you will use them in the Step 3b.


_______________________________________________________________________________________________
How do I load Products into Standard Price Book and Custom Price Books via the Force.com AppExchange Data Loader? 

Step 3a - EXPORT "Price Book 2" Table 
You will need the "PriceBook2ID's" and other fields to prepare a file for the next step. 

1. Open the Data Loader 
2. Export the "Price Book 2" table
3. You need "Price Book 2 ID" for insert file
4. Save file in an easy to access location
5. This will be used to create your final insert file for Step 3c and 3e

Step 3b - PREPARE file for Standard Price Book 

NOTE: Even if you do not want to associate these Products with the Standard Price Book this step still must be followed.

Data Columns: 
-Product2ID: Use the "ID" from the "Product2" success file from Step 2
-PriceBook2ID: get the Standard Price Book ID from file created in Step 3a
-CurrencyIsoCode: use Salesforce CRM currency symbol (only for Multi-Currency org)
-Unit Price: this is the List Price/Standard Price field for the product
-UseStandardPrice: this should be TRUE or FALSE and indicates whether to use the price from Standard Price Book or not.
----FALSE (only option for Standard Price Book)
----TRUE (is not an option because "Use Standard Price" field is not available in the Standard Price Book)
-IsActive: TRUE (must be TRUE to insert - this can be changed to FALSE after Step 5 is complete)

Step 3c - INSERT into "Price Book Entry" Table

1. Open the Data Loader 
2. Insert to "Price Book Entry" table
3. Save success and error files in a folder for this project

Step 3d - PREPARE file for Custom Price Books
Use the file from last step and modify it to have the Custom Price Book ID.

Data Columns: 
-Product2ID: Use the "ID" from the "Product2" success file from Step 2
-PriceBook2ID: get the Custom Price Book ID from file created in Step 3a
-CurrencyIsoCode: use Salesforce currency symbol (only for Multi-Currency org)
-Unit Price: this is the List Price/Standard Price field for the product
-UseStandardPrice: this should be TRUE or FALSE and indicates whether to use the price from Standard Price Book or not.
----FALSE (this choice means you will use the Unit Price from the custom Price Book and NOT the Unit Price from the Standard Price Book)
----TRUE (this option only works if this Product has been added to the Standard Price Book. This choice will use the Unit Price from the Standard Price Book)
-IsActive: TRUE

Step 3e - INSERT into "Price Book Entry" Table 
MAKE SURE your Custom Price Books are Active in SFDC.

1. Open Data Loader 
2. Insert to "Price Book Entry" table
3. Save success and error files in a folder for this project

Step 3f (optional)
INACTIVATE Standard Products in Standard Price Book

1. Take the success files the Standard Price Book Entry insert from step 3c 
2. Change the values in "IsActive" from true to FALSE
3. Map only the "Product2ID" and "IsActive" fields
4. Open AppExchange Data Loader 
5. UPDATE "Price Book Entry" Table
6. Save success and error files in a folder for this project

_______________________________________________________________________________________________
How do I load Opportunity Line Items via the Data Loader? 

Step 4 (optional)
INSERT products into "OpportunitiesLineItem" Table

1. Data Columns: 
-Opportunity ID 
-PricebookEntryID 
-Total Price 
-Quantity 
-Unit price
-other fields as needed
NOTE: The Opportunity Product Table provides the "ProductID" as a mappable field, however this field should not be mapped in this process.

1. Export the Opportunity Product (OpportunityLineItem) table for a template
2. Export the appropriate Opportunities from the data loader with filter criteria. 
3. Copy OpportunityID into the insert/update template file.
4. Export the "Price Book Entry" table from the data loader, the "ID" column is the PricebookEntryID. 
5. Copy the appropriate PricebookEntryID's (with corresponding Opportunities) into the insert/update file. 
6. Add the other data into the relevant columns in the insert/update file.
7. Open AppExchange Data Loader.
8. Insert to "Opportunity Product (OpportunityLineItem)" table 

_______________________________________________________________________________________________

NOTES: 
- The PricebookEntryID used in the insert file must correspond to the Opportunity Price Book that has been selected in the application.
- If you change the Product Family as a part of the update process, be sure to change the picklist values for this standard field as well. 
- When loading new products and Price Books, pay particular attention to the sequence of steps detailed above. The user defined standard Price Book must contain all products before the products can be assigned to other Price Books.

ADDITIONAL REFERENCE:

1. Use Link: http://www.sforce.com/us/docs/sforce60/wwhelp/wwhimpl/js/html/wwhelp.htm 
2. From the Table of Contents | Entity Relationships Diagrams | Product and Schedule objects

 

Message Edited by Stevemo on 08-14-2009 02:02 PM

Message Edited by Stevemo on 08-14-2009 02:04 PM

[Autologigal]

All I need to do is have those summary fields display the total per family, so a breakdown of subtotals is visible to the user along with the running grand total as they continue to add products to the order.

I created a Product related list on the Opportunity page, so that users can add products from the active pricebook.

I created the respective Family fields as roll-up summary fields in the Opportunity page. 

Now, I want to do what I need to do so those fields start calculating the Family-specific subtotals. 

This might be really simple once you know what to do!

[Autologigal]

Those are the instructions I followed. I am stuck on step 4.

[Stevemo]

Sorry if I'm still a little fuzzy on things, I just got back from vacay, and I'm still on my first cup of coffee... 

So using your earlier Fruit Stand analogy, is what you want basically for EVERY Opportunity in your SFDC Org to contain OpportunityProducts/LineItems for:

Apples

Oranges

Banannas

Etc.

regardless of whether the customer has purchased any of them?

[Autologigal]

And I am a total noob so there is probably a basic piece of info that I don't yet understand!

We'll figure it out I really appreciate this assistance. 

what I want is subtotals for products selected, grouped according to family. those subtotals  are fields that 

[Stevemo]

Okay, no biggie...  you can defenitly get there, it's just gonna take a few custom fields, and a little tweaking of your Opportunity, Opportunity Product, objects and Line Item page layouts.