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;
}

}

[Sai Shravan]

Hi @triciav ,

To get the 26th record from the GlideAggregate, you can use the setLimit() method to limit the number of records returned by the query and setOffset() method to specify the starting record

var overridesGr = new GlideAggregate("accm_promotion");
overridesGr.addEncodedQuery("current_grade=senior_economist");
overridesGr.addAggregate('COUNT');
overridesGr.query();
overridesGr.setLimit(1); // Limit the number of records returned to 1
overridesGr.setOffset(25); // Start from the 26th record (offset is zero-based)

if (overridesGr.next()) {
  var pm = overridesGr.getAggregate("COUNT");
  gs.info("TRICIA RESULT " + pm);
  // Do something with the 26th record
  gs.info("This is the 26th record: " + overridesGr.getValue("column_name"));
}

Replace "column_name" with the actual column name of the record you want to retrieve.

Regards,
Shravan.

[triciav]

Shravan,

I need to do a median to get the Middle number of the count and then round it to the next whole number as indicated on my code. How can I get the  whole number result passed into the setOffset?

[triciav]

I am also getting

Evaluator: com.glide.script.RhinoEcmaError: Cannot find function setOffset in object [object GlideAggregate]. script : Line(6) column(0) 3: overridesGr.addAggregate('COUNT'); 4: overridesGr.query(); 5: overridesGr.setLimit(1); // Limit the number of records returned to 1 ==> 6: overridesGr.setOffset(25); // Start from the 26th record (offset is zero-based) 7: 8: if (overridesGr.next()) { 9: var pm = overridesGr.getAggregate("COUNT");

[Sai Shravan]

Hi @triciav ,

Here is the update code without using setOffset function

var overridesGr = new GlideAggregate("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.addQuery("ORDERBY", "column_name"); // Replace column_name with the name of the column to order by
  overridesGr.chooseWindow(offset, offset+1);
  overridesGr.query();

  if (overridesGr.next()) {
    gs.info("This is the " + median + "th record: " + overridesGr.getValue("column_name"));
  }
}