Adding Sets To Complicated GlideRecords

Recently I discovered a little trick with GlideRecords that would’ve made my life a lot easier with previous projects if I knew it existed.

As it stands, out of the box, the GlideRecord API does not support query sets. Consider the following example where you want to bring back the following:

All users whose name starts with A and are active

AND

All users whose name starts with B and are inactive.

So how can we accomplish this? Without further ado, let me explain…

It’s very easy to add an Or condition to one query condition, but not for sets. For example, you could do this:

var gr = new GlideRecord('sys_user');
var qc = gr.addQuery('name', 'STARTSWITH', 'a');
qc.addOrCondition('name', 'STARTSWITH', 'b');
gr.addQuery('active', true);

but this would return all names starting with A or B and which are active; not what we are looking for.

Or you could try:

var gr = new GlideRecord('sys_user');
var qcOne = gr.addQuery('name', 'STARTSWITH', 'a');
qcOne.addOrCondition('name', 'STARTSWITH', 'b');
var qcTwo = gr.addQuery('active', true);
qcTwo.addOrCondition('active', false);

but again this doesn’t bring back what we’re looking for.

So, how can we accomplish this? The answer is (until ServiceNow provides a proper API for it), is using encoded queries.

You can build both queries individually, and then combine them to get what we’re looking for as follows:

//This finds all users with a name starting with A and who are Active
var queryOne = new GlideRecord('sys_user');
queryOne.addQuery('name', 'STARTSWITH', 'a');
queryOne.addQuery('active', true);

//This finds all users with a name starting with B and who are Inactive
var queryTwo = new GlideRecord('sys_user');
queryTwo.addQuery('name', 'STARTSWITH', 'b');
queryTwo.addQuery('active', false);

//Get the encoded query for each of the queries and join them in the middle with '^NQ'. You can do this with as many sets as you require.
//My guess is that NQ stands for New Query....
var en = queryOne.getEncodedQuery() + '^NQ' + queryTwo.getEncodedQuery();

//Then build the actual gliderecord you want and add the encoded query
var gr = new GlideRecord('sys_user');
gr.addEncodedQuery(en);
gr.query();
while (gr.next()) {

//then do something useful with the results
gs.print('name: ' + gr.name + ' active: ' + gr.active);

}

A nice, simple and clean solution to those complicated and seemingly impossible queries we sometimes have to make. You won’t need to do numerous loops and joins etc.

Hopefully you’ll find this as useful as I have! Saved me from pulling out my hair on a number of occassions now!

2 Comments

  1. you should be able to do that with:

    var gr = new GlideRecord(‘sys_user’);
    var qc = gr.addQuery(‘name’, ‘STARTSWITH’, ‘a’);
    gc.addCondition(‘active’, true);

    var or = qc.addOrCondition(‘name’, ‘STARTSWITH’, ‘b’);
    or.addCondition(‘active’, false);

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s