Lookup Mapping (Target Lookup and Source Lookup)

Lookup mapping fetches a value for the mapped Target field from a record in any Source or Target object based on specific conditions. Source Lookup retrieves a value from a record in the Source object. Target Lookup retrieves a value from a record in the Target object. Lookup mapping is available in Import and Synchronization integrations.

Lookup mapping is helpful in the following cases:

• When values for mapping are absent in the Source object, but available in another object in Source or Target connection.
• For getting the unknown IDs required for UPDATE, DELETE, and UPSERT operations.
• For loading related data when the parent object records already exist in Target.

Terms

Lookup Object - Source or Target object in which the search is performed.
Result Column - Lookup object field that contains the desired value.
Lookup Key Column - Lookup object field, based on which the desired value is retrieved.
Lookup Condition - Source value that is compared with the Lookup Key.

Skyvia allows using more than one lookup key or lookup condition. All used keys or conditions are combined by the AND logical operator. Lookup returns rows that match all of the conditions.

How to Configure Lookup Mapping

To enable Lookup mapping, do the following.

1. On the Mapping Definition tab, select Target Lookup or Source Lookup.
2. Select the object in the Lookup Object drop-down list.
3. Choose the required field in the Result Column drop-down list.
4. Select the Lookup Key Column from the drop-down list. You can add more than one Lookup key.
5. Specify the Lookup condition. Select the mapping type and map the Lookup key.
6. Set Lookup options.

Lookup Condition

The Lookup condition defines a value to compare with the Lookup key. Select the mapping type and provide the corresponding value to set the Lookup condition.

Column

Use this condition to compare the Lookup key field with a source field.

Constant

Use this condition to compare all Lookup key values with a single constant value.

Source Lookup and Target Lookup

Use this condition when there is no field in the Source to be compared with the Lookup key. It will retrieve a value for the Lookup key from another object in the Source or Target.

Lookup Options

You can configure Lookup behavior using Lookup options. Configure them separately for each Lookup mapping.

Use Cache

This option determines whether to use a Lookup cache.

Set null when no match is found

This option determines what to do if there is no match for the Lookup condition. If enabled, Skyvia assigns null to mapped fields. If disabled, the record fails.

Enable this option for the UPSERT operation. When Lookup finds no match, Skyvia will insert a new record. If Lookup finds a match, Skyvia will update the existing record.

Use first match when multiple results

This option determines what to do when Lookup finds multiple rows matching the specified Lookup conditions.

If enabled, Skyvia maps the first matching value to the target field. If disabled, the record fails.

Case insensitive lookup

This option enables case-insensitive comparison for string values. By default, Lookup performs case-sensitive comparisons.

How It Works

Skyvia’s behavior differs depending on the Lookup type and use case.

Regular Lookup

Skyvia queries the Lookup object for every source record and checks if the Lookup key matches the specified condition. If Skyvia finds a matching record, it takes the desired field value and maps it to the target field.

Skyvia queries the Lookup object for every single source record. For instance, if there are 100 source records, Skyvia will perform 100 queries.

Cached Lookup

If you activated the Use Cache Lookup option, Skyvia queries Lookup object into a cache. During the integration run, Skyvia queries this cache for every source record and checks if the Lookup key matches the specified condition. If Skyvia finds a matching record, it takes the desired field value and maps it to the target field.

When to Use Cached Lookup

1. When the source API doesn’t support filters for the fields you use in Lookup.

2. When there are significantly more records in Source than in Target (for Target Lookup). When there are considerably more records in Target than in Source (for Source Lookup).

For example, you have 1 million records in Source and 1000 in Target. If you enable a regular Target Lookup, Skyvia will query the Lookup object 1 million times, which will take much time and use many API calls. In this case, Cached Lookup can help you save time and API calls. Skyvia will read the Lookup object and write its records into cache. Then Skyvia will query this cache for each of the million source records.

Nested Objects Lookup

Different connectors have objects with complex structures. They have fields storing complex structured data, such as Invoice.Lines in QuickBooks or Products.Variants in Shopify. Skyvia allows mapping nested object fields in Import integrations. You can use Lookup to import data to a complex structured object from an object with a different structure.

In this case, Source Lookup compares the Lookup key with the field in another source object and returns all the marching records in array format. Then, you can map the fields of this array to the nested fields of the Target object. For example, each invoice line refers to the invoice by invoice ID. Lookup returns all the lines of the specific invoice.

Example 1. Simple Lookup

You import a list of contacts from CSV file to Salesforce. You want to assign these contacts to the existing Salesforce accounts. To do that, you need to specify the related account ID for each contact. You can’t use the Column mapping for the AccountId field, because the source file doesn’t contain the Salesforce account IDs. But it contains company names corresponding to account names in Salesforce.

Use Lookup to fetch the absent AccountId values by comparing account names in Salesforce and company names in the Source file.

Before configuring your Lookup, prepare an Import integration with CSV as Source and Salesforce as Target. Add an Import task and set the Source and Target objects.

To configure the Lookup, do the following:

1. On the Mapping Definition tab select Target Lookup for the AccountId field.

AccountId is a foreign key field which associates the Contact object with the Account object. For such fields Skyvia sets the Lookup Object and Result Column values automatically. In other cases, select them manually.

1. Select the Account.Name field as the Lookup Key Column.
2. Choose Column mapping.
3. Select the source file column with which the Lookup will compare the Lookup key. In our example, it is the Company field.
4. Leave Lookup options in the default state.
5. Complete mapping for other fields and run the integration.

After the run you have new Salesforce contacts and assigned them to existing accounts.

Example 2. Lookup by Constant

Let’s say you want to insert the prices for products from the CSV file into the Salesforce Standard Price Book. When you import data to the Salesforce PricebookEntry object, you must map the price book ID.

If you don’t know the ID of the desired price book, you can use Lookup to retrieve it by its name. The Lookup will compare the price book name in the Salesforce Pricebook2 object with the provided price book name and map its ID to all imported records.

Before you start configuring your Lookup, prepare an Import integration with CSV as the Source and Salesforce as the Target. Add an Import task and set the Source and Target objects.

1. On the Mapping Definition tab, select the target Lookup for the Pricebook2Id field.

Pricebook2Id is a foreign key field associating the PriceBookEntry object with the Pricebook2 object. For such fields, Skyvia sets the Lookup Object and Result Column values automatically. In other cases, select them manually.

1. Select the Pricebook2.Name field as the Lookup Key Column.
2. Choose Constant mapping.
3. Specify the price book name with which Lookup will compare the Lookup key.
4. Complete mapping for other fields and run the integration.

After the run, you have new PricebookEntry records automatically assigned to the Salesforce Standard Price Book using Lookup.

Example 3. Composite Lookup Key

You want to update product prices which belong to Salesforce Standard Price Book. You don’t know the IDs of records to update. You have the price book ID and the list of product names and prices.

You can’t identify the records to update only by product name, because the same product may belong to multiple price books. But you can identify them by price book entry ID and price book ID.

To perform such update, you can use Lookup with composite key. This Lookup will get the PricebookEntry.Id values using two Lookup keys with separate conditions.

Before configuring your Lookup, prepare an Import integration with CSV as Source and Salesforce as Target. Add an Import task, set the Source and Target objects.

1. On the Mapping Definition tab select target Lookup for the Id field.
2. Select PricebookEntry as the Lookup Object and Id as the Result Column.
3. Select the PricebookEntry.Name field as the Lookup Key Column and choose Column mapping.
4. Specify the column with which Lookup will compare the Lookup key. This column is Name in this example.
5. Below click + Add Lookup Key and select the Pricebook2Id field.
6. Choose Constant mapping and specify the ID of the needed price book.

After the run, you have the updated prices in the price book based on the product name and price book ID.

Example 4. Two-Level Lookup

You import data from Zoho CRM to Salesforce. You already imported accounts. Now you want to import contacts preserving their relations with accounts in Salesforce as it was in Zoho CRM. The AccountId field in the Contact object stores the reference to the parent account in Salesforce.

You can’t use Column mapping for AccountId, because Salesforce accounts have other IDs than Zoho CRM accounts. You can use Lookup to fetch the account IDs comparing account names in Salesforce and Zoho CRM. But you can’t map the Lookup key by Column or Constant, because Zoho CRM Contacts doesn’t store the account name. It stores the related account ID. Thus, you map the Lookup key by Lookup to retrieve the account name from the source accounts using their IDs.

Such Lookup compares the account IDs in Contacts and Accounts objects in the Source and gets the accounts names. Then it compares the fetched names with account names in Target and assign their IDs to the AccountId field of the target contacts.

Before configuring your Lookup, prepare an Import integration with Zoho CRM as Source and Salesforce as Target. Add an Import task, set the Source and Target objects.

1. On the Source Definition tab, select the relation, which will be used by Lookup. We use Contacts_Account for this example.

   

2. On the Mapping Definition tab, select Target Lookup for the AccountId field.
3. Select Account as the Lookup Object and select the Id as Result Column.
4. Choose Name as the Lookup Key Column and select the Source Lookup mapping to get the corresponding account names from Zoho CRM Accounts.
5. Select the Accounts object as the Lookup Object and select the Account Name as the Result Column.
6. Select Id as the Lookup Key Column and choose Account in the bottommost drop-down list.

After the run you have new contacts assigned to the existing accounts.

Example 5. Nested Objects Lookup

You import a list of products to Shopify. You store products list in the Products table and their variants in the ProductVariants table. Each ProductVariants record refers to the product it belongs to by the ProductId column. In Shopify, the products and variants are stored in a single Products object. The Variants field has a complex structure and stores a nested array of records. To assign mapping to the fields of the nested object, you can use Lookup.

Such Lookup fetches the array of records for mapping to the nested objects of the target field. It compares the ProductVariants.ProductId field with Product.Id, retrieves all the matching records at once, and transforms the result into the required data structure.

Before configuring your Lookup, prepare an import integration with a database such as Source and Shopify as Target.

1. Enable the Nested Objects checkbox in the integration.
2. Add an Import task, set dbo.Products as Source and Products as Target.
3. On the Mapping Definition tab, select Source Lookup for the Variants field.
4. Select ProductVariants as the Lookup Object.
5. Choose ProductId as the Lookup Key Column and select the Column mapping.
6. Choose the Id field to compare with the Lookup key.
7. Complete the Nested fields mapping.

After the run, you have a new record in Shopify Products with variants fetched from another table with a different structure.