Import Set insertMultiple coalesce by target sys_id

[Dave Oshinsky]

I have JSON data exported from a table u_task_3. Using Import Set insertMultiple, I would like to import this JSON data back into the target table u_task_3. For those records with a matching sys_id in target u_task_3, the data from the import JSON should overwrite the target table row with matching sys_id. Otherwise, a new row should be added with freshly allocated sys_id.

This sounds like a simple application of the coalesce feature. I initially tried to coalesce on sys_id in the Field Map. This always adds a new record, with a new sys_id, even when the import record's sys_id matches the sys_id of an existing row in the target. I see that the original sys_id from the import JSON is written to Staging Table sys_id field. Staging Table has another field sys_target_sys_id that is always set to a newly allocated sys_id for the target table sys_id field.

I have tried a Source Script for sys_id coalesce like this:

var now_GR = new GlideRecord('u_task_3');

var qc = gr.addQuery("sys_id", source.sys_id);

gr.query();

if(gr.next()) 

{

    answer = source.sys_id;

}

else 

{

    answer = -1;

}

The intention is to search target u_task_3 for a row with sys_id matching source.sys_id from the input JSON, and overwrite with data from JSON if found.  It does not work.  A new record is always added to the target, with new sys_id.

What am I doing wrong? Is this "coalesce by target sys_id" behavior possible with Import Set API? Or do I need to try Update Sets instead?

As a more general question, how does one debug into a Source Script, to see what it's doing? Is it possible for it to log lines somewhere so I can see what happened when the Source Script ran?

[Dave Oshinsky]

Correcting above script as follows does not help:

var qc = gr.addQuery("sys_id", source.sys_id);

gr.query();

if(gr.next()) 

{

    answer = source.sys_id;

}

else 

{

    answer = -1;

}

[Amit Gujarathi]

HI @Dave Oshinsky ,
I trust you are doing great.

1. Set up an Import Set table to define the mapping between your JSON data and the target table fields. Make sure to map the sys_id field from the JSON data to the staging table's sys_id field.

2. Create a Transform Map to specify the field mappings and coalesce rules. In your case, you can map the sys_id field in the transform map, and enable the "Coalesce" option for that field. This will instruct ServiceNow to check for existing records with the same sys_id in the target table and update them instead of creating new records.

3. Perform the import using the "insertMultiple" operation on the Import Set API. Here's an example of how you can accomplish this in ServiceNow:

var importSet = new GlideImportSet();
importSet.setImportSetTable('u_task_3'); // Set the Import Set table name

var importSetRec = importSet.getImportSetRecord();
importSetRec.initialize(); // Initialize the import set record

var jsonData = '...'; // Your JSON data to be imported
importSetRec.setPayloadFromXML(jsonData); // Set the JSON data as the payload

var importResult = importSet.loadImportSet(); // Load the import set data

if (importResult == 'insert') {
  importSet.transformMap(); // Apply the transform map to transform the data
  importSet.runTransformScript(); // Execute any transform scripts if needed
  importSet.importAll(); // Import all the transformed records into the target table
}

[Dave Oshinsky]

Thanks @Amit Gujarathi  I did create a Transform Map and Field Map as described. The Field Map includes sys_id in Staging mapped to sys_id in Target table, with coalesce checkbox checked. The import still always creates a new sys_id in the Target table, while the import source JSON sys_id is only used in the Staging table. I also tried using a Source Script with coalesce box still checked, as shown in my original post. That did not change the behavior where a new sys_id is always created in Target table. Other fields in the Field map are successfully imported into Staging and Target tables, so I know that the Transform and Field Maps are being honored.

It appears that mapping to the sys_id in Target table has special behavior, which is not intuitive. Any ideas?