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?

[Alikutty A]

Hi,

Can you try this in an on before transform map script?

var change = new GlideRecord('change_request');
change.addQuery('correlation_id',source.u_correlation_id); //replace proper field names
change.query();
if(change.next()) {
 change.assigned_to = source.u_assigned_to;
 change.update();
 ignore=true;
}else{
 ignore=true;
}

[Dead Blade]

Hi A K, thanks for checking in.  Same with your script, it is allowing an insert of new change requests.  I may start a new post about indexing the correlation_id field.  Everything I try still allows for the insertion of a new change request.