Conditional coalesce script for sys_id creates record

[SANJEEV4]

I have a scenario on multiple coalesce conditions. So I created coalesce on the sys_id field(script)

var gr = new GlideRecord("table");
gr.addQuery("field1", source.fieldA);
gr.query();
if (gr.next()) {
    return gr.sys_id;  // I tried with - answer = gr.sys_id;
} else {
var gr1 = new GlideRecord("table");
gr1.addQuery("field1", source.fieldB);
gr1.query();
if (gr1.next()) {
return gr1.sys_id; // I tried with - answer = gr1.sys_id;
} else{
   return null;     //I tried with :  return -1 (and) return GlideGuid.generate(null); (and) answer = -1
}
}

// Its not working 

 If It does not match, should not create a new record. for that, I created onBefore with 

if (action == 'insert') {
ignore = true;
}

Still creating new records. 

Thanks in advance 

[Tushar]

Hi @SANJEEV4 

Please try this  -

var gr = new GlideRecord("table");
gr.addQuery("field1", source.fieldA);
gr.query();
if (gr.next()) {
    return gr.sys_id;
} else {
var gr1 = new GlideRecord("table");
gr1.addQuery("field1", source.fieldB);
gr1.query();
if (gr1.next()) {
return gr1.sys_id;
} else {
   return null;
}
}

// Ignore the record if it is a new record
onAfter = function(context) {
if (context.action == 'insert') {
context.ignore = true;
}
};

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[SANJEEV4]

@Tushar  

Is this script in onAfter transform script

// Ignore the record if it is a new record
onAfter = function(context) {
if (context.action == 'insert') {
context.ignore = true;
}
};

[Mohith Devatte]

Hello @SANJEEV4 ,

When coalescing on multiple fields, all coalesce fields must be a match for there to be a collision. Matching some coalesce fields but not all does not produce a match. If a match is found using the coalesce fields, the target record is updated with the information imported from the staging table.

So i guess its not finding the match in your case with multiple fields on coalescing and its creating a new record

You can do one thing in the on before transform script lets say i am importing user records with name and email as coalesce.

i can do like this to ignore the new record insertion

var userid = source.field_name.toString();   //replace field name with user id
var email = source.filed_name.toString(); //replace field name with email id

var gr = new GlideRecord('sys_user');
gr.addQuery('user_name',userid);
gr.addQuery('email',email);
gr.query();
if(gr.next()){

gr.name = source.field_name;
gr.update();
}else{
ignore=true;
}

Mark the answer correct if this helps you 

Thanks 

[SANJEEV4]

@Mohith Devatte 

If it does not match that record should be ignored(not create a new record)