How to Fix duplicate numbers issue using background/Fix script

[Ben42]

Hello Guys,

By running the below script in background scripts, we have identified we have 100+ contract records with duplicate record numbers.
And, For the records that share the same number, I need the more recent record to be renumbered to the next available number by using fix script/background script. 

Any help with the code is much appreciated.

Script:
var dupRecords = [];
var gaDupCheck1 = new GlideAggregate('ast_contract');
gaDupCheck1.addAggregate('COUNT','number');
gaDupCheck1.groupBy('number');
gaDupCheck1.addHaving('COUNT', '>', 1);
gaDupCheck1.query();
while (gaDupCheck1.next()) {
dupRecords.push(gaDupCheck1.number.toString() + '\n');
}
gs.print(dupRecords);

Thanks,

Ben.

[SatyakiBose]

Hello @Ben42 

Please check the below community articles.

It should help you in renumbering the duplicate records:

1. Need to correct numbering of records, there are duplicate number records in a table.
2. Duplicates Numbers

[prithatcs]

You can also check here for the script to renumber the duplicate records.

https://docs.servicenow.com/en-US/bundle/tokyo-platform-administration/page/administer/field-adminis...

[Basheer]

Hi @Ben42 ,

In the gs.print(dupRecords) you would get the array of the records which got created as duplicates. Add sys_ids and do as below. Do little bit modifications if you wish as per your need.

var dupRecords = [];
var dupSysId = [];
var gaDupCheck1 = new GlideAggregate('ast_contract');
gaDupCheck1.addAggregate('COUNT','number');
gaDupCheck1.groupBy('number');
gaDupCheck1.addHaving('COUNT', '>', 1);
gaDupCheck1.query();
while (gaDupCheck1.next()) {
dupRecords.push(gaDupCheck1.number.toString() + '\n');
dupSysId.push(gaDupCheck1.sys_id.toString() + '\n');
}
gs.print(dupRecords);
gs.print(dupSysId);

var latestNum = ' ';
var grLatestRecNum = new GlideRecord("ast_contract");
grLatestRecNum.orderByDesc();
grLatestRecNum.query();
if(grLatestRecNum.next()){
latestNum = grLatestRecNum.number;
}

for(i=0;i<dupSysId.length;i++){
var gr = new GlideRecord("ast_contract");
if(gr.get(dupSysId[i])){
gr.number = latestNum +1;
}
}

[Ben42]

Hello Basheer,

Thank you for the response. But, when I run the first part of script (in background script), I am not getting sys ids of records but getting only record numbers.
Am I missing something here?
Script:
var dupRecords = [];
var dupSysId = [];
var gaDupCheck1 = new GlideAggregate('ast_contract');
gaDupCheck1.addAggregate('COUNT','number');
gaDupCheck1.groupBy('number');
gaDupCheck1.addHaving('COUNT', '>', 1);
gaDupCheck1.query();
while (gaDupCheck1.next()) {
dupRecords.push(gaDupCheck1.number.toString() + '\n');
dupSysId.push(gaDupCheck1.sys_id.toString() + '\n');
}
gs.print(dupRecords);
gs.print(dupSysId);

Thanks,

Ben.