Relation Mapping

Relation mapping is used when you create a package that loads several source objects (each in its own task) with relations between them. Please note that you need to import both objects with relation between them in the same package (in separate tasks) to use the relation mapping.

Relations in Different Data Sources

Relations associate objects with other objects. A relation is when one object references another object. In databases, the relations are foreign keys between tables — when a field (or collection of fields) in one table uniquely identifies a row of another table. In cloud sources, the relations are mostly similar to the foreign keys.

For example, in Salesforce, there is a relation between Account and Contact objects. Account can have zero, one, or multiple linked contacts. This relationship is actually determined by the AccountId field of the Contact object, which stores the ID value of an account, the contact belongs to. We can call it a foreign key to the Account table by analogy with databases.

When you import related database tables or cloud objects, you can specify the relation mapping simply by selecting the source relation to use — whether it is a database foreign key or a relationship between cloud objects. CSV files don’t store any relationship definitions. However, when you import several CSV files in one package, and they contain the related data (to be exact, when a field of one imported CSV file uniquely identifies a row of another imported CSV file), you can use the relation mapping and manually select the referenced CSV file and the corresponding fields, as described below.

Skyvia fully supports self-referencing relations (when an object or table references itself), for example, the Parent relation in the Salesforce Account object. However, Skyvia does not support polymorphic relations, when the same foreign key can reference rows from different objects. For example, the Contact object in Dynamics 365 has the parentcustomerid field, which can reference another contact object or an account object. Skyvia does not support such relations.

When to Use Relation Mapping

Relation mapping can be used when you import several source objects or files with related data to several related objects. It provides the easiest way to build relations between the imported target objects based on relations between source objects. Especially this can be useful when you import data to a cloud application, and the detail object references the master object by its ID, since you cannot get this ID before actually inserting the master data.

Relation mapping can be specified only for the foreign key fields of the target objects. You can specify the relation mapping for a field of an imported target object that refers another imported target object, when there is a relation between the corresponding source objects or files.

For example, when we import data to Salesforce accounts and contacts from similar related objects of another CRM, you can use Relation mapping for the AccountId field of the Contact object and select the corresponding relation between source objects or files. When performing import, Skyvia will import the parent (Account) objects first and retrieve the ID values of the result accounts. When importing contacts, it will automatically assign the ID values of the imported accounts in target, corresponding to the source objects that are related to the contacts being imported.

To see more examples of relation mapping, take a look at our tutorial Importing Related Customer, Order, and Product Data tutorial.

How to Configure Relation Mapping

First you need to create a task that inserts data into master target object. After this, when you create a task that inserts data into detail target object, you can click the Column list and select Relation.

After this, you need to do the following:

• For database and cloud applications as source simply select the required relation (foreign key) in the list. Check an example from our tutorial How to Import Tables from SQL Azure (mapping the AccountID column of the Opportunity object in Salesforce to a foreign key relation in the source SQL Azure database):

  

  In case of database and cloud applications as source, self-referencing relations can be selected in the same way as other relations.

• For CSV files as source select the master CSV file in the Referenced Object drop-down list, then select the foreign key column in the Column drop-down and the referenced key column of the master CSV file in the Referenced Column drop-down list.

  Here is an example of importing Salesforce Accounts and Contacts from CSV files. The files contains the related data: the Contacts.CSV file contains the CompanyName column, which stores the name of the account the contact belongs to. Accounts.CSV file stores account names in the Account Name column. We map the AccountID foreign key field of the target Contact object using this relation between our CSV files.

  We select our master CSV file — Accounts.CSV in the Referenced Object drop-down list. Then, in the Column list we select our foreign key column from the Contacts.CSV file — CompanyName. In the Referenced Column list, we select the corresponding column from the Accounts.CSV file — Account Name.

  

  You can use a self-referencing relation (that references the same source file) when importing a CSV file by selecting the Reference itself checkbox.

  For example, we import a CSV file to the Salesforce Account object. Salesforce Account references itself via the ParentID field. Suppose the imported Account.CSV file has the ParentName column that contains the name of the parent account. In this case we need to configure mapping for the target ParentID field in the following way:

  1. First, we select the Relation mapping for the field.
  2. Then we select the Reference itself checkbox.
  3. In the Column drop-down list, we select the ParentName column.

  4. Finally, in the Referenced Column list, we select the Account Name column.