Query users with multiple duplicate records.

[Maxwell3]

Hello,

I have a case where users are only allowed to have 1 record in the "Ready" state on a custom table. The issue is that users have more than one record in the "Ready" state. I need to query this custom table, and find users with more than one record in the "Ready" state, find the most current one and set the other ones to "cancel".

I have tried GlideAggregate but that only returns the total amount of records in the "Ready" state, I will probably be running this in a background script. Could someone point me in the right direction?

[Vaishnavi Lathk]

Hi Matthew,

You can go to Custom Table table 

Alternatively you can use this background Script:

var gaDupCheck1 = new GlideAggregate('CTableName'); 
     gaDupCheck1.addQuery('active','true'); 
     gaDupCheck1.addQuery('state','ready'); 
     gaDupCheck1.addAggregate('COUNT', 'caller_id'); 
     gaDupCheck1.groupBy('caller_id'); 
     gaDupCheck1.addHaving('COUNT', '>', 1); 
     gaDupCheck1.query(); 
     while (gaDupCheck1.next()) { 
             gaDupCheck1.setAbortAction(true);
     } 

Please mark as correct/helpful if this helps!

Thanks

Vaishnavi Lathkar

[Hitoshi Ozawa · Accepted solution]

Hi Maxwell,

Following script will set state to cancel on all records that is not the latest and are by duplicate user's record. I've set to turn off running workflow.

var tableName = '<table name>';  // name of table to query
var userFieldName = 'user_id';  // name of user field
var stateReady = 'ready';
var stateCancel = 'cancel';
var orderByFieldName = 'sys_updated_on';  // name of column to find most recent record

var gaTable = new GlideAggregate(tableName);
gaTable.addQuery('active', 'true');
gaTable.addQuery('state', stateReady);
gaTable.addAggregate('COUNT', userFieldName);
gaTable.orderByAggregate('COUNT', userFieldName); 
gaTable.orderBy(userFieldName); 
gaTable.groupBy(userFieldName);
gaTable.addHaving('COUNT', '>', 1);

gaTable.query();
while (gaTable.next()) {
    var gr = new GlideRecord(tableName);
    gr.addQuery(userFieldName, gaTable.getValue(userFieldName));
    gr.orderByDesc(orderByFieldName);
    gr.query();
    if (gr.next()) {
        gs.info('most recent user:' + gaTable.getDisplayValue(userFieldName) + ' updated:' + gr.getValue(orderByFieldName));
        while (gr.next()) {
            gs.info('cancelled   user:' + gaTable.getDisplayValue(userFieldName) + ' updated:' + gr.getValue(orderByFieldName));
			grTable.setValue('state', stateCancel);
            grTable.setWorkflow(false);
            grTable.update();
        }
    }
}

[Maxwell3]

Thank you for your reply Hitoshi. The query only runs once, I am not sure why. I changed all the 'if' to 'while' but that did not work. What do you suggest?

[Maxwell3]

Hello Hitoshi,

My apologies,

I got it to run now. I had a ';' where I should have put a '{ }'. 

Thanks again.