Set the checkbox to false of a target field if the record is not in the load data sheet

[SK41]

Hi,

 I have a requirement where I have to set the checkbox field to "false" for a target record when on the load data sheet that record does not come.

For example, for the first load, say we have loaded REC1, REC2 in target table. Now, if again I load the data, say, REC1, REC34. So, in this case the logic should set the checkbox for REC2 in target table as false, because in second load the same data did not come.

How can I achieve this requirement?

Hope, I can explain the requirement properly.

Please assist!

Thanks!

[AshishKM]

Hi @SK41, 

Write onComplete transform script which will execute at the end of an import after all rows are read and transformed, in the script you can check which record updated by this transform map you can exclude those record and update the flag. 

-Thanks,

AshishKMishra

[SK41]

how can I check in the script which all records updated?

[Sainath N · Accepted solution]

@SK41 : You can achieve the following in the following way:.

1. Configure an OnAfter transform script in your transform map with the below code that captures the target record sys_id's that this execution touched. You will have an array of Sys IDs from this step.

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

    if (!import_set.coalescedIds)
        import_set.coalescedIds = [];
    import_set.coalescedIds.push(target.getUniqueValue());

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

2. Configure an OnSubmit transform script in your transform map with the below code to look out for the other records in the table that this transform map execution did not touch.

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

    var gr = new GlideRecord('YOUR_TABLE_NAME');
    gr.addQuery('sys_id', 'NOT IN', import_set.coalescedIds.join(',')); // This filters out the records that this transformation touched
    gr.setValue('YOUR_CHECK_BOX_FIELD', false);
    gr.setWorkflow(false);
    gr.updateMultiple();

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

If you are using workplace safety delivery applications in your instance, you will have this similar logic in the "Space Transform Map" transform map.

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

[SK41]

Hi,

There is another part of this requirement, where I have to delete the records from target table, if those records are not coming through the import.

 For example, for the first load, say we have loaded REC1, REC2 in target table. Now, if again I load the data, say, REC1, REC34. So, in this case the logic should delete REC2 from target table, because in second load the same data did not come.

How can I achieve this?