Background Script to remove duplicate records from Location table

[Mahesh23]

Hi Community,

We have requirement to update duplicate location reference records with original Location and remove the existing duplicate records from Location table since there no unique field available on the location table is it possible to find duplicates records based name and created date ?

For example the first record in the list is original and second is duplicate

@Ankur Bawiskar & @Hitoshi Ozawa any suggestion ?

Please Suggest.

Thanks!

[Harish KM]

Hi refer this blog for the same

https://community.servicenow.com/community?id=community_blog&sys_id=124ffd5adb2517007b337a9e0f9619c2

[Mohith Devatte]

Hello mahesh,

please try this script 

var gr=new GlideRecord('cmn_location');
gr.addQuery('<field name>','<value>'); // give your query to filter out unwanted records 
gr.query();
while(gr.next())
{
var loc=new GlideRecord('cmn_location');
loc.orderBy('sys_created_on');
loc.addQuery('sys_id','!=',gr.sys_id.toString()); // give your query to filter out unwanted records 
loc.addQuery('name',gr.name);
loc.query();
while(loc.next())
{
loc.deleteRecord();
}
}

OR

var dupRecords = [];
var gaDupCheck = new GlideAggregate('cmn_location');
gaDupCheck.orderBy('sys_created_on');
gaDupCheck.addAggregate('COUNT','name');
gaDupCheck.addNotNullQuery('name');
gaDupCheck.groupBy('name');
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck.sys_id.toString());  
}
for(var i=0; i<dupRecords.length;i++)
{
var del = new GlideRecord('cmn_location');
del.addQuery('sys_id',dupRecords[i]);
del.query();
if(del.next())
{
del.deleteRecord();
}
}
}

[Gaurav Rotke1]

Hi  Mahesh,

please try below script,

var dup = new GlideAggregate('cmn_location');

dup.groupBy('name');

dup.query();

while(dup.next()){

var dup1 = new GlideRecord('cmn_location');

dup1.addQuery('name', dup.name);

dup1.query();

dup1.next();

while(dup.next()){

dup1.deleteRecord();

}

}

If this helps, please mark helpful and correct.

Thank you

Gaurav Rotke

[Ankur Bawiskar]

Hi,

check my blog if it helps

Search for Duplicates & Delete Based on 2 Columns

regards
Ankur