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? 

[Community Alums]

Ahhh my old nemesis, pointers... thank you for pointing this out, that fixed it! I had the wrong idea about what GlideRecord objects were haha.

(Hah...just realized I made a pun up there.)

[Javier Arroyo]

Would you know if there is documentation around how gr.next works internally? Or GlideRecord overall?

This behavior can be explained by JavaScript evaluation strategy (call by sharing), or mutability vs immutability (Objects are mutable while primitives are immutable).

Immutability causes gr.sys_id.toString(), or gr.getValue('sys_id'), gr.sys_id + '' to work as a new copy of the primitive is created and returned allowing the Object reference to change without affecting the newly set variable. Otherwise they'd mutate as well when the parent object changed. 

Mutability vs Immutability can be seen when creating objects within a while( gr.next()) loop. Keys don't need getValue() or any other explicit conversion to maintain the correct value because JavaScript internally converts keys into strings. This isn't the case, however, for key's value. Mutability is the reason an Object contained within gr.field_name changes values when stored within other objects, the new value will be whatever .next logic points it to, causing it to change where ever else it is being used. But, if the value is a primitive, it won't changing with the changing pointer. 

 Has SNow consider adding a toJSON method to GlideRecord, and it's fields? Or make some field properties enumerable? 

With a toJSON object something like the code below would work. Or if any enumerable properties were exposed, something similar to Object.assign ({}, gr.field_name) could be created for this very purpose. It would be possible toJSON a GlideRecord returning enumerable properties without having to do it ourselves. The lazy in me wants it. 🙂

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

var fields = {};

while (incident.next() ) {
  fields[incident.sys_id] = incident.sys_id.toJSON();

}

//output
gs.debug( global.JSON.stringify(fields, null, 2));
{
   sys_id_1: {
       value: sys_id_1, type: string, lable: something1
   },

   sys_id_2: {
       value: sys_id_2, type: string, lable: something2
   }

   sys_id_3: {
       value: sys_id_3, type: string, lable: something3
   }

}

[kevin146]

Hey Chuck, this works fine when adding individual fields to the array.  I want to add the gliderecord to the array.  Any thoughts?  I would rather NOT store the sys_ids in the array and then re-retrieve the glide record (I already retrieved the record, no need to retrieve it again).

function getVTBLanes(grBoard) {
	var arLanes = [];
	var grLN = new GlideRecord("vtb_lane");
	try {
		gs.print(grBoard.sys_id);
		grLN.addEncodedQuery("board=" + grBoard.sys_id);
		grLN.query();
		while (grLN.next()) {
			var grLane = grLN;
			arLanes.push(grLane);
		}
	} catch(ex) {
		gs.print("ERROR: " + ex);
	}
	return arLanes;
}