How to fix Duplicate Records in table?

[Sowjanya Jangam]

In TRM Products Table, I have some bad data.

Ex: There are 3 records with same name and same owner but different TRM phases

If a product name - A has owner - X and its TRM phase is Approved and the other two records have same name same owner but the Phase is Evaluation. Now, I want to keep the record with Phase - Approved and make Phase Evaluation to Active - False i.e., other two records.

If a product name - A has owner - X and its TRM phase is Evaluation and the other two records also have same name same owner and the same Phase as Evaluation then No need to change anything.

I just want to change Active true to false only in first scenario.

I want to write a fix script to fix the issue. I have written code wherein it is taking all the records in the table and making the next record as inactive based iteration. Please suggest

[Gowtham10]

Hello @Sowjanya Jangam ,

Please try with below code. Update the code as per your requirement wherever I have mentioned in comments.

var productGA = new GlideAggregate('cmdb_hardware_product_model'); // Change the table as per your requirement. ie. TRM Product table

productGA.groupBy('name');

productGA.orderByAggregate('COUNT', 'sys_mod_count');

productGA.addAggregate('COUNT', 'sys_mod_count');

productGA.query();

while (productGA.next()) {

    var count = productGA.getAggregate('COUNT','sys_mod_count');

    if(count > 1){

        gs.info('Grouped By Name: ' + productGA.getValue('name') + ' - Count:' + productGA.getAggregate('COUNT','sys_mod_count'));

        var gr = new GlideRecord('cmdb_hardware_product_model');  // Change the table as per your requirement. ie. TRM Product table

        gr.addQuery('name',productGA.getValue('name'));

        gr.query();

        while(gr.next()){

            if(gr.barcode == 'DX4350-UR21P')// Change the condition as per your requirement. ie. Phase is evaluation.

            {

                gs.info(gr.name);

                gr.active = false;

                gr.update();

            }

        }

    }

}

Please mark helpful if it is working.