Group then select just a single latest record

[PJ363182]

I'm trying to create a report by grouping Cost Center then just select a single latest entry based on Created for each group. However, it shows only 1 group.

Can you please assist?

var a = Class.create();
a.prototype = Object.extendsObject(AbstractAjaxProcessor, {
aa: function(){

var count = new GlideAggregate('sys_user');
count.addAggregate('COUNT');
count.groupBy('cost_center');
count.query();
while (count.next()) {   
var customer = count.cost_center.getDisplayValue();
var customerLatest= new GlideRecord('sys_user');
customerLatest.addQuery('cost_center',customer );
customerLatest.setLimit(1);
customerLatest.orderByDesc('sys_created_on'); 
customerLatest.query();
if(customerLatest.next()){
return customerLatest.getValue('name');

}

}

},
    type: 'a'
});

[Sandeep Rajput]

@PJ363182 Try the following and see if this works.

var a = Class.create();
a.prototype = Object.extendsObject(AbstractAjaxProcessor, {
aa: function(){
var customerArray = [];
var count = new GlideAggregate('sys_user');
count.addAggregate('COUNT');
count.groupBy('cost_center');
count.query();
while (count.next()) {   
var customer = count.cost_center.getDisplayValue();
var customerLatest= new GlideRecord('sys_user');
customerLatest.addQuery('cost_center',customer );
customerLatest.setLimit(1);
customerLatest.orderByDesc('sys_created_on'); 
customerLatest.query();
if(customerLatest.next()){
customerArray.push(customerLatest.getValue('name'));
}
}
return customerArray.toString();
},
    type: 'a'
});

Hope this helps.

[Chaitanya ILCR · Accepted solution]

Hi @PJ363182 ,

Try this with Sandbox enabled as true on the script include

var a = Class.create();
a.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    aa: function() {
        var customersArr = [];
        var count = new GlideAggregate('sys_user');
        count.addAggregate('COUNT');
		// count.addNotNullQuery('cost_center') // add this if you don't want to add the user where cost center is empty
        count.groupBy('cost_center');
        count.query();
        while (count.next()) {
            var customer = count.getValue('cost_center');
            var customerLatest = new GlideRecord('sys_user');
            customerLatest.addQuery('cost_center', customer);
            customerLatest.setLimit(1);
            customerLatest.orderByDesc('sys_created_on');
            customerLatest.query();
            if (customerLatest.next()) {
                customersArr.push(customerLatest.getValue('name'));
            }
        }
		return customersArr.join();

    },
    type: 'a'
});

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[Shivalika]

Hello @PJ363182 

Please change the order of code. You first need to orderByDesc on created on field and then need to set the limit. What you are doing now is setting the limit first and then order by desc. This will already result in only one record. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway,

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY