How to query the value of a variable using a GlideRecord

[johndel]

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.

[mmongeau]

It appears that is not supported (tested in Helsinki patch 4).   Your only option may be to check for the variable value in the query loop.

while ( gr.grCompletedRitms.next() ) {
         if (variables.application_profile != '303') {
                   // do other logic
         }
}

[johndel]

Hoping to get the 600 records, I was trying this just now and couldn't make it work.

// retrieve ONLY the COMPLETED RITMs

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);

var grOwnerships = grCompletedRitms.addJoinQuery('sc_item_option_mtom', 'sys_id', 'request_item');

var grOptions = grOwnerships.addJoinQuery('sc_item_option', 'sc_item_option', 'sys_id');

grOptions.addCondition('value', '303');

var grVariables = grOptions.addJoinQuery('item_option_new', 'item_option_new', 'sys_id');

grVariables.addCondition('name', 'application_profile');

grCompletedRitms.query();

[drjohnchun · Accepted solution]

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

[johndel]

Hi John, thanks for this. Could you please also let me know what is wrong in my code (my response to Micheal Mongeau)? I tried using multiple addJoinQuery, but failed.

[drjohnchun]

What was the issue? I just eyeballed your query all the way through, but didn't find anything obvious; all the join statements as well as the keys and conditions look correct. Did you try joining and running one by one and see where it fails? What do you get for .getRowCount() at each step and are they about right?