question addQuery and addEncodedQuery queries not working correctly. in Developer forum

addQuery and addEncodedQuery queries not working correctly.

Wesley Breshear — Thu, 28 Dec 2017 21:17:22 GMT

Hello,

I am having problems getting my addQuery or addEncodedQuery statements work correctly. I am trying to provide a summary of answered options/variables from the Request Item catalog form. But I want to remove all the 'null', false, undefined, [blank], along with some specific options/variables from being listed out in the Task 'Description' field. I created queries using the ServiceNow list query builder, so hopefully, they are correct. I found one article saying to have addEncodedQuery before addQuery but didn't seem to help much. Can you assist me my code or logic in how I am trying filter out unwanted catalog options/variables?

var fltr1 = "item_option_newNOT LIKEMembers^ORitem_option_new=NULL^item_option_newNOT LIKEINCORRECT^ORitem_option_new=NULL"; // Filter 'item_option_new' of "Members" and "INCORRECT"

var fltr2 = 'value!=false^ORvalue=NULL^value!=undefined^ORvalue=NULL'; // Filter 'value' for "false" and "undefined"

var gr = new GlideRecord('sc_req_item');

gr.addQuery('request', current.request.sys_id);

gr.query();

while(gr.next()) {

// Get Owned Variables for Requested Item and sort by Order

var ownvar = new GlideRecord('sc_item_option_mtom');

ownvar.addEncodedQuery(fltr1); // Breaks command task.description = items; from working

//ownvar.addEncodedQuery(fltr2); // Breaks command task.description = items; from working

ownvar.addQuery('request_item.number', gr.number);

//ownvar.addQuery('sc_item_option.value','!=',''); // Doesn't seem to filter anything

ownvar.addQuery('sc_item_option.value','!=','false'); // Removes all 'false' options/variables (blank) - works

ownvar.addQuery('sc_item_option.value','!=','undefined'); // Must have this line to work, not sure why, but when REMARKED out it breaks? -works

//ownvar.addQuery('sc_item_option.item_option_new.name','!*','Members'); // Doesn't work

//ownvar.addNotNullQuery('sc_item_option.value'); // Doesn't seem to filter anything but seems logical to use.

ownvar.orderBy('sc_item_option.order');

ownvar.query();

var items = "Summary of " + gr.number + ": " + gr.cat_item.getDisplayValue() + "\n\n";

while(ownvar.next()) {

var field = ownvar.sc_item_option.item_option_new;

var fieldValue = ownvar.sc_item_option.item_option_new.name;

// Print variable name

items += field.getDisplayValue() + ": " + gr.variables[fieldValue].getDisplayValue() + "\n";

}

task.description = items;

Thank you,

-Wesley

Re: addQuery and addEncodedQuery queries not working correctly.

Andrew Wortham — Wed, 03 Jan 2018 15:29:21 GMT

Hello Wesley,

There is a lot going on in this code.

For starters try this:

currently you are declaring your variable Items inside a while loop, and then trying to access it outside of that while loop. I would not expect this to ever work, encoded query or not. On line 1 declare

var items = '';

There are 3 layers outside code, while loop 1, while loop 2. You define items in while loop 1, add to it in while loop 2, then try to access it in the outside code.

Next you might want to consider cleaning up this code. You have a ton of queries that can all be lumped into a single encoded query string.

Does that help?

Best,

Andrew

Re: addQuery and addEncodedQuery queries not working correctly.

Wesley Breshear — Thu, 04 Jan 2018 23:48:46 GMT

Hi Andrew,

Yes, that is the goal to get the encoded query to have all the queries. But my encoded wasn't working. I am using the code from another community string and it works perfect but I am needing to filter out some additional fields (I am still learning JavaScript). I see your point about the VARs being inside the other loops but it seems fine and it is how the list of options gets created. I was hoping something would pop-out with my encoded queries not having the correct syntax.

I think that I figure out what I was doing wrong but still doing some testing to validate my changes. When I built the query it was using 'display value' and it looks using 'value' is working now. Also, do you know how I can reduce or clean up this code? All the addQuery's?

var fltr1 = "sc_item_option.item_option_new.nameNOT LIKEu_subscription_users"; // This seems to be working now, using value vs. display value

var gr = new GlideRecord('sc_req_item');

gr.addQuery('request', current.request.sys_id);

gr.query();

while(gr.next()) {

// Get Owned Variables for Requested Item and sort by Order

var ownvar = new GlideRecord('sc_item_option_mtom');

ownvar.addEncodedQuery(fltr1); // This seems to be working now

ownvar.addQuery('request_item.number', gr.number);

ownvar.addQuery('sc_item_option.value', '!=', ''); // Doesn't seem to filter anything

ownvar.addQuery('sc_item_option.value', '!=', 'false'); // Removes all 'false' variables (blank)

ownvar.addQuery('sc_item_option.value', '!=', 'undefined'); // Must have this line to work not sure why but when REMARKED out it breaks

ownvar.addNotNullQuery('sc_item_option.value'); // Not sure if this is needed or helping.

ownvar.orderBy('sc_item_option.order');

ownvar.query();

var items = "Summary of " + gr.number + ": " + gr.cat_item.getDisplayValue() + "\n\n";

while(ownvar.next()) {

var field = ownvar.sc_item_option.item_option_new;

var fieldValue = ownvar.sc_item_option.item_option_new.name;

// Print variable name and option value

items += field.getDisplayValue() + ": " + gr.variables[fieldValue].getDisplayValue() + "\n";

}

task.description = items;

Thanks!

-Wesley

Re: addQuery and addEncodedQuery queries not working correctly.

SanjivMeher — Fri, 05 Jan 2018 00:42:10 GMT

Remove all queries and just use this

var gr = new GlideRecord('sc_req_item');

gr.addQuery('request', current.request.sys_id);

gr.query();

while(gr.next()) {

// Get Owned Variables for Requested Item and sort by Order

var ownvar = new GlideRecord('sc_item_option_mtom');

ownvar.addEncodedQuery('sc_item_option.valueISNOTEMPTY^sc_item_option.value!=false^sc_item_option.value!=undefined^request_item='+gr.sys_id+'^sc_item_option.sc_cat_item_option.nameNOT LIKEu_subscription_users');

ownvar.orderBy('sc_item_option.order');

ownvar.query();

var items = "Summary of " + gr.number + ": " + gr.cat_item.getDisplayValue() + "\n\n";

while(ownvar.next()) {

var field = ownvar.sc_item_option.item_option_new;

var fieldValue = ownvar.sc_item_option.item_option_new.name;

// Print variable name and option value

items += field.getDisplayValue() + ": " + gr.variables[fieldValue].getDisplayValue() + "\n";

}

task.description = items;

Re: addQuery and addEncodedQuery queries not working correctly.

paulmorris — Fri, 05 Jan 2018 01:39:44 GMT

Please see below for how to troubleshoot and successfully build encoded queries:

Look at the Dictionary Entry for your table to get the the field labels and names
Build the filter in ServiceNow
Modify the filter until it returns the result set you want
Copy the query from the filter breadcrumb
Copy it into your code (example)

var encQry = 'sc_item_option.valueISNOTEMPTY^sc_item_option.value!=false^sc_item_option.value!=undefined^request_item.cat_item=0241d1b2db4e4700821a3e5c7c9619b8';

gr.addEncodedQuery(encQry );

Re: addQuery and addEncodedQuery queries not working correctly.

Andrew Wortham — Fri, 05 Jan 2018 19:16:28 GMT

Hello Wesley,

+1 to what Paul said, build out the query see the resulting records, and then when correct "copy query"

Is the issue you are running into an actual error? Or are you just not getting any results back?

When you "addEncodedQuery" just like when you "addQuery" it is going to add an and clause to the GlideRecord this means that you encoded query and every other query has to return true in order to be returned. Sometimes you will end up conflicting queries when you have such a complex GlideRecord, which is why I tend to prefer a single encoded query.

Best,

Andrew