How to Import Related Data
This topic covers different tools and features that simplify working with related data and allow you to automate creating relations in target based on the relation in source. This topic describes several such features used in import.
Explaining Data Relationships
In relational databases, data is stored in tables as rows. Skyvia presents data in cloud apps in the same way — as a set of objects storing records.
For example, there are objects with contact and company data. There is a relationship between company and contact records that allows the user to know, which contact is from which company. It allows you to get combined data from two objects with consistent information of links between contacts and companies. That’s why it is important to maintain this relationship when loading data between different data sources.
One-to-Many, One-to-One, and Many-to-Many Relationships
There are several kinds of relationships: one-to-many, one-to-one, or many-to-many. For example, in a usual case, we can have multiple contacts, representing the same company, but the same contact cannot represent multiple companies. This is a one-to-many relationship, which is the most common kind of relationships.
A contact, however, can be linked more than to one company. So if both a company can have multiple contacts, and a contact can link multiple companies, this is a many-to-many relationships.
If there is always one contact per company, this is one-to-one relationship. Such relationships are more rare than others.
How Relationships Work
Let’s see how different kinds of relationships are implemented.
Almost always, a record in a database table or object has a field or a set of fields, uniquely identifying this record in a table. In database terminology, such a field or a set of fields is called primary key.
In most cloud apps this is a single field, named Id or something like _
One-to-Many Relationship
One-to-many relationship is usually implemented in the following way: a table or object on the “many” side (in our example, Contact) has a field or fields, corresponding to the primary key of the “one” side table. This field or set of fields is called Foreign key. In cloud apps, it can be called reference.
We call the object on the “one” side of the relationship — a parent object, and the object on the “many” side — a child object.
In cloud apps you don’t see such foreign key fields in the interface. Instead, you see a link to parent record or a list of child records. However, in most cases, the relations are implemented in the same way internally. There are the fields storing parent record IDs in child records, and you can access these fields in Skyvia.
For example, in Salesforce, there is a one-to-many relationship between Account and Contact objects. The Contact object has the AccountID field, which references the IDs of the accounts:
One-to-One Relationship
One-to-one relationship is usually implemented in the same way as one-to-many. In databases, if the one-to-one relationship needs to be enforced, the primary key of the child table is also the foreign key, referencing the parent table.
Many-to-Many Relationship
In many-to-many relationship, record on each side of the relationship can reference multiple records. In databases, such relations are implemented via an intermediate table. The related tables do not reference each other, but the intermediate table has foreign keys to both of them. Its records represent the relations between records of the two related tables.
In cloud apps many-to-many relationships can be implemented similarly to databases, via an intermediate object. However, the relation can be implemented differently: a record may have a field with the list of the related record IDs. Such a field can be only on one or on both sides of the relationship.
Here is an example of a many-to-many relationships in HubSpot. The Engagement object has a number of many-to-many relationships with several objects. For example, it has the Associations_ContactIds field that stores a list of related contact IDs. For users’ convenience, Skyvia also provides intermediate objects for some of HubSpot many-to-many relationships. In this case, this is the EngagementContacts object:
Its records represent the relations between contacts and engagements. For example, there is a record with Engagement ID = 4760017877 and Contact ID = 201. This means that the contact with ID = 201 and engagement with ID = 4760017877 are related.
Polymorphic Relationships
In cloud apps there is another kind of relationships — a polymorphic relationship. Such a relationship means that a child record can reference a record in different objects with the same foreign key field. For example, a contact’s parent customer record can be either an account or another contact. You can find examples of such relations in Dynamics 365 and some other cloud apps. See example of such relationship below.
How to Work with Related Data in Skyvia Import?
Skyvia Import offers several features for working with related data. Let’s list them:
- Joining related data — Skyvia allows you to join data of the parent objects when extracting data from an object.
- Lookup mapping — you can get values from matching records in source and target objects using lookups.
- Relation mapping — create relations in target based on the relations in source automatically when you load data from both related objects.
- External ID mapping — for some connectors, like Salesforce or Zoho CRM, you can build relations using special custom external ID fields.
Why You Should Not Use Column Mapping for Relation Fields
Mapping source foreign key fields to target does not work, because IDs or primary keys in different systems have different values and in most cases even different format.
For example, we load Accounts and Contacts from Zoho Desk to Salesforce. In both Salesforce and Zoho Desk, the Contact object stores references to Accounts in the AccountId field. However, it does not mean that these fields can be mapped to each other. If you use a mapping like this:
The records won’t be imported — you will get the errors like this: “Account ID: id value of incorrect type: 114316000006594001”
In Zoho Desk IDs are large integer numbers, while in Salesforce IDs are 15 or 18-character sequences of letters and digits.
In most cases you cannot use column mapping even when loading data between different instances of the same system, for example, between different Salesforce organizations, because the same accounts in different Salesforce organizations have different ID values.
To preserve source relation in target you need to use a different mapping. Let’s see how to use Skyvia’s features in different scenarios of importing related data.
Example 1. Loading Data From Two Related Objects
This example shows how to load data from both parent and child objects.
Let’s show how to load Accounts and Contacts from Zoho Desk to Salesforce once again — this time how to do it properly.
Relation Mapping
The easiest way to preserve a relation in Skyvia when loading data from both related tables is to use relation mapping. Just select the relation mapping from the list, and select the respective relation, like this:
And that’s all. Skyvia will automatically build relations between accounts and contacts in Salesforce, based on the relations in Zoho Desk.
Relation mapping can also be used in more complex cases, when the schemas of loaded data in source and target are not that similar as in this example. Visit our tutorial on importing data from SQL Azure to Salesforce for a more complex example.
Relation mapping always requires both related records to be loaded by the same integration, in the same integration run. If a parent or a child record is already present in target, and you load only the opposite one, the relation won’t be built, and the record will fail.
Relation mapping thus suites when you import all the data from the related objects, but may fail if you load only new and changed data (if only a parent or only a child record is loaded).
For example, you load only new and changed accounts and contacts from Zoho Desk to Salesforce every day. An account is added to Zoho Desk, and Skyvia loads it to Salesforce. Next day, a related contact is added to this account in Zoho Desk. Skyvia loads it to Salesforce separately from its parent account. In this case, relation mapping won’t work.
For such cases, use one of the methods described below.
Lookup Mapping
You can use lookup mapping to find the necessary account in Salesforce to establish the relationship.
To maintain the relation between accounts and contacts, you need to assign the ID of the account in Salesforce to the AccountID field of the imported contact. When the account is already inserted, we can use lookup by some other identifying field. For example, if accounts have unique names, we can use lookup by account name.
You can use any identifying column or multiple columns with lookup mapping.
You can configure your import in two ways:
The first way is to use a combination of source and target lookup, first to find the corresponding account in source, by Zoho Desk AccountID value, and get its name, and then to use this name to get the Salesforce ID of the account. See Two-Level Lookup for more information. The mapping would look like the following:
The second, easier way is to use import’s ability to query parent objects together with child objects and query the account data from Zoho together with contacts. You need to select the corresponding relation in Source Definition of the import task:
This allows you to use parent object fields in mapping, and thus, greatly simplify your lookup:
Lookup mapping for loading related data has the following requirement. Since we look for IDs of parent records in target when importing child records, the parent records must be imported to the target prior to child ones. You can do the following to manage the order of loading data:
- Order your import tasks so that tasks loading parent object data are higher than tasks loading child object data.
- Select the Preserve task order checkbox in your import.
Relation mapping does not have this requirement, because for relation mapping, Skyvia loads parent data first by default.
External ID Mapping
Some connectors support External ID mapping. External IDs are custom fields, that uniquely identify records. They are used to store IDs from an external system, that’s why they are called External ID. Currently, External ID feature is available in Salesforce and Zoho CRM connectors.
To use the External ID mapping, we need to select the External ID field to use (an object can have multiple External ID fields) and map it to the AccountID field.
As for our scenario of Zoho Desk data import to Salesforce, Skyvia has the following requirements for using External ID mapping:
- The Account object must have an External ID field. Note that External ID fields are custom, so there is no such field in Salesforce by default. The field should have a suitable type to store Zoho Desk IDs (Number).
- When you import accounts, you must load Zoho Desk IDs to this field.
Example 2. Loading Child Object Data
This example demonstrates how to load child records to the target that already contains parent records. It also demonstrates related data import when some of the child records don’t have a parent record.
We load Zendesk tickets to Salesforce opportunities. Zendesk Tickets object has a reference to the Organization object, and Salesforce Opportunity has a reference to Account. Suppose we already have the corresponding accounts in Salesforce, and need to import only Tickets.
Besides, in the case of Zendesk, tickets often don’t reference an organization - the OrganizationID field is NULL in this case. The AccountID field in the Opportunity object also can be NULL. Let’s see how such cases are processed in Skyvia.
In this case, we cannot use the relation mapping, because we load only child records. We can use lookups or External ID mapping.
Relation mapping supports cases when some of the child records don’t have parent records. However, it still requires parent records to be loaded in the same integration with child records.
Lookup Mapping
As in the previous example, Lookup mapping can be used in two ways: a two-level lookup in source and in target and one-level lookup together with joining parent organizations when extracting data from the source.
When the foreign keys can be NULL, we need an additional step when configuring the lookup: we need to select the Set null when no match found check box in the Lookup Options.
If you don’t select this lookup option, you will receive the following errors for records with the NULL foreign key field: “No record found for the specified lookup condition.”
Here is the mapping for the first way:
Note that in this case we must select this lookup options for both levels of Lookup.
As well, as in the first example, the second way requires joining Organization data when extracting data.
And then configure lookup like this:
External ID
For loading data in Salesforce, we can use External ID mapping. There are no additional requirements for cases when a foreign key field can be empty, so the mapping is similar to the previous example:
Example 3. Loading Data from Related Objects into Single Target Object
This example shows the case when target object contains information from both parent and child objects in source.
Let’s consider a case when we load Salesforce contacts into a database. The target SQL Server Customers table has the CompanyName field, and we need to load the name of the contact’s account to it.
In this case, the easiest way is to join the account data from the source in your import:
And then we can map the corresponding target field to the name of the account:
Alternatively, you can use the source lookup mapping and look for the account Name by its Id, but we recommend joining data by relation from source, because it is simpler.
When you need to import data in the opposite direction, from one source object into multiple related target objects, you can use Data Splitting.
The above examples demonstrated how you can load data with one-to-many relationships in Skyvia. The same applies to one-to-one relationships as well.
Example 4. Polymorphic Relationship
This example shows how to work with polymorphic and self-referencing relations in Skyvia.
Let’s consider an example of migrating contacts and accounts between Dynamics 365 and Salesforce. Dynamics 365 contacts have the parentcustomerid field, which can reference both account and contact records. We can consider cases for both directions: loading data from Salesforce to Dynamics 365 and vice versa.
In Salesforce, there are two corresponding relationships:
- Contact records can referentce Account records via the AccountId field.
- Contact records can reference other contacts via the ReportsToId field.
We need to use two tasks of loading data: one task loads contacts that reference accounts, and another one loads contacts that reference other contacts. Let’s see how we can filter them.
Relation Type Field
Skyvia provides an additional virtual field for such a relation that determines the type of the parent record. Its name starts with the name of the corresponding foreign key field, and has the ’$type’ suffix. In this example, this is the parentcustomerid$type field that can have a value ‘account’ or ‘contact’.
When importing data from Dynamics 365, use this field to determine whether the source contact record references an account or a contact as a parent. When importing data to Dynamics 365, assign ‘account’ or ‘contact’ values to it to determine what object the target record will reference.
Splitting Contact Data with Filters
When loading data from Salesforce to Dynamics 365, we can filter Salesforce contact by checking if the corresponding foreign key field is NULL. One task will load contacts that have non-NULL AccountID, and another one — with NULL AccountID.
Filter in the first task:
Filter in the second task:
When you load data into the opposite direction, from Dynamics 365 to Salesforce, filter contacts by the virtual parentcustomerid$type field. One task loads contacts with parentcustomerid$type equal to ‘account’, and another one - with parentcustomerid$type equal to ‘contact’.
Relation Mapping
Whenever you load both parent and child records together, we recommend using Relation mapping for preserving relations. Relation mapping is fully supported for such a polymorphic relationship, as well as for self-referencing relationship (when an object references itself).
You can use it both for loading data from Salesforce to Dynamics 365:
and for loading data from Dynamics 365 to Salesforce:
What If Only Part of Contacts Is Loaded
Let’s also consider a case when you load, for example, only new contacts and accounts. In this case some parent records can be already loaded.
Relation mapping won’t work in this case. Lookup (and External ID mapping in case of loading to Salesforce) will work for contacts, referencing accounts. However, it may not always work for self-referencing relationship — contacts referencing contacts, because child contacts can be loaded prior to the parent contacts.
In this case, we recommend the following solution. First, add an import task that loads contacts without mapping the foreign key fields at all. Then add extra tasks that update loaded contacts and set the foreign fields using lookups. Don’t forget to select the Preserve task order checkbox. This ensures that parent contacts are already loaded when we create the relationship.
Polymorphic relationship are supported not for all connectors.
Example 5. Many-to-many Relationship
This example demonstrates import of Zendesk tickets to Insightly CRM as opportunities. Zendesk Tickets object references the Users object:
In Insightly CRM, there is a many-to-many relationship between the Opportunities and Contacts object. Both Opportunities and Contacts object have the Links field with the list of links to other objects. There are also the corresponding intermediate objects ContactLinks and OpportunityLinks. Note that it is enough to add a record into one of these objects to add a relationship.
We suppose that Zendesk users are already synced with Insightly CRM contacts. When importing Zendesk tickets to Insightly CRM opportunities, we need to link the result opportunity to the contact, corresponding to the ticket requester in Zendesk.
Integration Configuration
When we load tickets, we use Data Splitting and load data into Opportunities and OpportunityLinks objects. We need to configure mapping for both target objects.
In mapping for the OpportinityLinks object, foreign key to the contacts object can be mapped using lookup. Foregn key to the parent Opportunities object is filled automatically when using data splitting.
As we can see, when relationship is implemented via an intermediate object, you can preserve these relationships when loading data using Import. If such a relation is implemented differently, you may need to use more advanced tools, for example, Data Flow.