Remove duplicate records

[si21]

Hi experts,

We identified more than 100 duplicate active department records. We have the list of active records from workday.

Now I need an approach to remove duplicates from servicenow ( these records might be referenced in any user record or other tables) and keep the records as per workday.

Please suggest me an approach for this.

TIA

[Eshwar Reddy]

Hi @si21 

If it's necessary to address this in the higher instance as well, I would kindly suggest creating a FIX script to remove duplicates in both the higher and lower instances.

Script to delete mulitiple records

var gr = new GlideRecord('depertment');

  gr.addEncodedQuery(' '); -> query you used to identify the duplicates

  gr.query();

  gr.deleteMultiple();

Please mark this response as Correct and Helpful if it helps you can mark more that one reply as accepted solution

Thanks
Esh

[Sumanth16]

Hi @si21 ,

Please refer below code to achieve your requirement:

// Replace 'your_table_name' with the actual name of your table
var tableName = 'u_dummy_table';
var fieldName = 'u_name'; // Replace 'u_name' with the field containing the duplicate value

// Create a GlideRecord object for the table
var gr = new GlideRecord(tableName);

// Query for records with duplicate 'u_name' values
gr.addEncodedQuery(fieldName + 'ISNOTEMPTY');
gr.addAggregate('COUNT', fieldName);
gr.groupBy(fieldName);
gr.addHaving('COUNT', '>', 1);
gr.query();

// Loop through the duplicate records and delete the latest ones while keeping the oldest one
while (gr.next()) {
    var duplicateName = gr.getValue(fieldName);

    // Create another GlideRecord to find the oldest record among the duplicates
    var grDuplicate = new GlideRecord(tableName);
    grDuplicate.addQuery(fieldName, duplicateName);
    grDuplicate.orderByDesc('sys_updated_on'); // Assuming you have a 'sys_created_on' field to determine the oldest record
    grDuplicate.query();

    // Skip the first (oldest) record and delete the rest
    if (grDuplicate.next()) {
        grDuplicate.next(); // Move to the second record (the latest one)
        gs.print(gr.u_number + ' : :Deleting record with ' + fieldName + ': ' + grDuplicate.getValue(fieldName));
        grDuplicate.deleteRecord();
    }
}

// Display a message indicating the script has finished
gs.print('Duplicate records cleanup completed!');

You can use the code in a ServiceNow Script Include, Business Rule, Fix script, or a Scripted REST API. Remember to test it in a non-production instance and have a data backup before running it on the production environment. Exercise caution when deleting records permanently.

Note: You can recover mistakenly deleted records from sys_rollback_context table in ServiceNow.

If this helped you in any way, please hit the like button/mark it helpful. So it will help others to get the correct solution.

Thanks & Regards,
Sumanth meda