Multiple GR queries or addEncoded query?

Andrew9 · ‎03-30-2020

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.

Brad Bowman · ‎03-30-2020

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.

View solution in original post

Brad Bowman · ‎03-30-2020

Do you have 2 RITMs with Roberto Firmino, and at least one of them having the manager Jurgen Klopp, and is that manager variable have the type of single line text like the other 2?

Andrew9 · ‎03-30-2020

I do have 2 RITMs with Roberto Firmino and only one them has the manager Jurgen Klopp and going through this I've found out that my gr3 query is failing. If I run it independently it returns 0 when it should return 1. Here is the code:

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

The Line Manager variable is from a list collector and I have previously had difficulty with this but was able to get around it when scripting in a Workflow with this code:

var staffDetails = new GlideRecord('sys_user');
staffDetails.addQuery('first_name', current.variables.first_name_sml_vs);
	staffDetails.addQuery('last_name', current.variables.last_name_sml_vs);
	staffDetails.addQuery('manager.name', current.variables.line_manager_sml_vs.getDisplayValue());

The above code works, I've tried adding the ".getDisplayValue()" code to my gr3 query but it's still not working. Any thoughts?

Andrew9 · ‎03-31-2020

I figured a way around this manager problem, instead of trying to convert the sys_id into a display name I can just use the sys_id, like this:

 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','paste sys_id in here');
  gr3.query();
gs.info("query 3: "+gr3.getRowCount());

Big thanks to @Brad Bowman couldn't have got there without your help, nice one!