Related Query

The other day while working on the blog article The Ultimate Field Type Guide I was exploring configuration options for the Conditions field type. While browsing through ServiceNow's out of the box usages of this field type I saw something named "Related List Conditions". A filter mechanism for related tables which is only available when setting the dictionary attribute: allow_related_list_query=true.

I started to play around with this control, saved the form and checked the actual value stored in the database. The result was an encoded query string, different to what I have actually ever encountered before in ServiceNow. It looked like this:

^RLQUERYtask.assignment_group,>=1^active=true^ENDRLQUERY

Syntax

After figuring this out I wanted to know if this kind of queries can also be used when building regular GlideRecords, so I started to investigate a bit further. I have tried several scenarios and here is how it works. The general syntax is:

<query>^RLQUERY<child_table>.<reference_field>,<quantifier>^<related_query>^ENDRLQUERY^<query>

Here is a real live example:

var incidents = new GlideRecord('incident');
incidents.addEncodedQuery('^RLQUERYtask_sla.task,>=1^has_breached=true^ENDRLQUERY');
incidents.query();

This query translates to:

Give me all incidents which have at least 1 referencing breached Task SLA.

Filtering

If you want to further filter the incidents of our example you can either do this through the regular  addQuery() method or by attaching the encoded query before or after the related query:

// Attaching the encoded query BEFORE
incidents.addEncodedQuery('short_descriptionLIKEexample^RLQUERYtask_sla.task,>=1^has_breached=true^ENDRLQUERY');

// Attaching the encoded query AFTER
incidents.addEncodedQuery('^RLQUERYtask_sla.task,>=1^has_breached=true^ENDRLQUERY^short_descriptionLIKEexample');

Both of the above queries are equivalent and translate to:

Give me all incidents whose short descriptions contain "example" and which have at least 1 referencing breached Task SLA.

Quantifiers

When it comes to the   <quantifier>  part of the syntax mentioned above, there are several options available:

// More than 1
incidents.addEncodedQuery('^RLQUERYtask_sla.task,>1^ENDRLQUERY');

// Equals or more than 1
incidents.addEncodedQuery('^RLQUERYtask_sla.task,>=1^ENDRLQUERY');

// Less than 1
incidents.addEncodedQuery('^RLQUERYtask_sla.task,<1^ENDRLQUERY');

// Equals or less than 1
incidents.addEncodedQuery('^RLQUERYtask_sla.task,<=1^ENDRLQUERY');

// Exactly 1
incidents.addEncodedQuery('^RLQUERYtask_sla.task,=1^ENDRLQUERY');

// Not 1
incidents.addEncodedQuery('^RLQUERYtask_sla.task,!=1^ENDRLQUERY');

// Between 1 and 3
incidents.addEncodedQuery('^RLQUERYtask_sla.task,BETWEEN1@3^ENDRLQUERY');