need help with below script

[Pranavi13]

Hello, 

requirement is to remove duplicate numbers from our custom tables and update them with unique ones.

The duplication issue occurs because we add some xmls from lower instances to production.

we wanted to get all the duplicate records and then update them with latest record number plus 1
Below is the scheduled job script written this works and removes duplicates but only when executed twice.  when executed for the first time, number is getting updated but to another number which is already present. we would want the new number to be greater than highest existing number. Please suggest the corrections:

var tables = ['u_table_1, 'u_table_2', 'u_table_3', 'u_table_4'];

for (var i = 0; i < tables.length; i++) {
    getDuplicates(tables[i], 'u_number');
}

//function to check for duplicates and updating them with new number
function getDuplicates(tablename, val) {
    var dupRecords = [];
    var gaDupCheck = new GlideAggregate(tablename);
    gaDupCheck.addAggregate('COUNT', val);
    gaDupCheck.addNotNullQuery(val);
    gaDupCheck.groupBy(val);
    gaDupCheck.addHaving('COUNT', '>', 1);
    gaDupCheck.query();
    while (gaDupCheck.next()) {
        dupRecords.push(gaDupCheck[val].toString());
    }
    
    for (var i = 0; i < dupRecords.length; i++) {
        var currentNum = dupRecords[i];
        var tableGr = new GlideRecord(tablename);
        tableGr.get(val, currentNum);
        while (tableGr.next()) {
            var newNumber = new NumberManager(tableGr.getTableName()).getNextObjNumberPadded();
            tableGr.u_number = newNumber;
            tableGr.update();
        }
    }
}

[Mark Manders · Accepted solution]

The issue with your script is that it retrieves and updates records with the same duplicate number within the same loop iteration without ensuring that the new number it's updating to isn't already in use. This can lead to a scenario where a record is updated to a number that becomes a duplicate by the time the script finishes running, especially if the NumberManager class doesn't check for existing numbers before returning a new number.

To fix this, you can modify your script to first collect all records that need updating and then update them in a separate step, ensuring that each new number generated is unique and not already present in the table.

var tables = ['u_table_1', 'u_table_2', 'u_table_3', 'u_table_4'];

for (var i = 0; i < tables.length; i++) {
    getDuplicatesAndUpdate(tables[i], 'u_number');
}

function getDuplicatesAndUpdate(tableName, fieldName) {
    var duplicates = getDuplicates(tableName, fieldName);
    updateDuplicatesWithNewNumber(duplicates, tableName, fieldName);
}

function getDuplicates(tableName, fieldName) {
    var duplicates = {};
    var agg = new GlideAggregate(tableName);
    agg.addAggregate('COUNT', fieldName);
    agg.addNotNullQuery(fieldName);
    agg.groupBy(fieldName);
    agg.addHaving('COUNT', '>', 1);
    agg.query();
    while (agg.next()) {
        var fieldValue = agg[fieldName].toString();
        if (!duplicates[fieldValue]) {
            duplicates[fieldValue] = [];
        }
        var gr = new GlideRecord(tableName);
        gr.addQuery(fieldName, fieldValue);
        gr.query();
        while (gr.next()) {
            duplicates[fieldValue].push(gr.getUniqueValue());
        }
    }
    return duplicates;
}

function updateDuplicatesWithNewNumber(duplicates, tableName, fieldName) {
    var updatedNumbers = {}; // Keep track of numbers already used for updates
    for (var fieldValue in duplicates) {
        var recordSysIds = duplicates[fieldValue];
        for (var i = 0; i < recordSysIds.length; i++) {
            var gr = new GlideRecord(tableName);
            if (gr.get(recordSysIds[i])) {
                var newNumber;
                do {
                    newNumber = new NumberManager(tableName).getNextObjNumberPadded();
                } while (updatedNumbers[newNumber]); // Ensure the new number is unique
                updatedNumbers[newNumber] = true; // Mark this new number as used
                gr[fieldName] = newNumber;
                gr.update();
            }
        }
    }
}

[Anil Lande · Accepted solution]

Hi,

You will face this issue until you set Highest value in number counter table.

1. Sort your table by number Column and copy the current Highest number in your table [eg. INC010583].

Check below table "sys_number_counter" and check the current counter value for your table [it must be very less compared to highest number 10583], update counter value with next next Highest number i.e 10584.

repeat same for all tables.

Once you update Highest counter value then you can run above script. It will start updating numbers from the counter your set.

[Mark Manders · Accepted solution]

The issue with your script is that it retrieves and updates records with the same duplicate number within the same loop iteration without ensuring that the new number it's updating to isn't already in use. This can lead to a scenario where a record is updated to a number that becomes a duplicate by the time the script finishes running, especially if the NumberManager class doesn't check for existing numbers before returning a new number.

To fix this, you can modify your script to first collect all records that need updating and then update them in a separate step, ensuring that each new number generated is unique and not already present in the table.

var tables = ['u_table_1', 'u_table_2', 'u_table_3', 'u_table_4'];

for (var i = 0; i < tables.length; i++) {
    getDuplicatesAndUpdate(tables[i], 'u_number');
}

function getDuplicatesAndUpdate(tableName, fieldName) {
    var duplicates = getDuplicates(tableName, fieldName);
    updateDuplicatesWithNewNumber(duplicates, tableName, fieldName);
}

function getDuplicates(tableName, fieldName) {
    var duplicates = {};
    var agg = new GlideAggregate(tableName);
    agg.addAggregate('COUNT', fieldName);
    agg.addNotNullQuery(fieldName);
    agg.groupBy(fieldName);
    agg.addHaving('COUNT', '>', 1);
    agg.query();
    while (agg.next()) {
        var fieldValue = agg[fieldName].toString();
        if (!duplicates[fieldValue]) {
            duplicates[fieldValue] = [];
        }
        var gr = new GlideRecord(tableName);
        gr.addQuery(fieldName, fieldValue);
        gr.query();
        while (gr.next()) {
            duplicates[fieldValue].push(gr.getUniqueValue());
        }
    }
    return duplicates;
}

function updateDuplicatesWithNewNumber(duplicates, tableName, fieldName) {
    var updatedNumbers = {}; // Keep track of numbers already used for updates
    for (var fieldValue in duplicates) {
        var recordSysIds = duplicates[fieldValue];
        for (var i = 0; i < recordSysIds.length; i++) {
            var gr = new GlideRecord(tableName);
            if (gr.get(recordSysIds[i])) {
                var newNumber;
                do {
                    newNumber = new NumberManager(tableName).getNextObjNumberPadded();
                } while (updatedNumbers[newNumber]); // Ensure the new number is unique
                updatedNumbers[newNumber] = true; // Mark this new number as used
                gr[fieldName] = newNumber;
                gr.update();
            }
        }
    }
}

[Anil Lande · Accepted solution]

Hi,

You will face this issue until you set Highest value in number counter table.

1. Sort your table by number Column and copy the current Highest number in your table [eg. INC010583].

Check below table "sys_number_counter" and check the current counter value for your table [it must be very less compared to highest number 10583], update counter value with next next Highest number i.e 10584.

repeat same for all tables.

Once you update Highest counter value then you can run above script. It will start updating numbers from the counter your set.

[Anurag Tripathi]

Here is a script i wrote some time back to get duplicates, hope this helps

You may need to modify it to fit for your purpose

gs.print(getDuplicates('<table Name>','<field name>')); 

function getDuplicates(tablename,val) { var dupRecords = []; 
var gaDupCheck = new GlideAggregate(tablename); gaDupCheck.addQuery('active','true'); gaDupCheck.addAggregate('COUNT',val); gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());  
}
return dupRecords;
}

[Aniket Chavan · Accepted solution]

Hello @Pranavi13 ,

Please give a try to the script below and see how it works for you.

var tables = ['u_table_1', 'u_table_2', 'u_table_3', 'u_table_4'];

for (var i = 0; i < tables.length; i++) {
    getDuplicates(tables[i], 'u_number');
}

// function to check for duplicates and updating them with new number
function getDuplicates(tablename, val) {
    var dupRecords = [];
    var gaDupCheck = new GlideAggregate(tablename);
    gaDupCheck.addAggregate('COUNT', val);
    gaDupCheck.addNotNullQuery(val);
    gaDupCheck.groupBy(val);
    gaDupCheck.addHaving('COUNT', '>', 1);
    gaDupCheck.query();
    while (gaDupCheck.next()) {
        dupRecords.push(gaDupCheck[val].toString());
    }

    for (var i = 0; i < dupRecords.length; i++) {
        var currentNum = dupRecords[i];
        var tableGr = new GlideRecord(tablename);
        tableGr.addQuery(val, currentNum);
        tableGr.query();

        var highestNumber = getHighestNumber(tablename, 'u_number');

        while (tableGr.next()) {
            highestNumber++;
            tableGr.u_number = highestNumber;
            tableGr.update();
        }
    }
}

// function to get the highest number in the table
function getHighestNumber(tablename, val) {
    var ga = new GlideAggregate(tablename);
    ga.addAggregate('MAX', val);
    ga.query();
    if (ga.next()) {
        return parseInt(ga.getAggregate('MAX', val)) || 0;
    }
    return 0;
}

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

Thanks,

Aniket