Authored by Zishan Razzaq

Monday, August 19, 2013

Field Integrity Exception - When trying to set Use Standard Price to True on Product via Dataloader

Hi all:
As an admin, I recieved a request to have all products within a particular Pricebook to utilize the Standard Price.

When you try to update the USESTANDARDPRICE checkbox field in Salesforce via. Apex Dataloader for that product within that pricebook, you get an error:
"Field Integrity Exception"...

Because when you try to do it thru Apex Dataloader, you have to update the price as well as updating that checkbox.

Picture it out:
I will walk thru the Manual Way and then thru Dataloader:

Manual Way:

1. Click on the Product Tab
2. Choose the Pricebook and click on Go

3. You will see a list of all your products under that pricebook.

4. If you select a product and Click on Edit, you can change the setting of that product to utilize the Standard Price and click save it will save. Which means everytime an user selects this product it will utilize the Standard Price automatically.

As you can see the List Price is now grayed out and the list price has changed or been overrides by the Standard Price once that is checked.

This done is the Manual Way but what happens if you have 1500 or more products then what...

Apex Dataloader way:

1. Export all the data from the PricebookEntry object for just the Standard Price Book.

a) Make sure you check off "Show all Salesforce objects"
b) Select Price Book Entry Object
c) Name this file "PricebookEntryStandardPriceBook.csv"
d) Click Next



2. Now time for our SOQL Query:
You can Select each field or copy and paste this SOQL query into the query box:

Select Id, Name, Pricebook2Id, Pricebook2.Name, Product2Id, Product2.Name, CurrencyIsoCode, UnitPrice, IsActive, UseStandardPrice FROM PricebookEntry where Pricebook2id='00B50000005ZRbX' and IsDeleted=False

NOTE:*****Pricebook2id='00B50000005ZRbX' <== This is my Standard Pricebook Id, you can get this id off from a report or Manage Pricebooks or thru the URL of the Pricebook record you are on.

Click on Finish and done exporting out all Standard Pricebook.

Now we need to Repeat these steps for the Pricebook you would need to update.
Remember to name the CSV file a different name
and Update the Pricebook2Id within the SOQL

Now, you can do this in access and run a query to update the UnitPrice to the pricebook affected from the standard pricebook or use Excel via VLookup..

What needs to be done is:
 1. Open your StandardPricebook csv
 2. Open your "Otherpricebook" csv
 3. Update the UnitPrice on the "Otherpricebook" to match the StandardPricebook Unit Price and Set the UseStandardPrice checkbox to True on the products that need to be updated.
4. Once done and you are comfortable, then it ready to Update via. Apex Dataloader.
5. The mapping of fields would be similar to this screenshot:

a) Make sure you check off "Show all Salesforce objects"
b) Select Price Book Entry Object
c) Make sure you select the correct file to "Update"

This is the way to update UseStandardPrice checkbox via Dataloader.

Hope this helps
Zishan R.