Populate Multi Row Variable Set from Table

[John Shores1]

I have a custom table that contains items related to various types of supplies. It's a simple table containing fields for item category, item, description, quantity, etc.  I have a catalog item with a multi row variable set (mrvs). The variable set matches some of the fields in the custom table (e.g., item, description, qty). I need to populate the mrvs with all the data from the custom table that meets the query criteria when the catalog item loads. I've created the following onLoad() catalog client script, but it's not populating any records at all:

function onLoad() {

	// open new GlideRecord
	var gr = new GlideRecord('table_name');

	// create table query
	gr.addQuery('u_category','CONTAINS','search_term');

	// execute query
	gr.query();
	
	// create var to hold json
	var obj = [];
	
	// populate mvrvs records
	while (gr.next()) {
		var item = gr.getValue('u_item');
		var pkg_qty = gr.getValue('u_package_quantity');
		var max_qty = gr.getValue('u_max_qty_per_order');
		obj.push({
			"varItem": item,
			"varPkgQty": pkg_qty,
			"varMaxQty": max_qty
		});		
	// add record to varset
	g_form.setValue('mrvs_name', JSON.stringify(obj));	
	}	
}

Any ideas where this is going wrong?

[Mark Roethof]

Hi There,

Adding rows onLoad is certainly possible. I just tried something like below, works instantly. Though, you want the data retrieved from a GlideRecord? I would not recommend using GlideRecord within a Client Script. Better would to use GlideAjax for this.

To add rows onLoad, try this example:

function onLoad() {

  var daysOfWeek = [
    'Monday', 
    'Tuesday', 
    'Wednesday', 
    'Thursday', 
    'Friday', 
    'Saturday', 
    'Sunday'
  ];

  var obj = [];
  for (var i = 0; i < daysOfWeek.length; i++){
    obj.push({
      "field_name_1": daysOfWeek[i],
      "field_name_2": ""
    });
  }

  g_form.setValue("mrvs_name", JSON.stringify(obj));

}

Kind regards,
Mark

[Cody Smith _ Cl]

Hello John,

This could be because you're using the JSON.strigify on an object, but not looping through anything. A setValue is set by strings like `setValue(String fieldName, String value, String displayValue)` so you could try a loop with the obj, or you could set it all from inside your while loop instead of pushing outside of it first.

while (gr.next()) {
  var item = gr.getValue('u_item');
  var pkg_qty = gr.getValue('u_package_quantity');
  var max_qty = gr.getValue('u_max_qty_per_order');
	g_form.setValue(item, value);
}

If my answer was helpful, or solved your issue, please mark it as Helpful / Correct.

Thank you,

Cody Smith

[John Shores1]

Ok, I'm not getting anywhere with this at this point. 😞

I have a MRVS called cleaning_supplies. It has 4 columns (cp_item, cp_pkg_qty, cp_max_per_order, cp_qty). I want to populate this MRVS onLoad with data from a table. I have built the following script include to get the data:

var getOfficeSupplyList = Class.create();
getOfficeSupplyList.prototype = Object.extendsObject(AbstractAjaxProcessor, {
		
	getSupplyList: function() {
		// setup variables
		var query_param = this.getParameter('sysparm_query_param');
		var array = [];
		var result = '';
		var gr = new GlideRecord('table_name_here');
		
		// query table & filter records
		gr.addQuery('u_category','CONTAINS','query_param');		
		gr.query();
		while(gr.next()) {
			// push selected values into array
			array.push({
				"cp_item": gr.getValue('u_item'),
				"cp_pkg_qty": gr.getValue('u_package_quantity'),
				"cp_max_qty": gr.getValue('u_max_qty_per_office'),
				"cp_qty": '0'
			});
		}		
		// store result in JSON formatted string
		result = JSON.stringify(array); 
		return result;
	},
	type: 'getOfficeSupplyList'
});

If I run the basis of this code in Xplore, I can validate "result" contains the following JSON package:

[{"cp_item":"All Purpose Cleaner","cp_pkg_qty":"1 bottle","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Glass Cleaner","cp_pkg_qty":"1 bottle","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Toilet Bowl Cleaner","cp_pkg_qty":"1 bottle","cp_max_qty":"2","cp_qty":"0"},{"cp_item":"Disinfecting Wipes","cp_pkg_qty":"1 canister","cp_max_qty":"2","cp_qty":"0"},{"cp_item":"Trash Bags 10 gal.","cp_pkg_qty":"1000 trash bags","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Vinyl Gloves","cp_pkg_qty":"100 gloves","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Swiffer Dusters","cp_pkg_qty":"10 dusters","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Disinfecting Air Spray","cp_pkg_qty":"1 canister","cp_max_qty":"2","cp_qty":"0"},{"cp_item":"Trash Bags - 30 gal.","cp_pkg_qty":"250 trash bags","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Swiffer Wet Pads","cp_pkg_qty":"12 pads","cp_max_qty":"1","cp_qty":"0"},{"cp_item":"Ice Melt","cp_pkg_qty":"1 canister","cp_max_qty":"3","cp_qty":"0"}]

I've created an onLoad client catalog script and associated it with the variable set "cleaning_supplies":

function onLoad() {	
	// call script include
	var ga = new GlideAjax('getOfficeSupplyList');
	// function to call
	ga.addParam('sysparm_name','getSupplyList');
	ga.addParam('sysparm_query_param', 'Clean');
	ga.getXML(updateSupplyList);
}

function updateSupplyList(response) {
    
	var val = response.responseXML.documentElement.getAttribute("answer");	
	// set mrvs with retrieved values
	g_form.setValue('cleaning_supplies', val);
	
}

Nothing is being populated in the MRVS onLoad. No errors, no nothing.  I've tried associating the client script with the catalog item vs. the variable set with no difference in results.

Of note - the form is within a scoped app, the script include is in global and callable by clients.

What am I doing wrong???

[SatheeshKumar]

var item = gr.getValue('u_item').toString();

var pkg_qty = gr.getValue('u_package_quantity').toString();

var max_qty = gr.getValue('u_max_qty_per_order').toString();

can you add toString() and try once.

-satheesh