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

SK41 · ‎12-20-2023

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!

Sainath N · ‎12-20-2023

@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.

View solution in original post

Sainath N · ‎12-22-2023

@SK41 : The only change I see with your current requirement is to delete the records from the target table instead of marking your checkbox field as false. Below is the updated code. As we are dealing with the deletion of records, Please thoroughly test the functionality in DEV environments.

(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.query();
    while (gr.next()) {
        gr.deleteRecord();
    }

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

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

SK41 · ‎12-26-2023

this is not working,the records are not deleted from table.

Sainath N · ‎12-26-2023

@SK41 : I tested this, and it's working. Please make sure that the user you are trying to execute the load has the delete access to delete records from the table. Check the ACL's on the table; it could be that's blocking the access.

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