How to sum variables from a loop?

[Lisa Silvaroli]

I have this table in an email notification using an email script that is populated via GlideRecord query.

I want to take the “Total Amount” column and add it in the total. I have the “total amount” being populated by multiplying rate (gr.time_sheet.u_adjusted_rate) x hours (gr.total). Total Amount = roundedPay. 

I want to just add up all the “roundedPay” and add it in the empty cell. I am not sure how to do this since it is a new variable and not directly from the query. Below is my code (that is not working). Any ideas how I can accomplish this?

var gr=new GlideRecord('time_card');
	gr.addQuery('time_sheet', current.sys_id);
	gr.addQuery('category','Straight Time');
	gr.orderBy('u_date');
	gr.query();

	var StraightTotal = new GlideAggregate ('time_card');
	StraightTotal.addQuery('time_sheet', current.sys_id);
	StraightTotal.addQuery('category', 'Straight Time');
	StraightTotal.setGroup(false);
	StraightTotal.addAggregate('SUM','total');
	StraightTotal.query();
	//gs.log('Total '+ parseFloat(straightTime).toFixed(2));
	if(StraightTotal.next()){
		gs.info('SUM: ' + StraightTotal.getAggregate('SUM', 'total'));
	}
	while(gr.next()){
		var straightTime = StraightTotal.getAggregate('SUM', 'total');
		var Total = parseFloat(straightTime).toFixed(2);
		var rate = gr.time_sheet.u_adjusted_rate;
		var hours = gr.total;
		var pay = rate * hours;
		var roundedPay = parseFloat(pay).toFixed(2);
		var TotalAmount = roundedPay;

		var obj = JSON.parse(roundedPay);
		var nums = [];
		var tot = 0;

		for (var i=0; i<obj.length; i++){
			tot += parseFloat(obj[i]);
		}

[sekhar kurumoju]

Hai @Lisa Silvaroli 

I would like  Recommend to User GliedQuery.

we need More INFO.

Can You Please Share Screen shot You 'time_card' Table List View based on we give this solution

BR,

Sekhar Kurumoju

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Lisa Silvaroli]

I am using the time_card and time_sheet tables OOB (with a couple of added fields). 

I think the query is ok because I am able to set the Total Amounts from the query, I just don't know how to then add them together for the total. 

[sekhar kurumoju]

Hai @Lisa Silvaroli 

Yes, your logic But Here DEBUG your Script In to  Background Script Then know what is excat issue 

var gr=new GlideRecord('time_card');
	gr.addQuery('time_sheet', 'a121c1ce344nncdiddid9dds9');// passs on sys_id record 

	gr.addQuery('category','Straight Time');
	gr.orderBy('u_date');
	gr.query();

	var StraightTotal = new GlideAggregate ('time_card');
	StraightTotal.addQuery('time_sheet', current.sys_id);
	StraightTotal.addQuery('category', 'Straight Time');
	StraightTotal.setGroup(false);
	StraightTotal.addAggregate('SUM','total');
	StraightTotal.query();
	//gs.log('Total '+ parseFloat(straightTime).toFixed(2));
	if(StraightTotal.next()){
		gs.info('SUM: ' + StraightTotal.getAggregate('SUM', 'total'));
	}
	while(gr.next()){
		var straightTime = StraightTotal.getAggregate('SUM', 'total');
		var Total = parseFloat(straightTime).toFixed(2);
                gs.info(Total);
		var rate = gr.time_sheet.u_adjusted_rate;
                gs.info(rate );
		var hours = gr.total;
                 gs.info(hours);
		var pay = rate * hours;
                gs.info(pay);
		var roundedPay = parseFloat(pay).toFixed(2);
                 gs.info(roundedPay);
		var TotalAmount = roundedPay;
                 gs.info(TotalAmount);
		var obj = JSON.parse(roundedPay);
		var nums = [];
		var tot = 0;

		for (var i=0; i<obj.length; i++){
			tot += parseFloat(obj[i]);

		}
 gs.info(nums);
 gs.info(tot);

// cam you run this Code In your back Ground Script and revert back waht is excet output  plesae share here

BR,

Sekhar Kurumoju

Please mark reply as Helpful/Correct, if applicable. Thanks!

[DrewW]

Move this var declare out side the loop

var TotalAmount = 0;

and then in that loop do

TotalAmount += roundedPay;

This is assuming you want your column total to come from the TotalAmount var.