Script to sort results list by state

[mballinger]

Hello,

I have a background script that I need to be able to sort records by state. My state has several choices: Pending, Verified, Cancelled, Expired. The way this needs to be sorted is by most recently updated and pending state. All pending records should show first, but the states need to be grouped.

This is what I have so far:

var gr = new GlideRecord('u_custom_table');
gr.addQuery('active=true');
gr.groupBy('u_status');
gr.orderByDesc('u_status');
gr.orderByDesc('sys_updated');
gr.query();
while(gr.next()) {
   gs.print('u_number' + ' Updated on ' + gr.sys_updated + ' Status: ' + gr.u_status);
}

 Thanks!

[Valmar Neves]

Hi,

Try this:

var gr = new GlideRecord('u_custom_table');
gr.addQuery('active=true');
gr.orderBy('u_status');
gr.setLimit(20);
gr.query();
while (gr.next()) {
    gs.print('Number: ' + gr.getValue('u_number') + ' Updated on: ' + gr.getDisplayValue('sys_updated') + 'Status: ' + gr.getDisplayValue('u_status'));
}

Regards,

[mballinger]

@Valmar Neves - Thanks for your response! This returns only 20 records, I need all records to display. Pending is also not the first to appear on the output. My goal is to sort this list by Pending first

[Jake Sadler · Accepted solution]

Hi,

Simple fix for this. You need to use GlideAggregate for the groupBy method.

var agg = new GlideAggregate('incident');

agg.groupBy('state');

agg.orderByDesc('sys_updated');

agg.query();

[mballinger]

@Jsadler19 - Thanks for your response! My pending state falls in the middle, so if I group by status, it still wont display pending first. How can I show the pending status first?