Documentation

General information

  1. Introduction

  2. Security

  3. FAQ

  4. Comparison

Documentation for admins:

  1. Creating a Mapping

    1. Data types

    2. Lookups

    3. Empty cells

  2. User Management

Documentation for users:

  1. Importing Excel data

  2. Common errors

Examples

  1. Importing Price Lists

    1. Excel layout
    2. Mapping general settings
    3. Mapping fields: Id
    4. Mapping field: Pricebook2Id
    5. Mapping field: Product
    6. Mapping Field: UnitPrice
    7. Mapping Field: IsActive
    8. Assign the mapping
    9. Testing: create a new job

Example: Importing Price Lists

When trying to import price lists from Excel into Salesforce, there are 3 important objects to consider:

  1. The Product (Product2),

  2. The Price Book (Pricebook2),

  3. The Price Book Entry (PricebookEntry).

The Price Book can be viewed as a list, containing prices for certain Products: you can have multiple Price Books that contain different prices for the same product. For instance if you sell the same product in multiple regions; you might have a pricebook per region.

The Product is just information about the product you are selling, but does not have a price itself.

Now the important object linking these two is the Price Book Entry: it joins a Product to a Price Book, to indicate for which price it is added into the Price Book.

When you want to use SmartUpload to update price lists, we must create a mapping on the 'PricebookEntry' object, and we can choose which kinds of operations we want to do:

  • Update: only update existing entries, but do not add new entries.

  • Insert: only insert new entries, but do not update existing entries

  • Upsert: update existing entries and insert new entries.

In most usecases, the 'upsert' makes most sense, because you might have an Excel file containing product codes with new prices and you might not know if the file contains any new records.

The following sections will take you through the setup of the mapping manually. If you want to use this example, you can download the mapping JSON file here and use it as a basis for your own solution. Simply upload this JSON file when creating a new mapping.

Excel layout

In this example, we will be using an Excel file with the following columns:

  • Product: this will be the 'code' of the product (referencing the field 'ProductCode' on the related Product of the PricebookEntry)

  • Unit Price: this will be the new price for the product.

So our file will look like this, clean and simple:

Mapping general settings

When we create a new mapping, select the target object 'PricebookEntry' and fill out the general settings:

The mapping name and description will be shown to users, the batch size of 200 is the default and will probably work well (unless you have a lot of automation / validation rules defined on PricebookEntries).

Mapping fields: Id

The first field we need to map is the Id of the PriceBookEntry, because if we want to update existing entries, we must know which one to select for the update. We want to find the correct entry by looking for the entry with the given product code.

Open up the Advanced options, and define the following settings:

Let's have a look, the first two fields are defaulted and we don't need to override them:

  • The lookup object is 'PricebookEntry', which will be the base for the query

  • The lookup field is 'Id', because we want to get the Id of the current PricebookEntry to update

The next two options are more interesting:

  • The lookup query field is defined as 'Product2.ProductCode', which means that for every row in Excel, SmartUpload will try to find a PricebookEntry that has a related Product2 which has the field ProductCode that matches our code from Excel.

  • The additional conditions are set to Pricebook2.Name = 'Standard Price Book', because we might have multiple matches otherwise. If a product is present in multiple price books, our query would otherwise get all entries, but we need to filter: only the entry that is present in the Standard Price Book is relevant.

How does this work internally? Say we have a row in our Excel file with product code 'XYZ', SmartUpload will execute the following query:

SELECT Id FROM PriceBookEntry WHERE Product2.ProductCode = 'XYZ' AND Pricebook2.Name = 'Standard Price Book'.

Note that this query can have the following effect:

  • When there are 0 results, SmartUpload will decide that this is a new entry into the pricebook, so an 'insert' will be done for this row in Excel.

  • When there is only 1 result, SmartUpload will decide to update the existing entry with the data from this Excel row.

  • When there is more than 1 result, SmartUpload will give an error: because in this situation there is not a single entry to update. When this happens, most often the additional conditions are incorrect: in this example we might get 2 rows if there are 2 pricebooks with the name 'Standard Price Book'.

Mapping field: Pricebook2Id

The next field we need to map is the 'Pricebook2Id' field. This needs to become the Id of the Standard Price Book, and since we don't have a column for that in our Excel file, we can leave the field 'Excel Column' empty in our mapping:

Note the other settings:

  • The datatype is 'lookup' again, because we need to query Salesforce to get the Id of the Standard Price Book

  • The setting for 'emtpy cell values' is to use the default value specified in the next setting.

  • The setting for 'default value' is the name of our price book: Standard Price Book

  • VERY IMPORTANT: you have to check the box 'Exclude this field from updates during an Upsert' for this field mapping. Salesforce does not allow you to change this field value once it is set: so updates of this field is not allowed. If we had a simple update-only mapping, we wouldn't need to map the field at all. But since we are doing an upsert, there might be new records and for those we need to provide the correct value.

The advanced options are straightforward:

We are doing a lookup to the Pricebook2 object, searching for one with a matching Name, returning the Id, and making sure that the record has the field value IsActive = true.

Mapping field: Product

The mapping for the Product field is similar to the Pricebook field, however, we now don't rely on a default value but we will use the Product column from Excel.

And the advanced options are:

Again, the checkbox 'Exclude this field from updates during an Upsert' is ticked, because you may not update the Product field on existing PriceBookEntry records.

Mapping Field: UnitPrice

Finally we have arrived at the price field, which is very straightforward.

Note that SmartUpload will check the Excel data to make sure that all the fields in the 'Unit Price' column are valid double values. And if a price is missing, an error will be shown as well.

Mapping Field: IsActive

For the final field from our mapping: we want to automatically set the 'IsActive' flag on the PriceBookEntry, so we can create a mapping with an empty Excel Column, and with a default value:

Assign the mapping

After saving the mapping, you can assign it to all the users that need access by clicking the blue 'assignment' icon in the mapping list view:

By assigning it to the correct users, they will see the mapping when they want to create a new upload job.

Testing: create a new job

Go to the 'Jobs' overview and click the 'New Upload' button.

This opens the following screen:

The button 'Download template Excel file' will generate an Excel containing all the fields from the mapping that can be provided by the user. We can fill this excel with our data, and upload it.

Now click the 'Add Job' button, which will trigger the validation phase within SmartUpload. Your excel file will be parsed, checked and if there are any errors they will be shown. When there are no errors, you can execute the upload and after processing you will see updated prices in your Salesforce Pricebook!

For a step-by-step guide on using a mapping as a user, take a look at the user documentation.