Filter catalog item variable by variable on another field?

[JP-ODU]

I'm trying to update the Report an Issue record producer with variables that are reference fields to two custom tables: u_university_buildings and u_university_rooms.

The field "bldgcode" on u_university_rooms is a reference to u_university_buildings.

Once a user has selected a Building variable from u_university_buildings, how can I filter the selections in the Room variable to correspond only to entries in u_unviersity_rooms matching that bldgcode?

I thought it would be a simple reference qualifier, but that seems to not be the case... Is it an advanced reference qualifier? A catalog client script? Any help in getting this scripted would be greatly appreciated.

Thanks!

[Mohith Devatte · Accepted solution]

Hello @JP-ODU ,

You can achieve it through advanced reference qualifier by calling a script include in it.

javascript:new your_Script_include_name().your functionname();

this is the general sysntax 

Follow below for your requirement

javascript:new getRooms().rooms(current.variables.buidling_variable_backend_name);

Create a script include which is a non client callable one (dont check the client callable check box) and try to code like below

Make sure your  script include name is getRooms and your function name is rooms

var getRooms = Class.create();
getRooms.prototype = {
    initialize: function() {
    },
	rooms:function(building)
	{
		var rooms =[];
	var gr = new GlideRecord('your room table');
		gr.addQuery('bigcode field name',building);
		gr.query();
		while(gr.next())
			{
				rooms.push(gr.sys_id.toString());
			}
		return "sys_idIN"+rooms;
	},

    type: 'getRooms'
};

Please mark my answer correct if it helps you

Let me know in case of any doubts. Happy to help!

[Mohith Devatte]

@JP-ODU  thanks for marking my answer helpful

Can you please mark my answer as correct so that it will be helpful for future readers with same query.

[JP-ODU]

If you could please help me struggle through a little more, I tried adapting your recommendation, but don't seem to have it working, yet.

This is my version of the script include:

var getRooms = Class.create();
getRooms.prototype = {
	initialize: function() {
	},
	rooms:function(building)
	{
		var rooms =[];
	var gr = new GlideRecord('u_university_rooms');
		gr.addQuery('bldgcode',building);
		gr.query();
		while(gr.next())
			{
				rooms.push(gr.sys_id.toString());
			}
		return "sys_idIN"+rooms;
	},
	
	type: 'getRooms'
};

And this is how I tried to call it in the advanced reference qualifier: javascript:new getRooms().rooms(current.variables.buidling_bldgcode);

But it doesn't seem to working? I'd expect a given building to only have one room 0100, for example, but I'm getting several of those for rooms with a building selected? What have I done wrong?

[Mohith Devatte]

hello @JP-ODU ,

can you return like the array like below

return "sys_idIN"+rooms.toString();

please try this and let me know