Documentation

General information

  1. Introduction

  2. Security

  3. FAQ

  4. Comparison

Documentation for admins:

  1. Creating a Mapping

    1. Data types

    2. Lookups

      1. Regular lookups
      2. Advanced SOQL lookups

    3. Empty cells

  2. User Management

Documentation for users:

  1. Importing Excel data

  2. Common errors

Examples

  1. Importing Price Lists

Mappings - Lookups

Dynamic lookups are one of the most powerful features of SmartUpload, because they allow you to import data into 'Lookup Relationship' fields, without having to specify ids.

SmartUpload supports two kinds of lookups:

  1. Regular lookups. These are very similar to doing a VLOOKUP in Excel: you specify the Salesforce Object and the query field, and SmartUpload will do the rest.

  2. Advanced SOQL lookups. These give you the ability to craft custom SOQL queries that will be executed for every row in your Excel file. Use with care, because it will impact performance.

Regular lookups

Say that you have a custom object type 'Country__c', where you have a field 'Code__c' that identifies a country by it's 2-character ISO country code (for instance 'US' or 'NL').

On your Account object you have a field 'Country__c' that is a lookup relationship to this Country object.

Traditionally, when importing new Accounts, you would need to run an export of the 'Country__c' object, perform VLOOKUP formulas in Excel to gather the correct ID for every row, and then export the result to CSV to import using Dataloader. With SmartUpload, you can let the system do all that for you.

Now say we have an excel with 2 fields, that we want to import into Salesforce:

  • Account Name

  • Country

When mapping the 'Country' column to the 'Country__c' field, you will see that the data type is automatically set to 'lookup'. There is a button 'lookup configuration' that shows the following dialog:

In the background, using this configuration, SmartUpload will do the following:

  1. Retrieve all the distinct values of the Code field, for instance 'US' and 'NL'

  2. Execute the following SOQL Query to retrieve the ID values: 

    SELECT Id from Country__c WHERE Code__c IN ('NL', 'US') AND Active__c = true

This means you don't have to lookup ID's yourself: you let SmartUpload do the heavy lifting. The number of queries performed depends on the number of distinct values; if that number is low, then this will be executed very quickly.

You can make this as advanced as you like, as long as the generated SOQL query is valid. This means you can also traverse object relations.

Advanced SOQL lookups

The regular lookup works fine when matching a single column value to Salesforce data, but there are cases where you need to use multiple columns to find the correct record during a lookup. In this case, you can set the field datatype to 'lookup (soql)'. When opening the lookup configuration, you now have a text area that allows you specify the SOQL Where clause using variables:

This example contains to variables:

  • ${Code}, which will contain the value from the 'Code' column in the Excel file

  • ${Region}, which will contain the value from the 'Region' column in the Excel file

Opposed to when using regular lookups, SmartUpload cannot combine distinct values to limit the amount of SOQL queries. Therefore, this lookup query will be executed for every row. This means that if your Excel contains 1000 rows, a total of 1000 queries will be executed. Therefore, you should only use this if regular lookups are not an option, because you will run into quotas on the Salesforce API usage or within SmartUpload.