How can I iterate through a GlideRecord query and compare the previous record to the current one?

[Community Alums]

Hello Servicers of the Now, I'm in a bit of a pickle, and it has to do with GlideRecords. Specifically, I'm trying to iterate through a single Query, and compare the previous record to the current, similar to the following code: 

//Declare an array.
var arr = [5, 4, 3, 2, 1];
var i = 0;
var j = 1;
var sum = 0;

//Iterate through the array and add the previous value to the current.
while (j < arr.length) {
     sum = arr[i]+arr[j];
     i++;
     j++;
}

return sum;

My real world use case is that I have a Script Include (TotalConsumption) that takes and returns an item's average rate of consumption within an inventory. The data is sourced from an 'Inventory History' table, filled with snapshots of the inventory at different points in time. My current solution involves the following process: 

1. Query the Inventory History for all records between two points in time, for a particular Inventory record.  
2. Iterate through the GlideRecord, comparing the current value to the previous.
3. 1. I account for Additions to the Inventory (like receiving an order) by checking to see if there are Orders received between current and previous. 
   2. Items Consumed = (current - orders recieved) - previous
4. Get the average Rate of Consumption by dividing Total # Consumed by the RowCount of the Query. 

However, I'm running into one key problem: Every time I iterate through the loop, next == previous. They're set to the same record. 

Here's my code for context: 

getConsumption: function(startDate, endDate, inv) { //Parameter Types: GlideDateTime obj, GlideDateTime obj, sys_id
		//Get the span of time between start and end date. 
		var timeSpan = GlideDateTime.subtract(startDate, endDate); //Obviously not proper date math yet.
		
		//Query Inventory History.
		var gr = new GlideRecord('x_244116_smart_inv_history');
		//Query between startDate & endDate.  
		gr.addQuery('time_recorded', '>=', startDate); 
		gr.addQuery('time_recorded', '<=', endDate);
		gr.addQuery('source', '0'); //Automatic Updates only.
		gr.addQuery('inventory', inv); //Only for one Inventory Record.
		gr.query();
		
		//Iterate Once to get a value.
		gr.next();
		
		//Variables used to find Inventory Consumption.
		var prev = gr;
		var nxt;
		var consumption = 0;
		var ordered = 0;
		
		//Loop through the Records to get the total amount consumed.
		while (gr.next()) {
			nxt = gr; //Set nxt to the current 
			ordered = 0; //Reset Ordered.
			
			if (nxt.sys_id == prev.sys_id) //Check: Are next and prev the same? 
				gs.info('[Fail State | '+gr.inventory.getDisplayValue()+'] nxt.sys_id: '+nxt.sys_id+ '== prev.sys_id: '+prev.sys_id); //This is called every iteration except for the first.
			
			//Get any Orders that occurred between next and previous.
			var orderHist = new GlideRecord('x_244116_smart_inv_history');
			orderHist.addQuery('source', '3'); //Source 'Automatic - Delivery', which is inserted exclusively by the Order Lifecycle Workflow.
			orderHist.addQuery('time_recorded', '>=', prev.time_recorded);
			orderHist.addQuery('time_recorded', '<=', nxt.time_recorded);
			orderHist.addQuery('inventory', inv);
			orderHist.query();
			
			//Get the amounts added between next and prev.
			while (orderHist.next()) {
				ordered += orderHist.captured - prev.captured - difference;
			}

			//Add to amount consumed.
			consumption += (nxt.captured - ordered) - prev.captured;
			
			//I want to set Prev after the loop.
			prev = gr; //This is moved to the top of the function due to hoisting. Either that, or it's not registering the correct value for some reason.
		}
		//Return the average rate of consumption.
		gs.info('[getConsumption('+gr.inventory.getDisplayValue()+')] gr.getRowCount: '+gr.getRowCount()+' | timeSpan: '+timeSpan.getDisplayValue()+' | Consumption: '+consumption);
		return consumption/gr.getRowCount();
	}

From my research, I've come to believe this has to do with JS variable hoisting, and have an alternative solution involving another, duplicate query that I stagger alongside the main query (two GlideRecord queries with the exact same conditions, with one 1 step ahead of the other), but I would think this should be possible within a singlular query - at the very least, it would be a much healthier solution for the system. What do you think? 

[SanjivMeher]

Your first object will be empty when you do a nxt.sys_id == prev.sys_id.

Instead you should check, if this is the first loop, dont compare. Because there is no Previous record, when you are on the first record.

I think you should add a counter = 0 and increment it in while loop. Only if counter is greater than 0, compare it.

[Community Alums]

Hi Sanjiv, I appreciate that advice, but that has nothing to do with my actual issue unless I'm mistaken in my understanding of the code - at the very beginning of the loop, I set nxt = gr, which should have a value already because the while loop runs gr.next() before it goes through the first iteration. Prev has the very first record, since I run gr.next() once before the loop begins as well. 

How would a counter solve the issue of prev and nxt being the exact same every iteration? Am I misunderstanding how my code is running with the loop and such?

[SanjivMeher]

I would remove the gr.next() and move the add var prev = gr; inside while loop with condition count == 0.

Because I am not sure if while (gr.next()) will take the next instance of gr.

[Chuck Tomasi · Accepted solution]

I did a simple version of your script just to prove my theory... GlideRecord is a pointer. You can see this if you save sys_ids to an array in a simple script like this:

var gr = new GlideRecord('incident');
gr.query();

var list = [];

while (gr.next()) {
    list.push(gr.sys_id);
}

gs.info(list.join('\n'));

All the values are the same as the last because you are copying a reference, not a value. So in my "mini" version of your script, I did this:

var inc = new GlideRecord('incident');
inc.orderBy('number');
inc.query();

inc.next();
var prev = inc;

while (inc.next()) {
	var nxt = inc;
	gs.info(prev.number + '--' + nxt.number);
	prev = inc;
}

Instead of seeing "1--2" then "2--3" and "3--4", I got "1--1", "2--2", and "3--3" - again, pointers (references.)

If you want to get the values of specific fields, save them separately and distinctly like this:

var prev = {
   "start_time" : gr.getValue('start_time'),
   "end_time" : gr.getValue('end_time')
};

Using getValue() ensures you are getting a COPY of the value, not a pointer to it. You'll then do your comparisons or calculations on prev.start_time and prev.end_time for example.