Group by problem record on incident table and sort by created date on descending order

[srilaxmi1]

HI Team,

I am unable to get the orderByDesc('sys_created_on') on GlideAggregate method.

How should i get most recent date(sys_created_on)  when we group by problem. Screenshot will have most recent date for each problem.

1. On incidents table, when we group by Problem column.

2. Multiple incidents are associated for each problem. On these incidents, Most recent date (created date) incident number is needed. for others needs to be ignored. 

I have tried several scripts, Unable to get the above result. Please help me to modify below script.

gs.print(getDuplicates('incident','problem_id'));

function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);

gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
//gaDupCheck.orderByDesc('sys_created_on');
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString()); // Here i am getting problem_id sys_id's
}
return dupRecords;
}

Attaching the screenshot.

[Aniket Chavan]

Hello @srilaxmi1 ,

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

gs.print(getLatestIncidents('incident', 'problem_id', 'sys_created_on'));

function getLatestIncidents(tablename, groupByField, orderByField) {
    var latestIncidents = [];
    var gaLatestIncidents = new GlideAggregate(tablename);

    gaLatestIncidents.addAggregate('MAX', orderByField);
    gaLatestIncidents.addNotNullQuery(groupByField);
    gaLatestIncidents.groupBy(groupByField);
    gaLatestIncidents.query();

    while (gaLatestIncidents.next()) {
        var latestIncident = {};
        latestIncident[groupByField] = gaLatestIncidents[groupByField].toString();
        var maxCreatedDate = gaLatestIncidents.getAggregate('MAX', orderByField);
        
        var incident = new GlideRecord(tablename);
        incident.addQuery(groupByField, latestIncident[groupByField]);
        incident.addQuery(orderByField, maxCreatedDate);
        incident.query();
        
        if (incident.next()) {
            latestIncident['incident_number'] = incident.getValue('incident_number');
            latestIncident['sys_created_on'] = incident.getValue(orderByField);
            latestIncidents.push(latestIncident);
        }
    }

    return latestIncidents;
}

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

Thanks,

Aniket

[Maddysunil]

@srilaxmi1 

To retrieve the most recent incident for each problem, you can use a GlideQuery instead of a GlideAggregate, as GlideAggregate does not support ordering by a field and grouping simultaneously.

function getRecentIncidentsByProblem() {
    var recentIncidents = [];

    var grProblem = new GlideRecord('problem');
    grProblem.query();

    while (grProblem.next()) {
        var grIncident = new GlideRecord('incident');
        grIncident.addQuery('problem_id', grProblem.sys_id);
        grIncident.orderByDesc('sys_created_on'); // Order by created date in descending order
        grIncident.setLimit(1); // Limit the result to only one record, which will be the most recent incident
        grIncident.query();

        if (grIncident.next()) {
            recentIncidents.push({
                'problem': grProblem.getValue('problem_id'), 
                'incident_number': grIncident.getValue('number'), 
                'created_on': grIncident.getValue('sys_created_on')
            });
        }
    }

    return recentIncidents;
}

var recentIncidents = getRecentIncidentsByProblem();

for (var i = 0; i < recentIncidents.length; i++) {
    gs.print('Problem: ' + recentIncidents[i].problem + ', Most Recent Incident Number: ' + recentIncidents[i].incident_number + ', Created On: ' + recentIncidents[i].created_on);
}

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