How to Delete Data Using Transform Map

[hamauzutr13]

We are considering importing an Excel file into the group member (sys_user_grmember) table.
This involves not only adding data but also potentially deleting data from the group members table.
We are exploring adding a deletion flag to the Excel file and using a Transform Script to branch the process based on the deletion flag value, handling data deletion and addition accordingly.
Could you advise whether this approach is feasible or if there is a better method?

[Chaitanya ILCR]

HI @hamauzutr13 ,

yes, whatever you have mentioned is very much possible

if you are stuck please share the details like where you are stuck and what was done so far

alternative approaches you can follow

have a catalog item where you can upload this excel and use the GlideExcelParser api to run the excel into the MVRS and have workflow to perform the addition and removal of group members this way you will have a request to track the changes

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[Bhuvan]

@hamauzutr13 

Do not import the records to the table directly, for creation use import set table and transform to target tables and as part of onComplete transform, delete the records.

Best practices is to not delete the records directly as it could have potential impacts but considering this is user-group membership table, you can create onComplete transform like below and delete the records. 

Test it thoroughly in Sub-Production and migrate it accordingly.

    function onComplete() {
        var deleteGr = new GlideRecord('sys_user_grmember'); 
        deleteGr.addEncodedQuery(<add encoded query>);
        deleteGr.query();
        while (deleteGr.next()) {
            // Log for debugging if desired or remove logging
            log.info('Deleting record ' + deleteGr.getValue('sys_id'));
            deleteGr.deleteRecord();
        }
    }

If this helped to answer your query, please mark it helpful & accept the solution. 

Thanks,

Bhuvan

[Bhuvan]

@hamauzutr13 

Did you get a chance to review this ?

As per community guidelines, you can accept more than one answer as accepted solution. If my response helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Ankur Bawiskar · Accepted solution]

@hamauzutr13 

Data load is usually done for insert & update and not for deletion.

Why not ask user to share separate excel or csv for data deletion and then you can read that file using CSVParser or ExcelParser and then delete the record?

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