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

johndel · ‎12-07-2016

It still returns the whole records... a total of 22,000 records. I was expecting only to see 600 records. Ever since I tried using a addJoinQuery, I haven't succeeded. haha

drjohnchun · ‎12-07-2016

Also, you might just dot walk into the child tables instead of joining all of them, as in the example I provided above, copied below:

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

gr.addQuery('sc_item_option.value','item_value');

johndel · ‎12-07-2016

Thanks John! I tried this and worked to perfection. I got the number of records that I am hoping for. But still, I haven't succeeded when using chained addJoinQuery().

var catalogItemName = 'DevTools';

// retrieve ONLY the COMPLETED RITMs

var grCompletedRitms = new GlideRecord('sc_req_item');

grCompletedRitms.addActiveQuery();

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

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

grCompletedRitms.addQuery('quantity', 1);

grCompletedRitms.addQuery('state', 3);

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

grOwnerships.addCondition('sc_item_option.value', '303');

grOwnerships.addCondition('sc_item_option.item_option_new.name', 'application_profile');

/*

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

var count = grCompletedRitms.getRowCount();

gs.print(count);

drjohnchun · ‎12-07-2016

Also, did you want 'value != 303'? Your join condition says

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

instead.

johndel · ‎12-07-2016

In that case, I am trying to get the records that I don't want to be excluded in my final resultset.