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 advanced level of knowledge and/or familiarity with Scripting in ServiceNow.
So far in this series of articles I have covered what you might utilize concerning GlideRecords in your day-to-day ServiceNow Scripting development. Now, with this article, I will be jumping into a little deeper water.
What I will be covering:
- Moving the GlideRecord Location Pointer Around
- Encoded Queries - An Analysis
- The GlideRecord Object - An Analysis Exercise
Side note: I have tested the following code using Scripts - Background or Fix Scripts, and my personal instance. Your results may vary from mine in content.
Moving the GlideRecord Location Pointer Around
One of my major issues with the GlideRecord object is the lack of enumeration. You can't iterate through the records using the JavaScript "for (var i=0;..." construct. This is primarily, I think, due to the nature of retrieving each record on-demand.
Due to the nature of the object ServiceNow has provided a number of methods to help us restart a GlideRecord so that we don't have to re-fetch it from the database. This can be done from the beginning, or at any point we choose.
There may come a time where it will be necessary in your code to loop through a GlideRecord yet a second time OR jump back to a particular record in the GlideRecord for a re-examination of the data. Even though we don't have access to the under-the-hood enumeration for looping, we DO have access to the current row-number. Of course as with all things Java/JavaScript this is zero based. WARNING: There may be some scoping issues with this functionality so test, test, test!
The four GlideRecord methods I will be cover are:
- Get Location - .getLocation
- Save Location - .saveLocation
- Set Location - .setLocation
- Restore Location - .restoreLocation
Look carefully at the comments in the following code snippet to get the idea with what is happening with the Script.
var incidentRecords = new GlideRecord('incident');
incidentRecords.addActiveQuery();
incidentRecords.setLimit(10);
incidentRecords.orderBy('number');
incidentRecords.query();
// Loop through our glide record, and ONLY print when we reach record 5!
// This is important to note in that we can actually look at the under-the-hood
// enumeration, even though we can't normally use it!
// BTW, if you attemp to print off the first record prior to the first next
// you will get garbage. That is because at the beginning the pointer is pointng
// to nothing.
// print off all 10 records but make a special case of record 5.
while (incidentRecords.next()) {
if (incidentRecords.getLocation() == 5) {
gs.info('---> Found! location: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number]);
}
// we are printing off all 10 records as a reference for the rest of the script
gs.info('---> Incident number: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number]);
}
// print off the 10th one again; the pointer is still at the end.
gs.info('---> Current Incident number: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number]);
gs.info('---------------------------------------------------');
// It is important to note that the default restore location is -1. This allows
// us to easily reset to the beginning of the GlideRecord without having to
// repull it.
// Reset the pointer to beginning (before the first record)
gs.info('---> RESETTING POINTER TO THE BEGINNING!');
incidentRecords.restoreLocation();
// re-loop through the first 10 incident numbers starting with the first
var location = -1; // null/beginning pointer
while (incidentRecords.next()) {
// this time let's keep the location of record four for future use
if (incidentRecords.getLocation() == 4) {
gs.info('---> Found! location: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number + '']);
location = incidentRecords.getLocation();
incidentRecords.saveLocation(); // saves down the current location
}
}
// So where are we now? getLocation is your friend!
gs.info('---> Location: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number + '']);
// now jump back to record 4 - it does it, but it does not get the
// right incident number!!!! This is a flaw/bug in the restoreLocation function.
// instead the value is still the last value read.
incidentRecords.restoreLocation();
gs.info('---> Location: {0} - {1} <---bad number', [incidentRecords.getLocation(), incidentRecords.number + '']);
// It doesn't wake up to the correct value unless you do a .next()! Grrr.
// So move to record 5 to get a value other than the last one.
incidentRecords.restoreLocation();
incidentRecords.next();
gs.info('---> Location: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number + '']);
// now REALLY jump back to record 4 AND get the correct/expected value.
incidentRecords.setLocation(location);
incidentRecords.restoreLocation();
gs.info('---> Location: {0} - {1}', [incidentRecords.getLocation(), incidentRecords.number + '']);Encoded Queries - An Analysis
The best way to start building encoded queries is to grab them from the breadcrumbs of a List View. You simply create a query in a List View then right-click on the last entry on the breadcrumb and click on Copy Query from the context menu. You then paste it into your favorite script editor, and you are all set to incorporate it into your script. My intention here is not to teach how to create an encoded query, but rather, to show some techniques and best-practices when using them.
Side note: .addEncodedQuery is one of the few methods that actually will return an error, if one exists. Pretty much everything else associated with a GlideRecord eats the error. So a try/catch with an encoded query actually makes sense!
Link: Building Encoded Queries
Some useful tidbits:
- ^ - AND, also stands for "transition"
- ^OR - OR
- = - equals
- != - not-equals
- ^NQ - New Query - Start a new query and tack the results onto the old one (as close to a union as you will get)
- ^RLQUERY, ^ENDRLQUERY - Related List Query - dot-walk query for related fields
- javascript: - what follows the colen will be a mixed query/javascript script
- ^ORDERBY, ^CONTAINS, ^DOESNOTCONTAIN, ^BETWEEN, NOT LIKE, LIKE, etc. - The usual commands available to GlideRecords.
- Strings are not surrounded by double- or single-quotes! Don't make this mistake as it will error our.
Some Exercises
BTW, it is a best practice to separate out your query. Especially if it is a long one. Also break it out so that it is easy to understand and maintain!
A query written in the regular format
var incidentRecords = new GlideRecord('incident');
incidentRecords.addActiveQuery();
incidentRecords.addQuery('category','LIKE','inquiry');
incidentRecords.addQuery('priority','1');
incidentRecords.addQuery('state','1');
incidentRecords.addQuery('urgency','1');
incidentRecords.orderBy('number');
incidentRecords.setLimit(5);
incidentRecords.query();
while (incidentRecords.next()) {
gs.info('---> Number: {0}, {1}, {2}, {3}',
[incidentRecords.number, incidentRecords.priority, incidentRecords.state, incidentRecords.urgency]);
}
gs.info('-----------------------');
// same query written as an encoded query. Note the improvement of readability!
var sql = 'active=true'
+ '^categoryLIKEinquiry'
+ '^priority=1'
+ '^state=1'
+ '^urgency=1'
+ '^ORDERBYnumber';
var incidentRecords = new GlideRecord('incident');
incidentRecords.addEncodedQuery(sql);
incidentRecords.setLimit(5); // not allowed in the encoded query part, will be ignored if you put it there
incidentRecords.query();
while (incidentRecords.next()) {
gs.info('---> Number: {0}, {1}, {2}, {3}',
[incidentRecords.number, incidentRecords.priority, incidentRecords.state, incidentRecords.urgency]);
}MySQL SQL Equivalent:
SELECT * FROM incident
WHERE active=true
AND category LIKE 'inquiry%'
AND priority=1
AND state=1
AND urgency=1
LIMIT 5
ORDER BY number
Let's mix things up a bit and really get crazy!
// notice the elegance creaping into the code!
// this technique is much easier to read and maintain
var sql = 'active=true'
+ '^categoryNOT LIKEinquiry'
+ '^priority!=1'
+ '^state=3^ORstate=2'
+ '^urgencyIN1,2,3'
+ '^ORDERBYDESCnumber';
var incidentRecords = new GlideRecord('incident');
incidentRecords.addEncodedQuery(sql);
incidentRecords.addQuery('severity',1);
incidentRecords.setLimit(10);
incidentRecords.query();
while (incidentRecords.next()) {
gs.info('---> Number: {0}-{1}-{2}-{3}-{4}-{5}',
[incidentRecords.number,
incidentRecords.category,
incidentRecords.priority,
incidentRecords.state,
incidentRecords.urgency,
incidentRecords.severity]);
}MySQL SQL Equivalent:
SELECT * FROM incident
WHERE active=true
AND category NOT LIKE '%inquiry%'
AND priority != 1
AND (state=3 OR state=2)
AND urgency IN (1,2,3)
AND severity=1
LIMIT 10
ORDER BY DESC number
The GlideRecord Object - An Analysis Exercise
Now we will look in-depth at what is actually returned by the GlideRecord object. It is extensive, so just warning you!
Tables of interest
sys_db_object table - contains the table definitions
sys_dictionary table - contains the field definitions
Methods addressed
Link: GlideRecord API
- .getRecordClassName - get the class name for the current record (this will be the table name)
- .getClassDisplayValue() - returns the Label value of the class/table name.
- .getElement(column name) - useful to grab the field as an object to be worked with
- .getTableName() - returns the actual table name
- .getED() - retrieve the element descriptor - extremely useful!
- .field.getAttribute(attribute name) - retrieve the attribute (aka field info) object (list of attributes: link)
- .field.hasAttribute(attribute name) - is the field present in the element descriptor? (list of attributes: link)
Dumping the fields in a GlideRecord Object
First we will dump out the GlideRecord object returned from a simple Incident query. This method can be used with any table, and is a useful method to determine what exactly is contained in the object record (i.e. properties, values).
Note: In the following examples you will see me use bracket notation to address field via a variable name. Sometimes this is the ONLY way to get at a value in an object. This is a really useful alternative way to address object elements vs. dot-walking.
var incidentRecords = new GlideRecord('incident');
incidentRecords.addActiveQuery();
incidentRecords.setLimit(1);
incidentRecords.orderByDesc('number');
incidentRecords.query();
incidentRecords.next();
// print off the structure
for (var item in incidentRecords) {
gs.info('---> {0}: {1}',[item, incidentRecords[item]]);
}
gs.info('--------------------------------');
// filter out all the empty values - this will include defaulted fields like booleans
for (var item in incidentRecords) {
if (JSUtil.notNil(incidentRecords[item])) {
gs.info('---> {0}: {1}',[item, incidentRecords[item]]);
}
}
Using getED
An alternative method is to use getED. This is an alternative way to looking at all of the field values, but it is not as intuitive and easy to maintain.
var incidentRecords = new GlideRecord('incident');
incidentRecords.addActiveQuery();
incidentRecords.setLimit(1);
incidentRecords.orderByDesc('number');
incidentRecords.query();
incidentRecords.next();
// pull a list of fields using GlideRecordUtil
var glideRecordUtil = new GlideRecordUtil();
var incidentFields = glideRecordUtil.getFields(incidentRecords);
gs.info(incidentFields);
var fields = incidentRecords.getFields();
// now let's actually use getED (with fields) using some of the methods we know about!
for (var i=0; i < fields.size(); i++) {
try {
var element = fields.get(i);
gs.info('---> fields. {0}:\t{1}', [element.getName(), element]);
}
catch(err) {
// bypass illegal getter/setter grabs; if any
gs.error('---> {0}: {1}',[field, err]);
}
}
Retrieving the Table/Class Name
Now, let's get the class name, via several different ways, with the same query. This is useful to determine what table we are working with. Remember, we can feed a GlideRecord a table name via a variable.
var incidentRecords = new GlideRecord('incident');
incidentRecords.addActiveQuery();
incidentRecords.setLimit(1);
incidentRecords.orderByDesc('number');
incidentRecords.query();
incidentRecords.next();
gs.info('---> Table name: ' + incidentRecords.getTableName());
gs.info('---> Class name: ' + incidentRecords.getValue('sys_class_name'));
gs.info('---> Class name(2): ' + incidentRecords.getClassDisplayValue());
gs.info('--------------------------------');
// the record also contains what table it is contained in. This field should pretty much
// be ubiquitous. If not, you have the other methods available.
for (var item in incidentRecords) {
if (item == 'sys_class_name') {
gs.info('---> {0}: {1}',[item, incidentRecords[item]]);
}
}
Dump all ED Methods
getED allows us to retrieve information on a field, but did you know it also has a ton of under-the-hood functionality as well? However, not all of it is exposed for us to use. You can get a glimpse of that through the following query:
var incidentRecords = new GlideRecord('incident');
incidentRecords.addActiveQuery();
incidentRecords.setLimit(1);
incidentRecords.orderByDesc('number');
incidentRecords.query();
incidentRecords.next();
var incidentED = incidentRecords.getED();
// this method dumps EVERY function in ED. This makes for interesting reading!
for (var item in incidentED) {
try {
gs.info('---> {0}: {1}',[item, incidentED[item]]);
}
catch(err) {
// bypass illegal getter/setter grabs
gs.error('---> {0}: {1}',[item, err]);
}
}Alternative Method for Grabbing a Field's Attribute List
The sys_dictionary table is an excellent resource for a particular field's info. I tend to use it directly rather than running around looking for the exact method call to retrieve the list of, lets say, attributes!
// get the table attributes from the table collection field
var incidentDefinition = new GlideRecord('sys_dictionary');
incidentDefinition.addQuery('name','incident');
incidentDefinition.addQuery('internal_type','collection');
incidentDefinition.query();
incidentDefinition.next();
gs.info('---> Table Attributes: {0}', [incidentDefinition.getValue('attributes')]);
You could also get all fields that have any attribute types:
// get the field attributes from the field list
var incidentDefinition = new GlideRecord('sys_dictionary');
incidentDefinition.addQuery('name','incident');
incidentDefinition.addNotNullQuery('attributes');
incidentDefinition.addQuery('internal_type','!=','collection'); // exclude table attributes
incidentDefinition.query();
while (incidentDefinition.next()) {
gs.info('---> Field Attributes of "{0}": {1}', [incidentDefinition.getValue('element'), incidentDefinition.getValue('attributes')]);
}
If you are looking to retrieve an attribute value of a known field/attribute combination you could do something like this:
// retrieve the value of an attribute, check to see if it is present first
var incidentRecords = new GlideRecord('incident');
incidentRecords.setLimit(1);
incidentRecords.query();
incidentRecords.next();
if (incidentRecords.caller_id.hasAttribute('ref_contributions')) {
var attribute = incidentRecords.caller_id.getAttribute('ref_contributions');
gs.info('---> Incident.Caller_ID.ref_contributions value: {0}', [attribute]);
}
You now have seen how to:
- Move the GlideRecord record Location Pointer Around, AND overcome some of the gotchas when doing so
- Utilize a more maintainable approach to Encoded Queries that makes them as-good-as or perhaps better to use than a regular GlideRecord query
- Examine the internal works of a GlideRecord Object, the record and field structure.
Cool stuff!
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-22-2018 7:32 AM
I updated the code, fixed broken links, and brought the article into alignment with my new formatting standard.
