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

RAMON SANTANA · ‎06-29-2018

Hello Mr. Chun,

Hope this finds you well, I am trying to pass or copy a price(currency type) value from sc_req_item to a cost(currency type) of a proc_po_item. I have tried a script on a business rule from Procurement (Total Cost) something like,

updateTotal();

function updateTotal() {
/////////////START TEST///////////////
var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_req_item');    //Passing source table name
gr.query();

while(gr.next()) {
     var gr1 = new GlideRecord('proc_po_item');   //Passing destination table name
     gr1.initialize();
     gr1.cost = gr.sc_req_item.price;   //Copying price field
     current.cost = gr1.cost;   //Copying cost value to current.cost
     current.total_cost = (current.cost * current.ordered_quantity);
     if (current.list_price <= 0)
         current.list_price = current.cost;
     current.total_list_price = (current.list_price * current.ordered_quantity);
     gr1.insert();
}
}
/////////////END TEST///////////////

/* Start Default
  current.total_cost = (current.cost * current.ordered_quantity);
  if (current.list_price <= 0)
   current.list_price = current.cost;
  current.total_list_price = (current.list_price * current.ordered_quantity);
}
End Default */

Not working, can I have some help here?