How to query the value of a variable using a GlideRecord

johndel · ‎12-07-2016

Hi Guys,

I have a catalog item with variables and variable sets in it. My catalog item, for example is named DevTools. I have a variable inside the variable set (which DevTools uses). It's name is application_profile. Below is my existing query:

var grCompletedRitms = new GlideRecord('sc_req_item');

grCompletedRitms.addActiveQuery();

grCompletedRitms.addQuery('approval', 'approved');

grCompletedRitms.addQuery('cat_item.name', 'DevTools');

grCompletedRitms.addQuery('quantity', 1);

grCompletedRitms.addQuery('state', 3);

grCompletedRitms.addQuery('variables.application_profile', '!=', '303');

grCompletedRitms.query();

My query doesn't seem to work. It still returns the rows including which the application_profile is 303. May I ask the correct way and the best practice of doing it? Thanks in advance.

drjohnchun · ‎12-07-2016

Hi Johndel,

Using GlideRecord to Query Tables 3.6 Querying Service Catalog Tables reads:

You cannot directly query the variables of the Service Catalog Request Item table [sc_req_item]. Instead, query the Variable Ownership table [sc_item_option_mtom] by adding two queries, one for the variable name and another for the value. The query returns the many-to-many relationship, which you can dot-walk to the requested item. The following example finds the request items that have the variable named 'item_name' with a value of 'item_value' and displays the request item numbers:

and provides this example:

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','item_name');
gr.addQuery('sc_item_option.value','item_value');
gr.query();

while (gr.next()) {
       gs.addInfoMessage(gr.request_item.number);
}

Hope this helps.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

View solution in original post

darius_koohmare · ‎12-07-2016

Did you miss the comma just in the copy paste or in the real code?

grCompletedRitms.addQuery('variables.application_profile', '!=', '303');

johndel · ‎12-07-2016

Thanks for the correction! But still, same result mate.

darius_koohmare · ‎12-07-2016

I knew it couldn't be that easy...

You may need to do a call on the variables in a separate GR.

For example:

var gr = new GlideRecord('sc_item_option_mtom');

gr.addQuery('sc_item_option.item_option_new.name','application_profile');

gr.addQuery('sc_item_option.value','!=','303');

gr.query();

The returned records would have a gr.request_item.number where the number is a requested item whose variable application profile does not have value 303.

johndel · ‎12-07-2016

So, it can't be dot-walked? I was thinking of using a .addJoinQuery() for variable ownerships, options, and variables table. But i was hoping if there's a way to do a dot-walk. Thanks.