Mapping

With Skyvia you can load data from cloud application objects, relational database tables (and views), and CSV files. In this topic, we will refer to them as “source objects”. The source cloud application object fields, table columns, and CSV file columns will be referred as “source columns”.

Data can be loaded to relational database tables and cloud application objects. In this topic, they will be referred as “target objects”, and their fields/columns will be referred as “target columns”. On the mapping page of the task editor you should map the target columns to source columns. For synchronization, you configure mapping in both directions — source columns to target columns and target columns to source columns.

To map a target column, click it in the table, then click the tab corresponding to the required type of mapping in the source column, click Column and select mapping type used, and then specify the mapping depending on the type used.

The result value of the mapping should have the same data type as the mapped column. If you use column mapping, the column must have the same type in Skyvia. If you use expression mapping, its result should have the same type as the mapped column, etc. If you need to map a column to the column of different data type, you should use the expression mapping and specify a type conversion expression. Check https://msdn.microsoft.com/en-us/library/ms141704.aspx for more information on type conversion expressions.

Mapping Types

You can use the following types of mapping for target columns:

  • Column — you simply map a target column to a source column
  • Expression — you map a target column to an expression, including source columns, different functions, logical and mathematical operators.
  • Source Lookup or Target Lookup or Lookup — you can obtain a value for a target column from any target or source object. You need to specify the object and its column to get the value from, the lookup key column, and the corresponding source column or constant. Skyvia matches this constant or value from the source column with the the values from lookup key column to find the corresponding record in the lookup object.
  • Constant — you can set a target column to a constant.
  • External ID — this mapping type is available for Salesforce only, for the foreign key fields. It maps object references using the referenced object External ID field values.
  • Relation — this mapping type is available only for the foreign key fields if you load several related source objects. You need to specify the relation between the source data, and Skyvia will automatically build the corresponding relation between target data.
  • Zip File — this mapping type can be used when importing a CSV file together with a zip file with imported binary data. It is available for the fields of base64 types (Salesforce) or LOB fields (databases) for importing binary files from the uploaded zip archive to these fields. Not supported for Zoho CRM.

The table with the target object columns lists only the columns of one target object. If you load the data to multiple target objects, you can select the object to display fields for in the Table Mapping drop-down list.

Searching and Filtering Columns

If the object being mapped has many columns, and it’s not convenient to select a target column for mapping, you can use column search and filtering.

Filters list

To quickly find a column, start typing the column name in the Type to search box above the table on the left side of the Task Editor. Only the columns with names containing the typed text will be displayed.

Additionally, you can filter columns by selecting the Filters checkboxes above the table on the left side of the Task Editor. The checkboxes are as follows:

  • mapped — if selected this checkbox displays target columns with already specified mapping.
  • unmapped — if selected this checkbox displays target columns with mapping not specified yet.
  • not required — if selected this checkbox displays target columns that are not required to be mapped before saving the task.
  • valid — if selected this checkbox displays target columns that already have a valid mapping defined.

Required Target Columns

Columns that must be mapped in order to create a valid task are marked with the required label. Other columns may be left unmapped if you don’t want to load any data into them. To filter out not required columns click the not required button.

Mapping for Upsert, Update, and Delete Operations in Import Tasks

For Update and Delete operations, you need additionally to map the ID (primary key) of the target object in order to identify the records for updating or deleting respectively. You can map it using Column, Expression, Constant, or Lookup mapping respectively.

For Upsert operation, you must map the Id (or primary key fields) by default. If the mapping for Id or primary key returns Null for an imported row, the row is inserted. If the mapping returns a non-null value, Skyvia tries updating a row with such Id or primary key. See more details in Performing UPSERT Operation.

When performing UPSERT to Salesforce, you can also use an External ID column to find the corresponding records. In this case Salesforce uses this column to determine whether it should create a new record or update an existing one, and there is no need to map the Id column. You must map this External ID field using any of available mapping kinds: Column, Expression, etc.

Mapping in Synchronization Tasks

The main distinction of mapping in synchronization tasks is that mapping is specified for both directions separately. You can switch the side to map by clicking Source to Target or Target to Source under the task editor header. Column mapping is automatically reflected when switching sides, however other kinds of mapping must be defined separately.

Constant mapping has an additional checkbox — Use this value as filter of target records — in synchronization tasks. If you set constant mapping for one direction (for example, from source to target) and select this checkbox, only the data having the column values equal to the specified constants participate in synchronization when performing synchronization in opposite direction. For example, when you synchronize the Product2 Salesforce object and map its IsActive field using constant mapping to true, only the objects having IsActive field equal to true will participate in synchronization when this checkbox is selected. Product2 objects that have IsActive equal to false will be ignored.