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?

[Dead Blade]

After working this issue with HI and trying the several other options.  This was the simple fix.  This was first attempted but other configurations made this script not work properly.

So, even though correlation_id is not indexed and you will receive the index error, behind the scenes it will still index in a sense and this code will not allow the insert of a new record.  This is for those that run into a similar problem.  This explanation comes from HI support.

[Rahul Kumar17]

hi

why u r using  the ignore = true that means ignore the record not update the record u can use simply Coalesce.

[Dead Blade]

Please explain Rahul.

[Varsha21]

Hi,

can you try this

 var cr = new GlideRecord(targate);
    cr.addQuery('correlation_id',source.u_correlation_id);
    cr.query();
    gs.log(cr.getRowCount());
    if(cr.next()) {
        cr.assigned_to=source.u_assigned_to;
        cr.update();
    }
    else
   {
      ignore=true;
   }

please correct any spelling mistake or fields names according to your names.

varsha

[Dead Blade]

Hi Varsha, unfortunately your script is doing the same, it still inserts unmatched records.  I only want to update existing records.