Working with Products, Price Books, Price Book Entries in Salesforce can be tricky, especially when you need to do a mass update of price information. How do you update pricing correctly, without messing up the data import?
The following use case is based on a true story, and part of a journey that I believe all Admins (myself included) will experience. Rather than going through each and every Product and assigning a new value in a new Price Book manually, I’ll be talking about how to use Data Loader to set new values across the board – in bulk. Data Loader is a fundamental tool that should be in the arsenal of any Salesforce professional!
After we’ve outlined the use case, the tutorial will be split into two parts. The first part you’ll learn how to prepare your data for the load, and the second you’ll be learning how to use Data Loader to update your database in bulk. Let’s start with the backstory.
Outlining the Use Case
4:45pm, Friday Afternoon
You’ve recently found yourself voluntold that you’re the new Salesforce Administrator at your office, Reliable Paper Company. It’s 4:45pm on a Friday afternoon and the Sales team leader has just sent you the below email:
Please find attached the new prices for the new quarter (. We’ve also changed the codes for some items, please update them in Salesforce when you update the price list. Thanks!
You open the attachment and sigh. As Admins, we’ve all seen them – ugly spreadsheets with data that’s been pulled from multiple sources, no real headings or structure, and it’s your job often to ask the questions and fill in the gaps (another tool all Salesforce professionals will eventually become proficient in!).
After receiving the above spreadsheet with no headings and no real information, you recognise that columns A, C, and E seem to look like the Product Codes and columns B, D, and F look like price values.
Again, it’s 4:45pm on a Friday afternoon, and this is your first time updating the price list yourself after your implementation partner first uploaded it. The last thing you feel like
doing is manually creating hundreds of new records in Salesforce – especially because you are supposed to be catching up with some old friends for dinner soon!
Fear not! Salesforce loves to create tools to make your job easier, and today we’ll be looking at how to use Data Loader.
What is Data Loader?
If you’ve never worked with Data Loader before, you’re about to find a new best friend in the tool.
Simply put, Data Loader is a tool that allows admins to manipulate records in bulk. You can create, edit, or delete multiple record within a single object. There’s also a command line feature (but we won’t be going over that in this post).
So what’s the plan, Stan? How to Approach a Salesforce Data Import
There’s two very important things to understand here:
- What do you want to achieve (where are you going)?
- What resources do you currently have on hand (where you are right now)?
Right now, you know that you have a new price list that needs to be ready for the sales team on Monday morning. You also know that it’s already 15 minutes before you need to leave to meet your friends for a dinner catch up…
…side note: doing any big update in the last few hours of a Friday is ALWAYS a bad idea, you should have policies in place to prevent this sort of issue – but that’s a different issue!
Rather than going through each and every Product and assigning a new value in a new Price Book manually, you can use Data Loader to set new values across the board in bulk.
The first thing we need to do is understand what Data Loader needs to receive and how to clean up the price lists you’ve received. You’ll then need to know how to operate Data Loader.
This article will be split into two parts. The first part you’ll learn how to prepare your data for the load, and the second you’ll be learning how to use Data Loader to update your database in bulk.
Part 1: Preparing the Data
There are three main components involved here:
- The Product (Product2),
- The Price Book (Pricebook2),
- The Price Book Entry (PricebookEntry).
The Price Book can be viewed as a ‘folder’ that you can use to assign certain prices to certain Products. The Product itself is simply the information – its name, product family, and code for example. The Product itself does not have a financial value. The Price Book Entry is a junction object used to join a Product to a Price Book and give it a financial value.
Get to Know the Data Model (+ Sample Tables)
The Products and Price Books must exist before a Price Book Entry record can be created, so the suggested order in which you should create the records is Price Books, then Products, then Price Book Entries.
It’s important to note that there must always be a Price Book Entry record against the Standard Price Book for any Product you wish to assign to a new Price Book. For example, if the business provides a list of Products that contain new Products, you must create them as Products and assign a Price Book Entry back to the Standard Price Book before assigning to the new custom Price Book.
There are required fields on each of your tables. Below are some example headers that your csv files should contain to be able to process them through Data Loader.
|Price Book (Pricebook2)|
|Id (Required for building Price Book Entry)||Name (Text)||IsActive (Boolean)||Description (Text, optional)|
How to Structure the csv. File
We’re inserting related records across multiple different objects, so we need to ensure the records are inserted in the right order and related by gathering the Ids from the previously inserted records. The Price Book Entry records need the Id of the Price Book and the Product, so these two will need to be inserted first.
Note: If this is your first time using Data Loader, you may want to test your process in Sandbox before doing it in Production. In that case, do the following in Sandbox, then replicate in Production when you’re happy with the result.
|Id (Required for building Price Book Entry)||Name (Text)||IsActive (Boolean)||CurrencyIso Code (Picklist)||Family (Picklist)||ProductCode (Text)||Description (Text, optional)|
Price Book Entry (PricebookEntry)
|Price Book Entry (PricebookEntry)|
|UnitPrice (Currency)||IsActive (Boolean)||CurrencyIso Code (Picklist)||Pricebook2Id (Id of the Price Book)||Product2Id (Id of the Product)||UseStandard Price (Boolean)|
Part 2: Bulk Update Prices with Data Loader
The steps we’re going to follow in this tutorial are:
- Clean up the data that’s been provided
- Insert the Price Book
- Insert the Products
- Use Vlookup to Populate the IDs
- Insert the Price Book Entry Records
Step 1: Clean Up Your Data
The first thing you should do is clean up the data. Create a template based on the fields above to ensure your data follows the correct format and has the correctly labelled columns
(this makes it easier to auto-match inside Data Loader – more on that later).
PRICE BOOK ENTRIES
Step 2: Insert Price Book
This part can be done manually if preferred as there’s only a single record, however it may be a good opportunity to familiarise yourself with Data Loader by inserting this record first.
- If you use Data Loader, a success log will be created and will return an Id of the
newly created record.
- If you’ve created the record manually, you’ll need to retrieve the Id from the
URL bar in your browser.
You have an option here – you can create the Price Book Entry as a single line in a CSV file and insert it via Data Loader, but as it’s likely less work to create it via the UI, that’s what I’m going to do in this example.
Navigate to the Price Books tab in Salesforce and click New, fill out the details, and click Save.
Once your Price Book is ready, find the Id in the URL and store it somewhere (the Id of a Price Book (PriceBook2) record will always begin with ’01s’).
Step 3: Insert the Products
It’s best to use Data Loader for this step, and use the success log that is recorded to retrieve the Ids.
The first thing that needs to be done is you need to make sure your data is prepared accordingly, so if that’s not been done please refer to the templates above and make sure your data follows that format.
If you haven’t yet, download Data Loader to your computer. You can do that by following the instructions here.
Once you’ve installed Data Loader, click Insert (or Upsert if you’re updating/inserting in the same transaction).
This is where you’ll be asked to log in to your environment if you haven’t already. If this is your first time, you may want to run a test in Sandbox first. In that case, follow these steps in Sandbox, then quit Data Loader and reauthenticate in Production.
Once you’ve authenticated, click Next. You’ll be asked to select an object – select Product. You’ll also be asked to select your CSV file – use the one you’ve created based on the template.
Click Next. You’ll be prompted to match the fields from your spreadsheet to the fields within the Product2 object in Salesforce. If you’ve kept the same labels as written in the template in this article, you should be able to click Auto-Match and have them align correctly. If not, map them accordingly by clicking and dragging. Once you’re done, click OK, then Next.
The last page asks you to select a folder to store success and failure logs. It may be worth creating a separate logs folder for while you learn. Select that, and click Finish then OK on the popup.
Hopefully after the execution, you’ll see a popup similar to the below telling you that all your records have been inserted with no errors. You’ll also be able to view a preview of the success log that was generated as a result. If you have any errors, check the error log, fix your mistakes, and try again until you get a success.
Step 4: Use Vlookup to Populate the IDs
Use Vlookup to populate the Ids of the Price Books and Products into the Price Book Entry csv. We’ll gather the Product Ids from the success logs produced in steps 2 and 3 using the Vlookup tool in Excel.
You should already have a Price Book Entry template created, only it will be lacking the Ids for the newly inserted Products. To gather these, we’ll use an Excel tool called Vlookup.
Vlookup is a tool within Excel used to find unknown data based on known data. For the purpose of this tutorial, we’re going to assume that the Price Book was created manually and the Id was gathered and stored separately. This will allow us to focus on the Products.
Below is what your Price Book Entry template should look like, with one small addition – I’ve added a new column, [PRODUCT]. This will hold the Name value of the Product records that we’re going to match to the Product’s success record.
PRICE BOOK ENTRIES
Here’s how Vlookup works. There are 4 variables in the Vlookup:
A = The known value that you want to search for (in this case, the [PRODUCTNAME] column).
B = Where you’re searching (the A value and the value you’re searching for should both be in this table). It’s worth noting that the A value should be the first column in the B range for this to work.
C = The number of the column in the range (B) that contains the resultant value you’re searching for (the Id).
D = Should this be an approximate match (We need to set this as FALSE).
Before you use the Vlookup formula on the success file, create a duplicate of it and move the Product Name column to the far left – the A value should be the first column in the B range. Your duplicate success file, after you’ve moved the Name column to the far left, should look something like this:
So the formula that sits in the Product2Id column should follow the ‘=VLOOKUP(A,B,C,D)’ format and look something like this:
There’s an extremely handy video you can watch on Microsoft’s support website on how to use Vlookup if you need more guidance.
Once you’ve been able to populate the Product2Id column, you’ll need to copy it and paste as values. On a Mac, this is CMD + ALT + V, and on Windows this is CTRL + ALT + V. On both, Select Values, and click OK.
You should now have a list of Price Book Entries to insert in Step 5.
Step 5: Insert the Price Book Entry Records
Once the data is prepared, you can be Insert the Price Book Entry records using Data Loader.
Note: A Standard price is always required before a Price Book Entry can be added for a non-standard Price Book. If you’ve just inserted records, you’ll need to clone your Price Book Entry upload csv and replace the PriceBook2Id value with that of your Standard Price Book first, then repeat the process for the new, non-standard Price Book. Follow these steps below for the Standard Price Book, then repeat for the new Price Book if you run into any errors. You’ll also need to set the ‘UseStandardPrice’ column to FALSE for the Standard Price Book Entry records.
To insert your Price Book Entry records, navigate to Data Loader once more and click Insert. This time, you’ll need to check the ‘Show all Salesforce objects’ box at the top
before you’ll be able to see the Price Book Entry object. Once you have, select it, and select your csv.
You’ll be able to Auto-Match your fields here as well. I’ve left two columns in the csv that I don’t need to map, so you can see what that looks like.
Continue as per Step 3. Ideally, you’ll execute your upload and see a success screen. As mentioned above, this may need to be run twice if your Products don’t have values for the Standard Price Book.
Working with Products, Price Books, Price Book Entries in Salesforce can be tricky, especially when you need to do a mass update of price information. Based on a true story that I believe all Admins (myself included) will experience, this tutorial has shown you how Data Loader can come to your rescue, but you must follow the 5-step process to import data correctly and ensure you don’t get into a big mess!
Now, with that out the way, it’s time to go enjoy that dinner and catch up with your old mates – and much sooner than you would if you had uploaded them all manually!