Conditional coalesce script creates record with sys_id of "-1"

[Benjamin Nuttin]

I have to do a conditional coalesce on an inbound record, so I'm using a script that tries to find my target record, and if it can't, it returns -1, as explained at https://docs.servicenow.com/bundle/kingston-platform-administration/page/administer/import-sets/conc... 

For example, my field map script, which is set to coalesce, is:

answer = (function transformEntry(source) {
   var gr = new GlideRecord('incident');
   var qc = gr.addQuery('correlation_id', source.u_sys_id);
   qc.addOrCondition('sys_id', source.u_correlation_id);
   gr.query();
   if (gr.next()) {
       return gr.sys_id;
   } 
   else {
       return -1;
   }
})(source);

This works fine in most cases, but I have seen examples where this just ends up creating an incident with a sys_id of "-1". Any idea what could cause that?

[Shishir Srivast]

Transform map tries to create the record if matching record is not found for Coalesce field. So, may be we can try to set the ignore = true when it tries to insert (action == 'insert') the record and it's returning the sys_id is -1.

[Benjamin Nuttin]

But I still want to insert the record if the corresponding target isn't found - the ignore = true would prevent that.

What's odd is that this usually works (and is the recommended approach in SN Wiki), but sometimes it doesn't work. I haven't been able to figure out what is causing the intermittent failure.

[Shishir Srivast]

i believe it will always try to create a new record with sys_id = -1 since we are nor stopping the insertion of the record when it returns sys_id with -1, or may be you can have -1 sys_id in your source.u_sys_id and source.u_correlation_id as well, so it will just check 2nd time if the record with -1 sys_id is there then just update that and let's not create a new record.

[SanjivMeher]

Why do you return -1. You can just return the sys_id. If there is nothing, it should create record with new sys_id

answer = (function transformEntry(source) {
   var gr = new GlideRecord('incident');
   var qc = gr.addQuery('correlation_id', source.u_sys_id);
   qc.addOrCondition('sys_id', source.u_correlation_id);
   gr.query();
   if (gr.next()) {
       return gr.sys_id;
   } 
})(source);