Glide aggregate count how to return a specific record from the count

[triciav]

How can I return a specific record from the Glide Aggregate

var numbers = [];
var overridesGr = new GlideAggregate("accm_promotion");
overridesGr.addEncodedQuery("current_grade=senior_economist");//current_grade=senior_economist
overridesGr.addAggregate('COUNT');
overridesGr.query();
while (overridesGr.next()) {
//return overridesGr.getAggregate('COUNT');
var pm = overridesGr.getAggregate("COUNT");

gs.info(pm);
numbers.push(pm);
}

median(numbers);

function median(numbers) {
// median of [3, 5, 4, 4, 1, 1, 2, 3] = 3
var median = 0,
numsLen = numbers.length;
if (
numsLen % 2 === 0 // is even
) {
// average of two middle numbers
median = (numbers[numsLen / 2 - 1] + numbers[numsLen / 2]) / 2;
} else { // is odd
// middle number only
median = numbers[(numsLen - 1) / 2];
}
result = median / 2;
var rnd = Math.round(parseFloat(result));
gs.info("TRICIA RESULT " + result +" "+rnd); //TRICIA RESULT 25.5 (26) so I need the 26th record!
median = rnd;
if(median ==rnd){
gs.info(median);
gs.info("This is the 26th record: " + overridesGr.How to get the 26th record returned????);
}
var value = result;
if (isNaN(value)) {
value = 0;
}

}

[triciav]

Shravan,

I am getting

x_g_fr4_frbny_rs_0: TRICIA RESULT 51
x_g_fr4_frbny_rs_0: This is the median value: 26
field name 'ORDERBY' not found in table 'x_g_fr4_frbny_rs_0_accm_promotion'
Error getting record for x_g_fr4_frbny_rs_0_accm_promotion: java.lang.NullPointerException: 	com.glide.db.meta.Table.queryDistinctGroupCount(Table.java:545)
	com.glide.db.meta.Table.queryAggregate(Table.java:481)
	com.glide.db.meta.Table.query(Table.java:204)
	com.glide.script.GlideRecordITable.query(GlideRecordITable.java:115)
	com.glide.script.GlideRecord.query0(GlideRecord.java:3377)

[triciav]

Shravan,

I changed the code to below and I don't get the errors, but I am also not getting the result 

var overridesGr = new GlideAggregate("x_g_fr4_frbny_rs_0_accm_promotion");
overridesGr.addEncodedQuery("current_grade=senior_economist");
overridesGr.addAggregate('COUNT');

overridesGr.query();

if (overridesGr.next()) {
var pm = overridesGr.getAggregate("COUNT");
gs.info("TRICIA RESULT " + pm);
var median = Math.ceil(pm/2);
gs.info("This is the median value: " + median);
var offset = median - 1; // Offset is zero-based
overridesGr.setLimit(1);
overridesGr.orderBy("current_grade"); // Replace column_name with the name of the column to order by
overridesGr.chooseWindow(offset, offset+1);

gs.info("This is the " + median + "th record: " + overridesGr.getValue("sys_id"));
}

x_g_fr4_frbny_rs_0: TRICIA RESULT 51
x_g_fr4_frbny_rs_0: This is the median value: 26
x_g_fr4_frbny_rs_0: This is the 26th record: 

I think we may almost be there
Thank you so much for helping me

[Sai Shravan · Accepted solution]

Hi @triciav ,

o get the sys_id of the record at the 26th position, you need to add overridesGr.query() after calling overridesGr.chooseWindow(offset, offset+1) as shown below:

overridesGr.chooseWindow(offset, offset+1);
overridesGr.query(); // Add this line to execute the query
gs.info("This is the " + median + "th record: " + overridesGr.getValue("sys_id"));

This should retrieve the record at the 26th position and print its sys_id.

[triciav]

Shravan - Thank you so much for your help!