Multiple GR queries or addEncoded query?

[Andrew9]

I'm running a query to get an RITM based on variables from the Catalog Item. I found this really useful post from @drjohnchun (https://community.servicenow.com/community?id=community_question&sys_id=d4240329dbd8dbc01dcaf3231f96...) about querying the Variable Ownership table and my code works for one variable:

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','first_name_sml_vs');
gr.addQuery('sc_item_option.value','Roberto');
gr.query();
gs.info("Results returned = "+gr.getRowCount());

But I could easily have more than one "Roberto", I want a triple check of First Name, Last Name and Manager from sys_user, but when I introduce these as more "addQuery" it doesn't work and understandably so, I guess i'm effectively overwriting my parameters, but I equally can't figure out how to set it up as an Encoded Query. There are Service Now docs for getting variables for reporting purposes, but I can't figure out how to build an Encoded Query out of the same path. In Reports it goes from the req_item Table -> Column -> [Variables+] -> Catalog Item -> list of Variables.

Can you kind people please advise on what i'm either doing wrong with my original addQuery('s) or how I can build an AddEncodedQuery up like in the reports application?

Thanks for your help, I will make sure to 'Mark as Correct' the best answer and flag as 'Helpful' any other useful posts.

[Dubz]

Have you tried opening that table in the list view and building the query you need with the condition builder? You can then right click on the breadcrumbs and copy the query, that's often the best way to get what you need.

[Andrew9]

Almost, I can do sc_item_option_mtom.list to get a list of RITMs and I can do sc_item_option.list to see values, as per the "gr.addQuery('sc_item_option.value','Roberto')" query, but I want to get to "sc_item_option.item_option_new.name" to build my query from the variable names, the Questions available from the sc_item_option list are too generic because I have loads of "First Name" questions but I'm not able to drill down to get the proper name e.g. first_name_sml_vs

I struggle with navigation in Service Now sometimes, this is one of those times, is there something simple that I'm missing where I can easily get to "sc_item_option.item_option_new.name" from "sc_item_option_mtom.list"?

[Brad Bowman · Accepted solution]

Hi Andrew,

You mentioned wanting to check First Name, Last Name, and Manager from sys_user, but is that really what you're doing or will you hard-code all three values against 3 text variables, such as you have done with Roberto?  If you're ultimately trying to build a script that will return RITMs that have three variables populated a certain way you need to query mtom 3 times, so you'd have your first query, then for all the records returned, query on your second variable, and so on until the final records returned will be the RITMs, so something like this - pushing the results into an array since it has to cycle through them all.

var answer = [];
var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','first_name_sml_vs');
gr.addQuery('sc_item_option.value','Roberto');
gr.query();
while (gr.next()){ //for every Roberto, check the last name 
 var gr2 = new GlideRecord('sc_item_option_mtom');
 gr2.addQuery('sc_item_option.item_option_new.name','last_name_sml_vs'); //your last name variable
 gr2.addQuery('sc_item_option.value','Smith');
 gr2.query();
 while (gr2.next()){ // for every Roberto Smith, check the manager name
  var g3 = new GlideRecord('sc_item_option_mtom');
  gr3.addQuery('sc_item_option.item_option_new.name','manager_name_sml_vs'); //your manager name variable
  gr3.addQuery('sc_item_option.value','Michael Scott');
  gr3.query();
  while (gr3.next()){
   answer.push(gr3.request_item.number); //add each RITM number that matches all 3 variables to an array
  }
 }
}
answer = answer.join(','); // this will give you a comma-separated list of RITM numbers

I haven't tried a mock-up of this yet in my instance, but it sounds like it should work.  Let me know if I've not interpreted your requirements/use case correctly.

[Andrew9]

You nailed what I'm trying to do and I like your solution, there was a missing "r" in your definition of the gr3 variable but other than that the query looks to be running, but as the query goes on the data seems to start disappearing and when I try to unpack the "answer" the data is missing:

var answer = [];
var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','first_name_sml_vs');
gr.addQuery('sc_item_option.value','Roberto');
gr.query();
gs.info("query 1: "+gr.getRowCount());
while (gr.next()){ //for every Roberto, check the last name 
 var gr2 = new GlideRecord('sc_item_option_mtom');
 gr2.addQuery('sc_item_option.item_option_new.name','last_name_sml_vs'); //your last name variable
 gr2.addQuery('sc_item_option.value','Firmino');
 gr2.query();
gs.info("query 2: "+gr2.getRowCount());
 while (gr2.next()){ // for every Roberto Smith, check the manager name
  var gr3 = new GlideRecord('sc_item_option_mtom');
  gr3.addQuery('sc_item_option.item_option_new.name','line_manager_sml_vs'); //your manager name variable
  gr3.addQuery('sc_item_option.value','Jurgen Klopp');
  gr3.query();
gs.info("query 3: "+gr3.getRowCount());
  while (gr3.next()){
   answer.push(gr3.request_item.number); //add each RITM number that matches all 3 variables to an array
  }
 }
}
answer = answer.join(','); // this will give you a comma-separated list of RITM numbers
//for ( var i=0; i < answer.length; i++){   
//gs.info('RITM :' + answer[i]);   
//} 
gs.info("Answer = "+answer.length);

This is currently returning:

x_scoped_app: query 1: 2
x_scoped_app: query 2: 2
x_scoped_app: query 3: 0
x_scoped_app: query 3: 0
x_scoped_app: query 2: 2
x_scoped_app: query 3: 0
x_scoped_app: query 3: 0
x_scoped_app: Answer = 0

And to clarify I'm only using mock names for the ease of running this in Background Scripts, the code will be dynamically populated eventually. Thanks for your help.