"Error occurred during row insert" while importing JSON data using two transform maps

beycos
Tera Contributor

6 hours ago

Hi everyone,

I'm facing an issue while importing data from a JSON file using a Data Source in ServiceNow. There are two transform maps involved in the process:

  • 1 – uses a field map, and the name field has COALESCE = true

  • 2 – a script-only transform map

Use case:

I'm trying to import two records that have the same name — one active, and one that was previously deleted . The script in 2 is designed to allow this and seems to handle the logic correctly.

Issue:

Despite the script seemingly working, I consistently receive the following error during the import:

"Error occurred during row insert"

This is my script

(function runTransformScript(source, map, log, target) {
var recordName = source.record_name; 
var deletionDate = source.date_deleted; 
var updated = false;

// 1. Find record with same name AND deletion date
var existing = new GlideRecord('my_table_name'); 
existing.addQuery('name', recordName);
existing.addQuery('date_deleted', deletionDate);
existing.query();

if (existing.next()) {
log.info('Found matching record, updating: ' + existing.sys_id);

existing.status = source.status;
existing.company = source.company;
existing.code = source.code;
existing.description = source.description;
// Update other fields as needed

existing.update();
updated = true;
}

// 2. If not found, find active record (no deletion date) with same name
if (!updated) {
var active = new GlideRecord('my_table_name');
active.addQuery('name', recordName);
active.addQuery('date_deleted', ''); // Active records only (no deletion date)
active.query();

if (active.next()) {
log.info('Found active record with same name, updating: ' + active.sys_id);

active.status = source.status;
active.company = source.company;
active.code = source.code;
active.description = source.description;
active.date_deleted = source.date_deleted; // Mark as deleted if applicable

active.update();
updated = true;
}
}

// 3. If still not updated, insert new record
if (!updated) {
var newRec = new GlideRecord('my_table_name');
newRec.initialize();

newRec.name = recordName;
newRec.status = source.status;
newRec.company = source.company;
newRec.code = source.code;
newRec.description = source.description;
newRec.date_deleted = source.date_deleted;

var newSysId = newRec.insert();
if (newSysId) {
log.info('Inserted new record: ' + newSysId);
}
}

// Prevent default insert/update by transform map
target.setAbortAction(true);
})(source, map, log, target);

 

Could you please help me understand why this error occurs, especially since the script is allowing the insertion? 

Thanks in advance for your help!

0 Helpfuls
  • 293 Views
2 REPLIES 2

ayushraj763
Tera Contributor

5 hours ago

  1. Reviewed Script Logic
    • The script checks for an exact match (name + date_deleted)
    • If not found, it checks for active record (name + no date_deleted)
    • If still not found, it inserts a new record
    • Ends with target.setAbortAction(true) to prevent default insert/update
  1. Suspected Coalesce Conflict
    • Since Map 1 has COALESCE = true, it may be trying to update a record before Map 2 runs
    • This could cause a conflict when two records share the same name but differ in date_deleted
  2. Tested with Map 2 Only

Disabled Map 1 temporarily

    1. Ran import with only the script map active
    2. Result: No error — insert/update worked as expected
  1. Validated Field Values
  • Checked that all incoming values (e.g., status, company, code) match expected formats
  • Ensured no invalid references or choice mismatches
  1. Checked ACLs and Permissions
  • Verified that the import user has insert/update rights on the target table
  • No ACLs blocking the operation
  1. Added Logging and Try-Catch
  • Wrapped insert() in a try-catch block to catch any hidden exceptions
  • Added detailed logs to trace each step
0 Helpfuls

ayushraj763
Tera Contributor

5 hours ago

Troubleshooting Steps

  1. Reviewed Script Logic
  • The script checks for an exact match (name + date_deleted)
  • If not found, it checks for active record (name + no date_deleted)
  • If still not found, it inserts a new record
  • Ends with target.setAbortAction(true) to prevent default insert/update
  1. Suspected Coalesce Conflict
  • Map 1 has COALESCE = true, which may try to update a record before Map 2 runs
  • This could cause a conflict when two records share the same name but differ in date_deleted
  1. Tested with Map 2 Only
  • Disabled Map 1 temporarily
  • Ran import with only the script map active
  • Result: No error — insert/update worked as expected
  1. Validated Field Values
  • Checked that all incoming values (e.g., status, company, code) match expected formats
  • Ensured no invalid references or choice mismatches
  1. Checked ACLs and Permissions
  • Verified that the import user has insert/update rights on the target table
  • No ACLs blocking the operation
  1. Added Logging and Try-Catch
  • Wrapped insert() in a try-catch block to catch any hidden exceptions
  • Added detailed logs to trace each step
0 Helpfuls