Report on data stored in sc_req_item.variables[sets[x]]

[Jason_DaSilva]

This is yet another issue that I have inherited from a long gone contractor.  We have a Catalog Item used to track PPE equipment requests.  Unfortunately the data is stored in 2 places.  First, in the above location specified in the Subject line.  I have tested and scripted to eventually be able to see the data:

Spoiler

var rGR  = new GlideRecord('sc_req_item');

//sample test query of an RITM with approved PPE data ('numberSTARTSWITHRITM0208637')
rGR.addEncodedQuery("cat_item=d1ee3f95db43a34061a31be31596194b");
rGR.orderByDesc('sys_created_on'); 
rGR.setLimit(1);		//for testing
rGR.query();

var ritms = [];					//an array to hold the RITM sys_ids

while (rGR.next()){
  ritms.push(rGR.sys_id.toString());
}

var sets = [];
var oGRCVS = new GlideRecord("io_set_item");		//table of Catalog Item Variable sets
oGRCVS.addQuery('sc_cat_item', 'd1ee3f95db43a34061a31be31596194b');
oGRCVS.orderBy('variable_set');
oGRCVS.query();
while (oGRCVS.next()) {
  sets.push(oGRCVS.variable_set.internal_name);
	//gs.info('variable sets names : ' + oGRCVS.variable_set.internal_name);
}
//dFields are the fields in the Variable Set equipments_details
var dFields = ["approve_or_reject", "item_code", "equipment_name", "quantity", "size_type", "can_price", "wbs", "equipment_type", "billable_non_billable", "project_no", "logo", "us_price", "model", "u_item_details"]

for (var i=0; i<ritms.length ;i++){
  var req_item = new GlideRecord('sc_req_item');
  if (req_item.get(ritms[i])){
    for (var s=0; s<sets.length; s++){
      var rowcount = req_item.variables[sets[s]].getRowCount();
      for (var j=0; j<rowcount; j++) {
        //var row = req_item.variables[sets[s]].getRow(j);
        var row = req_item.variables.equipments_details.getRow(j);
        gs.info ("RITM SYSID :" + ritms[i] + '  --  ' + sets[s]);
       	gs.info ('Equipment item # ' + (j+1) + ' : ' + row);
       	//gs.info (dFields[4] + ' : ' + row[dFields[4]]);
        gs.info('--------------------EOF----------------------');
      }
    }
  }

}

The other place is an attached CSV file that is generated from the same data in the variable set.

Years later the new owner of this data needs to create reports on this data.  They need to be able to look back at a specific month and see what type of PPE was ordered and the quantity.  I have a filter to get the RITMs needed (based on key text in the short desc.), but how can I get access to the other data?  I have tried to dot walk, but I cannot get to variable sets when doing a report from the sc_req_item table...