Get only the most recent RITM for each Catalog Item

[Nen]

Hi All,

I am planning to create a report which will return only the most recent RITM for each Catalog Item that we have in our system. I know I can use group by to group the records per Item, but is there an easy way to get only the most recent record?

I have an idea in mind but that will involve iterating through all the RITM records n number of times (where n is the number of Catalog Items that we have) and I know that doesn't sound like a good idea performance-wise so I just wanted to check with you if I'm missing something.

Regards,

N

[Rajesh Chopade1]

hi @Nen 

To achieve your goal more efficiently, you can leverage GlideAggregate, which is designed for optimized aggregation queries. Following is the sample script which will return you most recent RITM per catalog item:

var ga = new GlideAggregate('sc_req_item');  // RITM table
ga.addAggregate('MAX', 'opened_at');  // Get the most recent RITM based on the 'opened_at' field
ga.groupBy('cat_item');  // Group by Catalog Item
ga.query();

while (ga.next()) {
    var catalogItem = ga.getValue('cat_item');  // Catalog Item ID
    var latestRITM = ga.getValue('MAX_opened_at');  // The most recent 'opened_at' date
    gs.info('Catalog Item: ' + catalogItem + ' - Latest RITM: ' + latestRITM);
}

I hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

Rajesh

[Tai Vu]

Hi @Nen 

It may require to build a scripted filter to achieve. We're gonna have a function to collect the most recent request for each catalog item, then having an array of sys_ids in return.

Cheers,

Tai Vu

[Ankur Bawiskar · Accepted solution]

@Nen 

you will have to create a client callable script include

Then invoke it from report filter condition using this syntax. Report will be on sc_req_item table.

SysId [IS ONE OF] javascript: getRecords();

function getRecords() {
    var gr = new GlideAggregate('sc_req_item');
    gr.addAggregate('MAX', 'sys_created_on');
    gr.groupBy('cat_item');
    gr.query();

    var recentRITMs = [];
    while (gr.next()) {
        var recentRITM = new GlideRecord('sc_req_item');
        recentRITM.addQuery('cat_item', gr.cat_item);
        recentRITM.addQuery('sys_created_on', gr.getAggregate('MAX', 'sys_created_on'));
        recentRITM.query();
        if (recentRITM.next()) {
            recentRITMs.push(recentRITM.sys_id.toString());
        }
    }

    return recentRITMs.toString();

}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[PritamG]

use a database view with MAX(sys_created_on) grouped by cat_item to get the most recent RITM for each Catalog Item efficiently.