NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: ADVANCED
Assumes having taken the class SSNF and has good intermediate to advanced level of knowledge and/or familiarity with Scripting in ServiceNow.
PRE-REQUISITE: I developed this example using Scripts - Background and requires the installation of the GlideRecordExtensions Script Include.
I have wanted SQL BETWEEN capability for some time with GlideRecord. With the ability to extend the out-of-the-box object myself it then became a relative snap to implement this feature (albeit not quite the perfect way I would want, but good enough). When I originally envisioned this functionality GlideQuery didn't even exist. If you are interested in how ServiceNow implemented GlideQuery on top of GlideRecord it is using something similar to the method I describe here.
Requirements:
1. Extends the GlideRecord object as a new .addQuery type.
2. Takes two GlideDateTime objects as a from-date, and to-date.
3. Returns all records for any query between the given range for any given GlideDateTime field.
SQL Example:
SELECT * FROM incident WHERE sys_created_on BETWEEN '2022-10-09' AND '2023-10-09'
Desired GlideRecord Implementation:
<<GlideRecord>>.addBetweenQuery(<<String>>field, <<GlideDateTime>>fromDate, <<GlideDateTime>>toDate);
NOTE: My actual desired implementation would have been something like this:
<<GlideRecord>>.addQuery(<<String>>field, 'BETWEEN', <<GlideDateTime>>fromDate, <<GlideDateTime>>toDate);
But currently I don't want to have to sort through the partial overriding of .addQuery it would take. Maybe later! 🙂
Lab: Extending GlideRecord With New Between Function
1. Navigate to System Definition -> Script Include. The Script Include List View will be displayed.
2. Search for and edit GlideRecordExtensions.
You will see that the script is very straightforward; simple functionality actually.
GlideRecord.prototype.addBetweenQuery = function(fieldToCheck, beginDate, endDate) {
var beginDateCheck = gs.dateGenerate(beginDate); // convert to the appropriate string
var endDateCheck = gs.dateGenerate(endDate); // ibid.
this.addQuery(fieldToCheck, '>=', beginDateCheck);
this.addQuery(fieldToCheck, '<=', endDateCheck);
};
Lab: Testing the .addBetweenQuery functionality
1. Navigate to Scripts - Background.
2. Paste the following script into the text box:
gs.include('GlideRecordExtensions');
var beginDate = new GlideDateTime('2022-10-09');
var endDate = new GlideDateTime('2023-10-09');
// Retrieve all active records between the two dates inclusive!
var incidents = new GlideRecord('incident');
incidents.addBetweenQuery('sys_created_on', beginDate, endDate); //<--- Our new function!
incidents.addQuery('active', true);
incidents.query();
gs.info('---> Records found: ' + incidents.getRowCount());
// Convert our GlideRecord recordset to an object list for easier manipulation
var convertedList = incidents.toObjectList();
gs.info('---> Records converted: ' + convertedList.length);
for (var i=0; i < convertedList.length; i++) {
gs.info(convertedList[i].number + ' - ' + convertedList[i].sys_created_on);
}
e. Click on the Run script button.
3. Your results should look something like the following:
*** Script: ---> Records found: 2
*** Script: ---> Records converted: 2
*** Script: INC0010569 - 2023-02-27 15:33:40
*** Script: INC0010693 - 2023-06-03 04:28:25
BTW, you could derive this same kind of functionality by using the following built-in functionality .addEncodedQuery. It would look like this:
incidents.addEncodedQuery("active=true^sys_created_onBETWEENjavascript:gs.dateGenerate('2022-10-09','00:00:00')@javascript:gs.dateGenerate('2023-10-09','00:00:00')");
Or using Encoded Query best practices:
var sql = 'active=true' +
'^sys_created_onBETWEEN' +
"javascript:gs.dateGenerate('2022-10-09','00:00:00')" +
"@javascript:gs.dateGenerate('2023-10-09','00:00:00')";
incidents = new GlideRecord('incident');
incidents.addEncodedQuery(sql);
incidents.query();
gs.info('---> Records found: ' + incidents.getRowCount());
// Convert our GlideRecord recordset to an object list for easier manipulation
var convertedList = incidents.toObjectList();
gs.info('---> Records converted: ' + convertedList.length);
for (var i=0; i < convertedList.length; i++) {
gs.info(convertedList[i].number + ' - ' + convertedList[i].sys_created_on);
}
Which gives the same results:
*** Script: ---> Records found: 2
*** Script: ---> Records converted: 2
*** Script: INC0010569 - 2023-02-27 15:33:40
*** Script: INC0010693 - 2023-06-03 04:28:25
The encoded query would be difficult to read, and difficult to maintain. The addBetweenQuery is much easier to read!
My solution does not zero out the times, and you may want to implement that feature.
BTW, if you are interested in my comparison of GlideRecord with GlideQuery you can find those articles here:
GlideQuery vs. GlideRecord: A Comparison - Part 1
GlideQuery vs. GlideRecord: A Comparison - Part 2
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 10-11-2015 07:43 AM
I updated the code and brought the article into alignment with my new formatting standard.
