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

[Andrew Wortham]

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

[Wesley Breshear]

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

[SanjivMeher]

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;

[Andrew Wortham]

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