Remove duplicate record

VM3 · ‎08-06-2022

Hello Team,

I have some duplicates in Company table (core_company) [Snap attached]. I need to remove older record and keep the new one.

I tried following script but looks like I am missing something.

Please help.

var rec = new GlideAggregate('core_company');
rec.groupBy('name');
rec.query();
gs.info(rec.getRowCount());

var rec1;
while(rec.next()){
rec1 = new GlideRecord('core_company');
rec1.sortAscending('sys_created_on');
rec1.addQuery('name',rec.name);
rec1.query();
rec1.next(); // Skip first record (Most current Record skip)
while(rec1.next()){
gs.info(rec1.name+" "+rec1.sys_created_on); //This is the duplicate record needs to be removed

}
}

Mohith Devatte · ‎08-06-2022

then can you try this i tested this and it deleted only one but yeah can you try this ?

because i am deleting in the second loop using cr.deleteRecord() which will delete second loop records and in add query sys_id != sys_id is important and above loop records should not match with below ones

var gr = new GlideRecord('core_company');
gr.query();
while(gr.next())
{
  var cr = new GlideRecord('core_company');
cr.orderByDesc('sys_created_on');
  cr.addQuery('sys_id','!=',gr.sys_id.toString());
  cr.addQuery('name',gr.name);
  cr.query();
  while(cr.next()){
     gs.info(cr.name+"   "+cr.sys_created_on);
   //cr.deleteRecord();
  }
}

View solution in original post

Rajanmehta · ‎08-06-2022

Hello VM you can try below script, that should do the job. [Try in Sandbox first]

var rec = new GlideAggregate('core_company');
rec.groupBy('name');
rec.query();
gs.info(rec.getRowCount());

var rec1;
while(rec.next()){
rec1 = new GlideRecord('core_company');
rec1.orderByDesc('sys_created_on'); //This will sort in Descending Order (Will Sort in Newer to Older Records)
// rec1.orderBy('sys_created_on'); //This will sort in Ascending Order (Will Sort in Older to Newer Records)
rec1.addQuery('name',rec.name);
rec1.query();
rec1.next(); // Skip first record
while(rec1.next()){
gs.info(rec1.name+" "+rec1.sys_created_on); //This is the duplicate record needs to be removed
}
}

VM3 · ‎08-07-2022

Thanks Rajan

Your reply was also ok and works fine.