How to get the latest updated record from the sys_history_line table for multiple records

[Omkar Kumbhar]

Hello Everyone,

I have a query regarding how I get the latest updated record from the below multiple records.

Please find the below screenshot.

Here if you see, I have 2 incidents whose states are changed multiple times.

now what I want for incident 9009 the latest record and similarly for incident 9001, I don't want other records I need only the latest updated record of both these incidents.

I need to print this data in email notifications.

I have tried using the glide aggregrate method it works fine. But when I try to populate or print old value and new value it does not print this is a drawback of this method. Because data can be pulled and populated by the glide record method.

Is there any other different way to achieve this? 

If you are suggesting with glide aggregate method. please let me know how you print the old and new values.

Thank you,

Omkar

[Shruti · Accepted solution]

Hi,

I tried this in background script

var arr = [];
var grSet = new GlideAggregate('sys_history_line');
grSet.addEncodedQuery('set=3ea31e7097d1b1103afb3d400153aff0^ORset=baa35e7097d1b1103afb3d400153afef'); 
grSet.addAggregate('count');
grSet.orderByAggregate('count');
grSet.groupBy('set');
grSet.query();
while (grSet.next()) {
    arr.push(grSet.set.toString());

}

for (var k = 0; k < arr.length; k++) {
    
var gr1 = new GlideRecord('sys_history_line');
gr1.addQuery('set',arr[k]);
gr1.orderByDesc('update_time');
gr1.setLimit(1);
gr1.query();

while(gr1.next()){
gs.info(gr1.label);
gs.info(gr1.field);
gs.info(gr1.set.getDisplayValue());

}
}

[Shruti]

hi,

You can use glide record and order by updated time or update number

[Omkar Kumbhar]

yes, I have tried it. But, my concern is for 2 incidents I need single records. even I tried using setlimit it will give only the latest updated record from the whole table. I don't want that, I want one incident one latest record another incident another latest record.

[Shruti · Accepted solution]

Hi,

I tried this in background script

var arr = [];
var grSet = new GlideAggregate('sys_history_line');
grSet.addEncodedQuery('set=3ea31e7097d1b1103afb3d400153aff0^ORset=baa35e7097d1b1103afb3d400153afef'); 
grSet.addAggregate('count');
grSet.orderByAggregate('count');
grSet.groupBy('set');
grSet.query();
while (grSet.next()) {
    arr.push(grSet.set.toString());

}

for (var k = 0; k < arr.length; k++) {
    
var gr1 = new GlideRecord('sys_history_line');
gr1.addQuery('set',arr[k]);
gr1.orderByDesc('update_time');
gr1.setLimit(1);
gr1.query();

while(gr1.next()){
gs.info(gr1.label);
gs.info(gr1.field);
gs.info(gr1.set.getDisplayValue());

}
}

[Omkar Kumbhar · Accepted solution]

Thanks for your quick help. I have also tried the same thing and it is working fine.