Update field value using GlideAggregate

[Pratiksha Lang1]

I want to update the field value using GlideAggregate but as per the articles I went through, I saw that the value of a field can't be updated using GlideAggregate.

Please help me with the code below:

var ga = new GlideAggregate('x_amspi_smdrs_app_ola_score');
ga.addQuery('frequency', 'Daily');
ga.addQuery('site', 'BDC11');
ga.addQuery('role', 'DE');
ga.addQuery('ola_type', 'measurable');
ga.addQuery('ola_formula_score','not_met_4');
ga.addAggregate('COUNT', 'sub_function');
ga.orderByAggregate('COUNT', 'sub_function');
ga.addAggregate('SUM', 'sub_score');
// ga.addQuery('sub_score','!=','');
ga.query();
var gr = new GlideRecord('x_amspi_smdrs_app_ola_score');
gr.addQuery('frequency', 'Daily');
gr.addQuery('site', 'BDC11');
gr.addQuery('role', 'DE');
gr.addQuery('ola_type', 'measurable');
gr.addQuery('ola_formula_score', 'not_met_4');
gr.query();
var score = ga.getValue('sub_score');
var i = 0;
var stdout = [];
while((ga.next() && ( i++ < 20)){
var count = ga.getAggregate('COUNT', 'sub_function');
var sum = ga.getAggregate('SUM', 'sub_score');
gs.info(ga.getDisplayValue('sub_function') +' '+ count+ ' '+sum );
var total = sum/count;
gs.info('total is : ' +total);
gr.total_score = total;
gr.update();
}

[Sai Shravan]

Hi @Pratiksha Lang1 ,

You are correct that you cannot update a field value using GlideAggregate. GlideAggregate is used to query and aggregate data from a table.

To update a field value, you should use GlideRecord instead 

var gr = new GlideRecord('x_amspi_smdrs_app_ola_score');
gr.addQuery('frequency', 'Daily');
gr.addQuery('site', 'BDC11');
gr.addQuery('role', 'DE');
gr.addQuery('ola_type', 'measurable');
gr.addQuery('ola_formula_score', 'not_met_4');
gr.query();
while(gr.next()){
  var total_score = gr.sub_score / gr.sub_function;
  gr.total_score = total_score;
  gr.update();
}

we are using GlideRecord to query records that match criteria. We then loop through each record and calculate the total score using the sub_score and sub_function fields. Finally, we update the total_score field with the calculated value using gr.update().

Regards,

Shravan.

Please mark this as helpful and correct answer based on the impact

[Pratiksha Lang1]

Hi @Sai Shravan  Thank You for your reply. however, This code doesn't work. I want to get an average in total score field. sum is getting the sum of sub score and dividing it by sub_function row count. 

[Sai Shravan]

Hi @Pratiksha Lang1 ,

To calculate the average of total_score across all the matching records, you can modify the code like this:

var gr = new GlideRecord('x_amspi_smdrs_app_ola_score');
gr.addQuery('frequency', 'Daily');
gr.addQuery('site', 'BDC11');
gr.addQuery('role', 'DE');
gr.addQuery('ola_type', 'measurable');
gr.addQuery('ola_formula_score', 'not_met_4');
gr.query();

var totalScoreSum = 0;
var recordCount = 0;

while(gr.next()) {
  var totalScore = gr.sub_score / gr.sub_function;
  gr.total_score = totalScore;
  gr.update();

  totalScoreSum += totalScore;
  recordCount++;
}

if(recordCount > 0) {
  var averageScore = totalScoreSum / recordCount;
  gs.info("Average total score: " + averageScore);
}