Utilizing the breadcrumb on lists to generate your query

Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

Hi there,

Experiencing struggles with setting up queries within your (Server Side) code? There's a nice and easy - out-of-the-box - way to start your basic queries!

What I will describe in this article is for sure not new. Though seeing the frequent Community questions about query help which could be helped with this, I thought let's write this down and provide some examples.

Some examples

- You want to set up a OR query within a GlideRecord. Though how. Maybe you're looking at the out-of-the-box Syntax Editor Macro "vargror" and are really puzzled about the code?!
- Want to do a date comparison on records on today? Or last week? Or more than 14 days ago? Pfff, how do we do that. Maybe adding some javascript, though where to start?!
- Querying on the user behind the keyboard. Should be possible to do this dynamically, though what's the code?!
- Want to check if a username contains a certain value. Know that it possible, though just forgot how to put this in an addQuery statement?!
- Or just after speeding up building your (basic) queries. There should be a way right?!
- Etc., etc., etc..

Utilizing the breadcrumb on lists to generate your query

Let's just jump right into it. On an out-of-the-box instance, just open the Incident list. Using the condition builder, just add the below filters and press Run:

1) Active is true AND Assignment group is Software

If you would right-click on the last piece of the breadcrumb, a context menu is displayed. Choosing the "Copy query", will copy below query to your clipboard:

active=true^assignment_group=287ebd7da9fe198100f92cc8d1d2154e

Just a basic query with an AND operator, not much more.

Though let's expand this.

2) OR Assignment group is Hardware

Choosing the "Copy query", will copy below query to your clipboard:

active=true^assignment_group=287ebd7da9fe198100f92cc8d1d2154e^ORassignment_group=8a5055c9c61122780043563ef53438e3

We've just added an OR statement to the query, that simple!

Let's expand the query further.

3) AND Priority is one of 1 - Critical, 2 - High

Choosing the "Copy query", will copy below query to your clipboard:

active=true^assignment_group=287ebd7da9fe198100f92cc8d1d2154e^ORassignment_group=8a5055c9c61122780043563ef53438e3^priorityIN1,2

Let's expand the query further.

4) AND Updated relative before 60 Minutes ago

Choosing the "Copy query", will copy below query to your clipboard:

active=true^assignment_group=287ebd7da9fe198100f92cc8d1d2154e^ORassignment_group=8a5055c9c61122780043563ef53438e3^priorityIN1,2^sys_updated_onRELATIVELT@minute@ago@60

Let's expand the query further.

5) AND Short description does not contain laptop

Choosing the "Copy query", will copy below query to your clipboard:

active=true^assignment_group=287ebd7da9fe198100f92cc8d1d2154e^ORassignment_group=8a5055c9c61122780043563ef53438e3^priorityIN1,2^sys_updated_onRELATIVELT@minute@ago@60^short_descriptionNOT LIKElaptop

Applying the copied query

Obviously we could go on and on and on with this. You will get the idea. So what now? We have the Query, how to use this?

For example, just open a Fix Script and generate the basic GlideRecord query code (could be done with help of the Syntax Macro Editor's).

This could be the basic code:

var gr = new GlideRecord('table_name');
gr.addQuery('field_name', 'field_value');
gr._query();

if(gr._next()) {
	//Your code goed here
}

One of the possibilities is to use an Encoded Query. This lets you use a full Query like what we are copying everything in the above examples!

Change your code slightly into: 

var queryStr = '';

var gr = new GlideRecord('table_name');
gr.addEncodedQuery(queryStr); 
gr._query();

while(gr._next()) {
	//Your code goes here
}

On the first line, I added a queryStr. You could paste your copied query within here. Copying our last query for example would make:

var queryStr = 'active=true^assignment_group=287ebd7da9fe198100f92cc8d1d2154e^ORassignment_group=8a5055c9c61122780043563ef53438e3^priorityIN1,2^sys_updated_onRELATIVELT@minute@ago@60^short_descriptionNOT LIKElaptop';

var grIncidentList = new GlideRecord('incident');
grIncidentList.addEncodedQuery(queryStr); 
grIncidentList._query();

while(grIncidentList._next()) {
	gs.info('--> Number: ' + grIncidentList.getDisplayValue());
}

Note: I also changed gr into something more describing, leaving gr in your code is really awful!!! Also added the incident table name and within the while loop a gs.info statement. And last but not least also used gs.info instead which absolutely should be preferred above using gs.log!!!

My fix script already returns me with these values:

*** Script: --> Number: INC0000002
*** Script: --> Number: INC0000003
*** Script: --> Number: INC0000016
*** Script: --> Number: INC0000049
*** Script: --> Number: INC0000050

Making your query more dynamic

Setting up queries might not always be enough. What if you are after an assignment group which you've also set in a System Property, let's say "my_company.first_network_group"? In this case, we would have to change the query manually. If you are a bit more experienced within scripting, this wouldn't be easy. Though, a short example on how to use a System Property within your query.

var queryStr = 'active=true^assignment_group=' + gs.getProperty('my_company.first_network_group') + '^ORassignment_group=8a5055c9c61122780043563ef53438e3^priorityIN1,2^sys_updated_onRELATIVELT@minute@ago@60^short_descriptionNOT LIKElaptop';

Wrap-up

Not that difficult, though a very nice good to know of these possibilities. Obviously, we could go on and on and on with building queries this way. Just give it a go, and make yourself comfortable with this.

---

And that's it actually! Not much to it. Just great to know this way of thinking and working, and letting the list condition builder do the work for you.

Kind regards,

Mark Roethof

ServiceNow Technical Consultant @ Paphos Group
---

LinkedIn