NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: INTERMEDIATE - ADVANCED
Assumes having taken the class SSNF and has very good intermediate to advanced level of knowledge and/or familiarity with Scripting in ServiceNow.
A few years ago in one of my ServiceNow Scripting classes a student brought up the following question:
- Can you have the following nesting with GlideRecords?
(this AND those) OR (which AND what)
This poses a problem for a normal GlideRecord in that everything is AND'ed together (even the OR's), and you don't really have any way to use parenthesis for order-of-precedence in your query. Especially, annoyingly, in your encoded queries! Playing around with it a bit I came up with a couple of different alternatives.
Here is the query in SQL that we will be trying to emulate:
SELECT COUNT(*) FROM incident
WHERE active='1'
AND ((sys_updated_on >= '20160101 00:00:00' AND sys_updated_on <= '20170101 23:59:59')
OR (sys_created_on >= '20171201 00:00:00' AND sys_created_on <= '20180820 23:59:59'));
Specifically note that the parenthesis for the above are interpreted as the following:
SELECT COUNT(*) FROM INCIDENT WHERE active='1'
AND (resolved first) OR (resolved second)
BASELINE QUERY
First lets get the actual number of records. We will fire two different queries that will give us the same total as the SELECT would have.
var sql1 = "active=true^sys_updated_on>=javascript:gs.dateGenerate('2023-01-01','00:00:00')";
sql1 += "^sys_updated_on<=javascript:gs.dateGenerate('2023-10-25','23:59:59')";
var incident1 = new GlideRecord('incident');
incident1.addEncodedQuery(sql1);
incident1.query();
var query1Total = incident1.getRowCount();
gs.info('---> count query 1a: ' + incident1.getRowCount());
var sql2 = "active=true^sys_updated_on>=javascript:gs.dateGenerate('2021-01-01','00:00:00')";
sql2 += "^sys_updated_on<=javascript:gs.dateGenerate('2023-01-01','23:59:59')";
var incident2 = new GlideRecord('incident');
incident2.addEncodedQuery(sql2);
incident2.query();
var query2Total = incident2.getRowCount();
gs.info('---> count query 1b: ' + incident2.getRowCount());
gs.info('---> BASELINE QUERY TOTAL: {0}', [query1Total + query2Total]);
After running the above query in Scripts - Background I got this result:
*** Script: ---> count query 1a: 27
*** Script: ---> count query 1b: 1
*** Script: ---> BASELINE QUERY TOTAL: 28.0
ENCODED QUERY SOLUTION
In theory we can simply merge the above query with an ^OR and give it a try. Crazily enough this does not work!
// I broke these out like this so that it was easier to comment them out while testing
var sql = 'active=true';
sql += "^sys_updated_on>=javascript:gs.dateGenerate('2023-01-01','00:00:00')";
sql += "^sys_updated_on<=javascript:gs.dateGenerate('2023-10-25','23:59:59')";
sql += "^ORsys_updated_on>=javascript:gs.dateGenerate('2021-01-01','00:00:00')";
sql += "^sys_updated_on<=javascript:gs.dateGenerate('2023-01-01','23:59:59')";
var incidentEncoded = new GlideRecord('incident');
incidentEncoded.addEncodedQuery(sql);
incidentEncoded.query();
gs.info('---> ENCODED QUERY TOTAL: ' + incidentEncoded.getRowCount());
Gave the results:
*** Script: ---> ENCODED QUERY TOTAL: 0
Zero?!? So missing a record. Yeah it doesn't get it right and won't. It worked it like this:
Updated >= 2023-01-01 AND (Updated < 2023-10-25 OR Update < 2021-01-01)
AND Updated > 2023-01-01
It should have returned something right? Yeah, garbage.
The encoded query:
active=true^sys_updated_on>=javascript:gs.dateGenerate('2023-01-01','00:00:00')
^sys_updated_on<=javascript:gs.dateGenerate('2023-10-25','23:59:59')
^ORsys_updated_on>=javascript:gs.dateGenerate('2021-01-01','00:00:00')
^sys_updated_on<=javascript:gs.dateGenerate('2023-01-01','23:59:59')
The actual query as it appears in the List View:
See the problem? Ends up looking like this:
active=true^sys_updated_on>=javascript:gs.dateGenerate('2023-01-01','00:00:00')
^sys_updated_on<=javascript:gs.dateGenerate('2023-01-01','23:59:59')
Returns nothing.
REGULAR QUERY SOLUTION
Now, transliterating the above encoded query into something that will work in a regular query wasn't as straightforward as I would have thought. The only solution that DID work is as follows:
var incident4 = new GlideRecord('incident');
incident4.addActiveQuery();
var incidentOr1 = incident4.addQuery('sys_updated_on', 'BETWEEN', '2023-01-01@2023-10-26')
.addOrCondition('sys_created_on', 'BETWEEN', '2021-01-01@2023-01-02');
incident4.query();
gs.info('---> REGULAR QUERY TOTAL: ' + incident4.getRowCount());
This solution is much more elegant, and easier to maintain. The BETWEEN statement was the kicker. No other scenario seemed to work! It too gave the same 27 + 1 records. Note that the top date for both betweens had to be +1 on the day. It probably would have been okay without doing that, but wanted to get as close as the date/time stamp in the encoded query.
I tried other variations on a theme, but was not able to get any of the other attempts to actually give me the 28 answer.
BTW, the "Regular" GlideRecord query solution, I first presented, may be the only way to segregate even more complex ANDing and ORing.
What we REALLY need are those parenthesis to be available in the encoded query!
Anyway, you are sure welcome to try to come up with other solutions, and I would be interested in what you find! 🙂
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 08-20-2018 12:36 PM
I updated the code and brought the article into alignment with my new formatting standard.
