How to Delete Data Using Transform Map

hamauzutr13
Mega Contributor

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?

3 REPLIES 3

Chaitanya ILCR
Kilo Patron

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
Tera Sage

@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

Ankur Bawiskar
Tera Patron
Tera Patron

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

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader