Compare one field value in a list with the field value from the next record in the list

[Andrew Bettcher]

Hi,

I have a list of records. I want to get a value from one field on the first record and see if it matches the value from the same field on the next record.

I can't work out how to get it to iterate to the next record. Using next() just goes through the list of records but it doesn't keep the original value. Here is my current script:

var grProtonDupe = new GlideRecord('u_cmdb_ci_proton_devices');
grProtonDupe.addEncodedQuery('u_device_type=Email^name=specific.email@address');
grProtonDupe.orderByDesc('serial_number');
grProtonDupe.orderByDesc('sys_created_on');
grProtonDupe.query();

//get the first serial number for comparison to the next one in the list

var currentSerial = grProtonDupe.serial_number; 
	var currentSysid = grProtonDupe.sys_id;

while (grProtonDupe.next()){

		var nextSerial = grProtonDupe.serial_number;
		var nextSysid = grProtonDupe.sys_id;
		
		gs.info(currentSysid + '    ' + nextSysid);
	}

I added sys_id because in my test data there are 3 records with the same serial number. My thinking is that I define my current sys_id and then move to the next() record so my two sys_ids should then be different but they aren't. 

[Brad Bowman · Accepted solution]

You need to do the comparison inside the while loop, so here's one way to do that:

var prevSerial = ''; 
var prevSysid = '';
var grProtonDupe = new GlideRecord('u_cmdb_ci_proton_devices');
grProtonDupe.addEncodedQuery('u_device_type=Email^name=specific.email@address');
grProtonDupe.orderByDesc('serial_number');
grProtonDupe.orderByDesc('sys_created_on');
grProtonDupe.query();
while (grProtonDupe.next()){
    if (prevSerial == '') {
        prevSerial = grProtonDupe.serial_number; 
        prevSysid = grProtonDupe.sys_id.toString();
    } else if (prevSerial == grProtonDupe.serial_number) {
        gs.info('Serial # ' + prevSerial + ' ' + prevSysid + '  ' + grProtonDupe.sys_id.toString());
    }
}

[Vishal Birajdar]

Hello @Andrew Bettcher 

I have tried on "Incident" table. you can do it for your table

I don't know if this work for you ....but please check if it works for you...!!

var number=[];
var count=0;

var grProtonDupe = new GlideRecord('incident');
grProtonDupe.addEncodedQuery('active=true^caller_id=6816f79cc0a8016401c5a33be04be441');  // caller Id - sys_id of system admin user 
grProtonDupe.orderByDesc('number');
grProtonDupe.orderByDesc('sys_created_on');

grProtonDupe.query();

while (grProtonDupe.next()){

 //push first incident sys in array
    number.push(grProtonDupe.getUniqueValue() );

   var index = number[count]
//again glide record on incident table but you need to add extra query

    var grProtonDupe1 = new GlideRecord('incident');
    grProtonDupe1.addEncodedQuery('active=true^caller_id=6816f79cc0a8016401c5a33be04be441^sys_id!=' + index);
    grProtonDupe1.orderByDesc('number');
    grProtonDupe1.orderByDesc('sys_created_on');

    grProtonDupe1.query();
    count = count + 1;

    while(grProtonDupe1.next()){

    //now compare 
    if(grProtonDupe.getValue('number') == grProtonDupe1.getValue('number') ){

        gs.info("Same incident number");
        // do your job
    }else {

        gs.info("Different incident number");
        //do your job

    }

}

}

Have a try...!!

[Brad Bowman · Accepted solution]

You need to do the comparison inside the while loop, so here's one way to do that:

var prevSerial = ''; 
var prevSysid = '';
var grProtonDupe = new GlideRecord('u_cmdb_ci_proton_devices');
grProtonDupe.addEncodedQuery('u_device_type=Email^name=specific.email@address');
grProtonDupe.orderByDesc('serial_number');
grProtonDupe.orderByDesc('sys_created_on');
grProtonDupe.query();
while (grProtonDupe.next()){
    if (prevSerial == '') {
        prevSerial = grProtonDupe.serial_number; 
        prevSysid = grProtonDupe.sys_id.toString();
    } else if (prevSerial == grProtonDupe.serial_number) {
        gs.info('Serial # ' + prevSerial + ' ' + prevSysid + '  ' + grProtonDupe.sys_id.toString());
    }
}

[Brad Bowman · Accepted solution]

Sorry, I just realized I forgot the last 'else' to update the prev variables when the serial numbers don't match, so that it will detect the next set of dupes.

var prevSerial = ''; 
var prevSysid = '';
var grProtonDupe = new GlideRecord('u_cmdb_ci_proton_devices');
grProtonDupe.addEncodedQuery('u_device_type=Email^name=specific.email@address');
grProtonDupe.orderByDesc('serial_number');
grProtonDupe.orderByDesc('sys_created_on');
grProtonDupe.query();
while (grProtonDupe.next()){
    if (prevSerial == '') {
        prevSerial = grProtonDupe.serial_number; 
        prevSysid = grProtonDupe.sys_id.toString();
    } else if (prevSerial == grProtonDupe.serial_number) {
        gs.info('Serial # ' + prevSerial + ' ' + prevSysid + '  ' + grProtonDupe.sys_id.toString());
    } else {
        prevSerial = grProtonDupe.serial_number; 
        prevSysid = grProtonDupe.sys_id.toString();
    }
}

[Andrew Bettcher]

Thank you Brad. I didn't expect a further update after I'd already accepted the solution. I've got it in place and I'm just working out a few kinks with the data before doing a dry run.

Thank you again.