How to get value of Item variable while quering Requested Items?

[hyperjam]

In job sheduler i have the following script. I'm querying users from custom table and checking whether they requested item with sys_id =d167ddab5109060018a2e46a281a7158, and if they do, then i want to get value they choose in one of the item variables[month_report_period].

var ga = new GlideRecord('u_user_cost_of_income');

ga.addEncodedQuery('u_employee.active=true^u_employee.locked_out=false')

ga.query();

if(ga.next())

{

employee_sysid = ga.u_employee.sys_id;

    var ge = new GlideRecord('sc_req_item');

    var encodedquery ="u_opened_on_behalf_of.sys_id="+employee_sysid+"^cat_item=d167ddab5109060018a2e46a281a7158";    

    ge. addEncodedQuery(encodedquery);

    ge.query();

    if(ge.next()){

    gs.print(ge.u_opened_on_behalf_of.sys_id+" RITM: "+ ge.number+" Period: "+ge.variables.month_report_period);

  }

}

I'm running this script in background to see results, and for now i get only: undefined in the place where particular value selected by user should be.

[drjohnchun · Accepted solution]

Hi Anna - you can't dot walk into variables, so your "ge.variables.month_report_period" wouldn't work.

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

Winner of November 2016 Members' Choice Award

[drjohnchun]

can you just try

gs.print(ge.u_opened_on_behalf_of.sys_id+" RITM: "+ ge.number);

and see if it works first?

[hyperjam]

yes this works

[drjohnchun]

That's a good sign. Do you know how to fix the rest?