A relatively common scenario in ServiceNow is to have to use the results from one query to filter conditions for another query.  This technique is commonly referred to as using a subquery. In general the scenario will be something like this:

• You need a set of information from a table.
• That information should be filtered based on information from another table that is related…but the relationship/reference that connects them is pointing the wrong direction to use dot-walking.
• You do a query of the first table, loop through and assemble the attributes you want to use as additional filtering (in an array typically).
• Use the results as part of the conditions on the query against another table.

Something like this:

var active_inc_array = []; 
var IncGr = new GlideRecord("incident"); 
IncGr.addEncodedQuery('active=true^problem_idISNOTEMPTY'); 
IncGr.query(); 
while(IncGr.next()) { 
     active_inc_array.push(IncGr.problem_id + ""); 
} 
var ProbGr = new GlideRecord("problem"); 
ProbGr.addQuery("active","false"); 
ProbGr.addQuery("sys_id", "IN", active_inc_array.join()); 
ProbGr.query();

In the above we ultimately want information from the problem table, however, we want to use incident information to filter. To achieve this we first query the incident table for all active incidents that reference a problem record, create an array of the problem record sys_id’s then use that problem sys_id array as filtering against the problem table along with the active=false.  The idea here being we probably shouldn’t have active incidents if the problem the incident is related too is not active.

While the above works fine, there is a better, more performant, cleaner way of accomplishing the same result. That way is by using a join on you GlideRecord query. The word join is a bit misleading as it is not really a join as you might have experienced when building/using a database view.  The join is really a subquery as it helps you filter results from one table based on data in another table BUT you can NOT access data in that other table as part of the record set returned from your GliderRecord call.

Lets take a look at our previous example and then look at how we can do it better. First, our ‘brute force’ method again:

Brute Force

var active_inc_array = [];
var IncGr = new GlideRecord("incident");
IncGr.addEncodedQuery('active=true^problem_idISNOTEMPTY'); 
IncGr.query();
while(IncGr.next()) {
     active_inc_array.push(IncGr.problem_id + "");
}
var ProbGr = new GlideRecord("problem");
ProbGr.addQuery("active","false");
ProbGr.addQuery("sys_id", "IN", active_inc_array.join());
ProbGr.query();

To do this cleaner/better we can use the join functionality that is part of GlideRecord. GlideRecord presents a couple of ways to use join functionality.  First is through the use of the addJoinQuery() function. The addJoinQuery() function takes a table name as it’s parameter. In our case ‘incident’. Take a look at our use case implemented using addJoinQuery():

GlideRecord.addJoinQuery()

var ProbGr = new GlideRecord('problem');
var IncJoin = ProbGr.addJoinQuery('incident');
ProbGr.addQuery('active', 'false');
IncJoin.addCondition('active', 'true');
ProbGr.query();

Note a couple of things:

• You must assign the result of the addJoinQuery() to a variable as you then need to reference that variable later for the condition.
• There has to be a reference on the subquery table (in our example incident, through the field problem_id) to the ‘main’ table (in our example problem).

The above works but also has a bit of black box ‘magic’ involved as you don’t explicitly get to define how the two tables are connected/’joined’. To remove that ambiguity you can also do the same using an encoded query string:

Encoded Query

var query = "active=false^JOINproblem.sys_id=incident.problem_id!active=true"
var ProbGr = new GlideRecord('problem'); 
ProbGr.addEncodedQuery(query); 
ProbGr.query();

Lets take a look at the encoded query string in line 1 a bit closer as that is where the fun is:) Deconstructing the string:

• active=false: conditions on the target table, in our case problem
• JOINproblem.sys_id=incident.problem_id:  explicitly definition of the criteria that connects records from the target table (problem) to the subquery table (incident) using the JOIN keyword
• !active=true: condition on the subquery table, in our case incident, following the “!” delimter

The one limitation that we have run in to is that the condition on the subquery table only allows 1 condition to be applied (in our case active=true).

The above example is obviously a bit trivial but for larger data sets I’ve seen a noticeable difference in performance between the brute force method and using GlideRecord JOINs. Given how often I have run in to the need to use a subquery, I’ve found the usage of the GlideRecord JOIN functionality a much more efficient alternative to have in my tool box. Hopefully you will as well!