Transform Map Coalesce

[beycos]

Hello Everyone, 

I'm importing data from a JSON file using a Data Source and Transform Map. I have two fields set as coalesce: x and y. These two fields always have values in the incoming data, and are mapped correctly. what I need to achieve is If a record already exists with matching x and y , but the existing record has a value in the date deleted field 

So basically:

• If Date deleted field is not empty, I want to bypass coalesce behavior and force insert.

• If  date deleted is empty, normal coalesce behavior (update) is fine.

• Any guidance or best practices are appreciated!

  Thanks in advance!

[Ankur Bawiskar]

@beycos 

you can use onBefore transform script for this logic and don't use any field maps for coalesce

i.e. no field map but only transform script

Something like this should work fine

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // query target with your coalesce fields
    var gr = new GlideRecord("targetTable");
    gr.addQuery("fieldX", source.u_fieldx);
    gr.addQuery("fieldY", source.u_fieldy);
    gr.query();
    if (gr.next()) {
        if (gr.targetField != '') {
            // use insert logic
        } else {
            // update the fields on target record using GlideRecord
        }
    }

})(source, map, log, target);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@beycos 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@beycos 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[RaghavSh]

try onBefore transform script

var targetRec = new GlideRecord('target_table'); // add your target table
targetRec.addEncodedQuery('x=' + source.x + '^y=' +source.y + '^date_field_nameISNOTEMPTY' );
targetRec.query();
if(targetRec.next()){
 targetRec.a = source.a;  // since you have coalese set , I think to force insert you may need to do the mapping manually.
targetRec.b = source.b; 
targetRec.insert();
}