How to delete duplicate records in service offering(service_offering)

[Ramu8]

Hi Team,

i have requirement to delete duplicate records 

but need to check name, parent and service classification

for eg: if i have data name as abc parent as yzi and service classificaition as business service

and if i have data name as abc parent as yzi and service classificaition as techinical service

need to return data 

and if i have data name as abc parent as yzi and service classificaition as business service

and if i have data name as abc parent as yzi and service classificaition as business service

need to delete 

please advice

Thanks & Regards

Ramu

[Najmuddin Mohd]

Hi @Ramu8 ,

You can try the below script:

var gr = new GlideRecord('service_offering');  
    
    // Query the records ordered by name, parent, and service classification
   
    gr.orderBy('name');
    gr.orderBy('parent');
    gr.orderBy('service_classification');
    gr.query();
    
    // To keep track of unique combinations of name, parent, and service classification
    var uniqueRecords = {};

    while (gr.next()) {
        // Create a unique key based on name, parent, and service classification
        var recordKey = gr.name + '_' + gr.parent + '_' + gr.service_classification;
        
        // Check if this combination already exists
        if (uniqueRecords[recordKey]) {
            // If it's a duplicate, delete the current record
            gs.info('Duplicate found and deleted: ' + gr.sys_id);
            //gr.deleteRecord();
        } else {
            // If it's the first occurrence, store the combination in uniqueRecords
            uniqueRecords[recordKey] = true;
        }
    }
   
}

Here, a recordKey is created using name, parent, and service classification.
If the code get's for the first time, it stores in the uniqueRecord. When it comes again and checks in the uniqueRecords,
if it is already present in the uniqueRecords it will delete.

You can first try this in you PDI, to get your requirement done.

I have commented out, gr.deleteRecord().

If the above information helps you, kindly mark it as Helpful and Accept the solution.
Regards,
Najmuddin.

[Community Alums]

@Ramu8 

To delete duplicate records in a ServiceNow table based on multiple fields, you can use the following script:

var dupRecords = {};
var gr = new GlideRecord('service_offering');
gr.addQuery('name', 'IN', function() {
    var subGr = new GlideRecord('service_offering');
    subGr.addAggregate('COUNT','sys_id');
    subGr.groupBy('name');
    subGr.groupBy('parent');
    subGr.groupBy('service_classification');
    subGr.having('COUNT(sys_id)', '>', 1);
    subGr.query();
    var names = [];
    while (subGr.next()) {
        names.push(subGr.name);
    }
    return names;
});
gr.addQuery('parent', 'IN', function() {
    var subGr = new GlideRecord('service_offering');
    subGr.addAggregate('COUNT','sys_id');
    subGr.groupBy('parent');
    subGr.groupBy('name');
    subGr.groupBy('service_classification');
    subGr.having('COUNT(sys_id)', '>', 1);
    subGr.query();
    var parents = [];
    while (subGr.next()) {
        parents.push(subGr.parent);
    }
    return parents;
});
gr.addQuery('service_classification', 'IN', function() {
    var subGr = new GlideRecord('service_offering');
    subGr.addAggregate('COUNT','sys_id');
    subGr.groupBy('service_classification');
    subGr.groupBy('name');
    subGr.groupBy('parent');
    subGr.having('COUNT(sys_id)', '>', 1);
    subGr.query();
    var classifications = [];
    while (subGr.next()) {
        classifications.push(subGr.service_classification);
    }
    return classifications;
});
gr.query();

var idsToDelete = [];
while (gr.next()) {
    var key = gr.name + '_' + gr.parent + '_' + gr.service_classification;
    if (dupRecords[key]) {
        idsToDelete.push(gr.sys_id);
    } else {
        dupRecords[key] = true;
    }
}

gs.info('Duplicate records found:'+ idsToDelete.length);

for (var i = 0; i < idsToDelete.length; i++) {
    var delGr = new GlideRecord('service_offering');
    delGr.get(idsToDelete[i]);
    delGr.deleteRecord();
    gs.info('Deleted record:'+ idsToDelete[i]);
}

This script finds all duplicate records based on name, parent, and service_classification, and stores their sys_id in an array idsToDelete. Then, it iterates over this array and deletes each record.

Important Note

If you found this response helpful, please select 'Accept as Solution' and mark it as 'Helpful.' Your support acknowledges my effort and helps enhance our community.

[Aniket Chavan]

Hello @Ramu8 ,

Please try to use the code below and see & let me know how it works for you.

// Define a map to track unique combinations of name, parent, and service classification
var uniqueEntries = {};

// Initialize GlideRecord for the service_offering table
var gr = new GlideRecord('service_offering');
gr.orderBy('name'); // Ensures records are processed in a predictable order
gr.orderBy('parent');
gr.orderBy('service_classification');
gr.query();

while (gr.next()) {
    // Construct a unique key using the combination of name, parent, and service classification
    var key = gr.name + '_' + gr.parent + '_' + gr.service_classification;

    // Check if the combination already exists in the map
    if (uniqueEntries[key]) {
        // Log and delete the duplicate record
        gs.info('Deleting duplicate record: ' + gr.sys_id);
        gr.deleteRecord();
    } else {
        // Add the unique key to the map
        uniqueEntries[key] = true;
    }
}

gs.info('Duplicate record cleanup completed.');

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Regards,
Aniket

[Mark Manders]

https://www.servicenow.com/community/developer-forum/how-to-delete-duplicate-records-in-service-offe...