How to delete duplicates based on 2 fields

[Dead Blade]

Hi,

So I've scrubbed through the forums and found many scripts to delete duplicate records, but they are all based on 1 field.  Some of which are using GlideAggregate.  So I need a script to delete records based on several field criteria.  Example

Field 1     Field 2

John        Smith

John        Doe

John        Smith

John        Johnson

Delete the Duplicate John Smiths. 

I will be using this script as a fix script running on a schedule as maintenance for an m2ms table.  The fields are reference fields if that matters.

[dvp]

I think you are missing to iterate though object

Try this

var optionsObj=JSON.parse(FindDuplicateRecords());
    
    for (var i = 0; i < optionsObj.length; i++) {
        
        var gr=new GlideRecord('u_m2m_authorize_servers');
        gr.addQuery('u_group',optionsObj[i].group);
        gr.addQuery('u_server',optionsObj[i].server);
        gr.query();
        var deleteCount = 0;
        while(gr.next()) {
            gr.deleteRecord();
            deleteCount++;
            gs.info('Record: ' + gr.optionsObj[i].group + gr.optionsObj[i].server + ' Deleted');
            gs.info('Records Deleted = ' + deleteCount);
        }
    }

[Dead Blade]

That is progress, but now it finds the duplicates and deletes the duplicate + the last remaining record of that duplicate.  So there is no record left. 

[dvp]

As you are using while loop it will remove all the matches

 

I think you are missing to iterate though object

Try this

var optionsObj=JSON.parse(FindDuplicateRecords());
    
    for (var i = 0; i < optionsObj.length; i++) {
        
        var gr=new GlideRecord('u_m2m_authorize_servers');
        gr.addQuery('u_group',optionsObj[i].group);
        gr.addQuery('u_server',optionsObj[i].server);
        gr.query();
        var deleteCount = 0;
        if(gr.next()) {
            gr.deleteRecord();
            deleteCount++;
            gs.info('Record: ' + gr.optionsObj[i].group + gr.optionsObj[i].server + ' Deleted');
            gs.info('Records Deleted = ' + deleteCount);
        }
    }

But again the above script will one remove single record.  Let's say if there are 3 reocrds with same group and server it will only remove 1 record

[Dead Blade]

It removed 1 record as you stated.  So now the issue is how do I delete all the duplicates in the table while leaving 1 record for said duplicate in the table.

Thank you so far.  Progress.

[aashishdasari]

What dvp has suggested is true. It is missing the iterate object:

Please change it from:

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group',optionsObj.group);
gr.addQuery('u_server',optionsObj.server);

To:

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group',optionsObj[i].group);
gr.addQuery('u_server',optionsObj[i].server);