Transform map no insert record problem

[Dead Blade]

I am attempting to update the Assigned To on a Change Record via a JDBC Data Source.  I am using 3 fields on the Transform Field Map.  The field I would like to Coalesce on is the correlation_id field but it is not indexed nor will SN let me index it, I receive an error every time I attempt:

FAILED TRYING TO EXECUTE ON CONNECTION 24: ALTER TABLE `tmp_t1573209519k`ADD INDEX (`a_dtm_2`) blah blah blah

So I have to use the assigned to field so that I can attempt to ONLY UPDATE records.

Here is my transform script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    var cr = new GlideRecord('change_request');
    cr.addEncodedQuery(active=true^correlation_idISNOTEMPTY);
    cr.query();
    if(!cr.next()) {
        if(action == 'insert'){
            ignore = true;
        }
    }
})(source, map, log, target);

But it is still allowing the insert of records, I only want to update records but I cannot really use the assigned_to in the query, or can I.

Thoughts?

[Abhishek Pidwa]

Seems that your correlation_id is non empty field needs to be changed to accept the source correlation_id (source.u_correlation_id)

var cr = new GlideRecord('change_request');

cr.addQuery(correlation_id, source.u_correlation_id);

cr.query();
    if(cr.next()) {
        if(action == 'insert'){
            ignore = true;
        }
    }

Try this and let me know what happens ?

[Dead Blade]

Hello Adhishek, thank you for responding.  With your script, if a record was not found it created the record.  This will not work, I only want to update existing records with the assigned to.

[Ashok Katam · Accepted solution]

If you have "Coalesce" set on your correlation_id field, you can create "onBefore" transform script and add below code there, that will ignore the record if action is insert

if(action =="insert")
    ignore =true;

[Dead Blade]

If you read my message I cannot Coalesce on the field that I want.  That is the reason for this post.