addQuery and addEncodedQuery queries not working correctly.

[Wesley Breshear]

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

[The SN Nerd · Accepted solution]

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

1. Look at the Dictionary Entry for your table to get the the field labels and names
2. Build the filter in ServiceNow
   
3. Modify the filter until it returns the result set you want
4. Copy the query from the filter breadcrumb
   
5. 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 );