A reference qualifier restricting to variable sets within a selected catalog item

[Chris Macdonald]

Hi all,

I have a simple reference field allowing the user to pick a catalog item from the sc_catalog_item table.  This reference field is named catalog_item.  There is a dynamic qualifier restricting the returned set of records, but that doesn't matter for the sake of my query.

I now have another reference field referring to the variable set table (item_option_new_set).  I would like to restrict the returned records in this field to only those variable sets included in the catalog item selected in the first field.

Eg.  I have selected my catalog item named 'Corporate Services' from the catalog item table.  Now I want to be able to select from only the variable sets included in 'Corporate Services'.

Thank you.

[Brad Bowman · Accepted solution]

Hi Chris,

Because the relationship of variable sets to Catalog Items is kept on the io_set_item table, your advanced reference qualifier would have to follow this format:

Where ritmUtils is the name of a Client callable Script Include, getVariableSets is the name of a function in that SI, and catalog_item is the name of your dependent reference variable.  The Script Include would look like this to query the relationship table and push the sys_ids of the variable sets to an array to be used by the reference qualifier:

var ritmUtils = Class.create();
ritmUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {
   	
	getVariableSets: function (cat_item) {
		var vsArr = [];
		var vs = new GlideRecord('io_set_item');
		vs.addQuery('sc_cat_item', cat_item);
		vs.query();
		while (vs.next()) {
			vsArr.push(vs.variable_set.toString());
		}
		return 'sys_idIN' + vsArr.join(',');
	},

    type: 'ritmUtils'
});

[Chris Macdonald]

Thanks so much, Brad. Worked perfectly.

[Brad Bowman]

You are welcome!