Replicating to an Import Set

The first step is to create a DataSync share configuration for the source table on the sharing instance, if one has not been created yet (in this case, a share configuration will be created for the ticket table). Configure this share as you desire with the one exception being to select the “Update or Insert” option instead of selecting the “Create” and “Update” options each separately:

This is an important technical detail, since we want to make sure messages are routed to the Import Set table appropriately on the destination side.

Table Setup

1. Create an import set table on the subscribing instance which will transform records from the incoming source table to the destination table. This table is where incoming data will be staged before being transformed into your final destination table.
   
   For our example, we will create a table called u_incident_import that will transform incoming ticket records to incident records. 
   
   
2. In this table, you should select Import Set Row as the value for the Extends table option.

Table Columns

1. In the Columns tab, define the columns that will be transformed from the incoming source table to the destination table. Click New to add a new column and click on any of the hyperlinked values to edit their type.

For our u_incident_import table example, we want values in the Short Description and Assigned To fields to be transformed from the ticket table to the incident table. You'll want to create additional columns in this table for any columns that you want mapped over. 

Below is an example of the resulting column names for each table:

You should use the appropriate Column Types for each (e.g., Reference to sys_user for u_assigned_to, Date/Time for u_opened_at). The column names for the Import Set Table are described in the Subscribe Field Mapping section below.

Handling sys_id and sys_updated_by

1. Create two custom columns in the import set table for the sys_id and sys_updated_by fields. Name the fields in the Import Set as u_sys_id and u_sys_updated_by as shown in the table above. Ensure that the sys_id field is set to String type of length 32.
   
   This will ensure the values coalesce correctly and the user updates are tracked correctly. Having these mapped over correctly is necessary for related tables (such as sys_journal_field) to show up properly.

Import set table example:

Here's what this import set table would look like for our u_incident_import example:

Subscribe:

Next, you need to create a DataSync subscribe configuration for the newly created import set table. Here's how:

1. From the Table dropdown, select the table you want to use as the destination table. In our example, it's the u_incident_import table.
   
   
2. Click on the Trigger Conditions tab.
   
   
3. Enter your original source table name in the Source table name field. In our example, it's ticket. 
   
   
4. In the Field prefix field, enter u_. However, if you are replicating custom fields, you would leave this field blank to avoid the custom field being replicated as u_u_custom_field.
   
   
5. Check the Run business rules box - this will automatically transform the data once it hits the Import Set table .
   
   
6. Check the Override system fields box.
   
   
7. (Optional) Check the Copy empty fields box.

Subscribe example:

Subscribe field mapping

In our incident import example, the table has the following columns:

This naming convention, paired with the correct Subscribe configuration, will allow the values to be properly mapped over. For example, the table tells the incoming ticket record for short_description in the source table to look for u_short_description in the Import Set table, and the incoming ticket record for u_custom_field to look for the Import Set Table u_custom_field.

If you do not, or cannot, name your columns appropriately, you can take advantage of the Before Subscribe Script portion to manually assign the values.

In your left-side navigator, type transform map and open the Transform Map table. These mappings are what transform data from your import set table into the final destination table (in our example, u_incident_import to incident)

Base Transform Map

1. Create a base transform map and name it appropriately.
   
   
2. In the Source table dropdown, select the correct import set table. In our example, it's u_incident_import.
   
   
3. In the Target table dropdown, select the correct destination table. In our example, it's incident.
   
   
4. Check the following boxes:
   
   
   • Active
   • (Optional) Run business rules - for if you want to run Business Rules (for validations, notifications, SLA)
   • (Optional) Copy empty fields - for if you want to be able to set values to empty 

Coalescing

This option will determine whether the incoming logic will insert or update a record. By default, Perspectium's Common Document format accounts for this. However, if you are creating an import set/transform map from scratch, you should check this box. Learn more about coalescing strategies here.

Impersonation

This option allows you to adjust which user is making updates. Without adding a user, your records will show as being updated by the "system user". This feature is useful for auditing and comment/activity log purposes. Learn more about impersonating strategies here.

Field Mappings

Under the Related Links, click on Auto map matching fields. This will automatically map fields you created in the import set table to related fields in the destination table. In the Field Maps tab below, make sure that the entry for sys_id has Coalesce set to true, since this is what ServiceNow uses to determine whether to insert or update. 

You've done everything you need to replicate to an import set! Below, you can find some extra/advanced configurations. 

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
	target.psp_subscribed_record = true;
})(source, map, log, target);

target.problem_id = source.u_problem_id;

var jgr = new GlideRecord(current.name);
if (jgr.get(current.element_id)) {
  var psp = new Perspectium();
  psp.refreshHistorySet(jgr);
}

var jgr = new GlideRecord(current.tablename);
if (jgr.get(current.documentkey)) {
  var psp = new Perspectium();
  psp.refreshHistorySet(jgr);
}