Lookup Mapping (Target Lookup and Source Lookup)

Skyvia supports Source Lookup and Target Lookup mapping. Source Lookup allows getting a value for the mapped target field from a row in another object in the Source connection. Target Lookup allows getting a value for the mapped target field from another object in the Target connection.

When you import CSV files, Source Lookup mapping is not available. You can only use the Lookup mapping, which in fact is target lookup.

The same is also applicable for synchronization. It also has only Lookup mapping. When you map a target field to source, it looks for a value in target, and when you map source field to target, it looks for a value in source.

The lookup object row to get the value from is determined by comparing lookup key column(s) with the provided values. You can provide these values in different ways:

1. Usually they are provided as source column values. Check Simple Lookup for more information.
2. You can also specify a constant value for all rows. Check Lookup By Constant for more information.
3. Alternatively, you can use a second-level Target lookup or Source lookup to select these values from source or target objects. Check Two-Level Lookup for more information.

Lookup mapping is most often used to map foreign key fields or ID/primary key fields when performing update or upsert. In such cases ID or foreign key values are often unknown, but the Lookup mapping allows getting them directly from target tables by other fields, identifying rows, such as names, etc.

Simple Lookup

Usually, the necessary row in the lookup object is selected based on a source column value. For example, suppose we import a CSV file with Salesforce contacts that should be assigned to existing accounts, and these accounts are specified by their names. So we configure lookup, getting rows from the lookup objects by a source column value.

When mapping a foreign key fields using Lookup mapping, Skyvia tries to detect the Lookup Object and Result Column values automatically — it takes the object and the corresponding primary key field of this object referenced by the mapped foreign key field. In other cases you need to select them manually.

So, in our example, we use the Target Lookup mapping to map the AccountId field of the Contact object. To configure such a lookup, perform the following steps:

1. In the Lookup Object drop-down list, select a target object to get the value from (in our example, it is Account selected automatically).
2. In the Result Column drop-down list, select a column from the Lookup Object to get the result value that will be assigned to the mapped target column (in our example, Id is selected as a result column. It is selected automatically).
3. In the Lookup Key Column drop-down list, select the target lookup key column. Two new drop-down lists are displayed. The first determines how to select the lookup object rows. It is set to Column by default.

4. Finally, in the bottom most drop-down list, select the source column, the value of which must be equal to the lookup key column value (in our example, it is CompanyName).

   

Lookup By Constant

You can specify a constant value for lookup in order to select the same value from the same row of the lookup object for all the imported rows. For example, when we import data to the Salesforce PricebookEntry object, we must map the PricebookId field. Often the import is performed to entries of only one pricebook, so we can simply use lookup to find the necessary pricebook by its name.

1. In the Lookup Object drop-down list, select a target object to get the value from (in our example, it is Pricebook2 selected automatically).
2. In the Result Column drop-down list, select a column from the Lookup Object to get the result value that will be assigned to the mapped target column (in our example, Id is selected as a result column. It is selected automatically).
3. In the Lookup Key Column drop-down list, select the target lookup key column (in our example, it is Name).

4. In the Column drop-down list, select Constant.

   

5. Specify the constant value to use. String constants are entered without quotes.

   

Composite Lookup Key

If a row in the lookup object cannot be uniquely identified by one lookup key column, you can add more lookup key columns by clicking the + Add Lookup Key links at the bottom of the mapping area. You can specify values for each of the lookup key columns in the same or a different way.

For example, suppose we need to update product prices in Salesforce standard pricebook, and we only have a CSV file with product names and prices, without knowing the IDs of the corresponding PricebookEntries. Knowing the Pricebook2 ID value and product name we can uniquely identify the PricebookEntry to update, so we use the lookup mapping for the ID field, using these two fields as lookup key columns. The product names (which are stored in the PricebookEntry Name field) are specified as source column values, and Pricebook2Id is specified as a constant.

1. In the Lookup Object drop-down list, select a target object to get the value from (in our example, it is the same PricebookEntry table).
2. In the Result Column drop-down list, select a column from the Lookup Object to get the result value for the mapped target column from (in our example, Id).
3. In the Lookup Key Column drop-down list, select the target lookup key column (in our example, Name).
4. In the bottom most drop-down list (under the Column drop-down list) select the column with product names (in our example, it is ProductName).
5. Click the + Add Lookup Key links at the bottom of the mapping area.
6. In the Lookup Key Column drop-down list, select the second lookup key column (in our example, it is Pricebook2Id).
7. In the Column drop-down list, select Constant.

8. Enter the Id value of the required pricebook.

   

Two-Level Lookup

In some cases, the necessary value cannot be directly retrieved by one lookup, but can be retrieved using two-level lookup, when values for lookup key columns of the first-level lookup are provided using a second-level lookup. For example, if we didn’t know the necessary Pricebook2Id in the previous example, and knew name of the pricebook, we could use a second-level lookup on the Pricebook2 object, to find the necessary ID by the pricebook name.

One more example: suppose you perform an import between two CRMs or from two instances of the same CRM. Let it be an import from one Salesforce organization to another. You have already imported accounts and now want to import contacts so that they belong to the same accounts in target that they belong in source.

In the target Salesforce organization, imported accounts already have different IDs, so you cannot just import AccountID values of source contacts. We will use Target Lookup mapping to find the IDs of the imported accounts by account names. However, the source Contact table does not contain the names of the referenced Accounts, it contains only their IDs. So we will use the Source Lookup to supply the account name values to the first-level lookup from the source accounts by their IDs.

1. In the Lookup Object drop-down list, select a target object to get the value from (in our example, the Account table).
2. In the Result Column drop-down list, select a column from the Lookup Object to get the result value for the mapped target column from (in our example, Id).
3. In the Lookup Key Column drop-down list, select the target lookup key column (in our example, Name).
4. In the bottommost drop-down list (under the Column drop-down list), select the column with product names (in our example, ProductName).
5. In the Column drop-down list, select Source Lookup to get the corresponding account names from the source Salesforce organization.
6. In the new Lookup Object drop-down list, select a source object to get the value from (in our example, the Account table).
7. In the Result Column drop-down list, select a column from the Lookup Object to get the result value for the mapped target column from (in our example, Name).
8. In the Lookup Key Column drop-down list, select the target lookup key column (in our example, Id).

9. In the bottommost drop-down list select the source column for the lookup — AccountID.

   

Now our lookup will get the names of the corresponding source accounts by their IDs, specified in the AccountID column of the source Contacts. Then it will use these names to find the corresponding accounts in target, and assign their IDs to the AccountID field of the target contacts.

However, this task can also be solved without two-level lookup in Skyvia. You join fields from the related master objects when importing data. In our case you can join source Account fields to Contact data and use simple one-level lookup.

Lookup Options

You can configure some aspects of the lookup behavior with lookup options. The options are configured separately for each lookup mapping.

Use cache

This checkbox determines whether to use lookup cache.

When this checkbox is not selected, Lookup executes a query against the data source once for every imported row (for some data sources one query for several imported rows is performed). Lookup queries only the matched lookup object rows in this case.

When this checkbox is selected, the necessary fields from all rows of the lookup object are queried and cached on Skyvia, and lookup is performed against this cache. Depending on the number of rows in the lookup object and the number of imported rows, this may provide a performance gain, and for cloud data sources with API calls limit, using this cache may decrease API call usage.

Set null when no match found

This checkbox determines what to do when there is no match in the lookup object for the provided lookup key column values.

If this checkbox is not selected, the imported row fails. If this checkbox is selected, a Null value is assigned to the mapped column.

For the UPSERT operation, if you map an ID or primary key column via the lookup mapping, you should select this checkbox. In this case, when no match is found, a Null value is returned, and the INSERT operation is performed; otherwise, an UPDATE operation is performed. But if this checkbox is not selected, the row simply fails when no match is found.

Use first match when multiple results

This checkbox determines what to do when there are multiple rows in the lookup object that matched the specified lookup key values.

If this checkbox is not selected, the imported row fails. If this checkbox is selected, the value from the first found lookup object row is assigned to the mapped column.

Case insensitive lookup

This checkbox enables case-insensitive comparison for lookup mapping. By default, lookup performs case-sensitive comparisons.