NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: BEGINNER
Assumes a good beginner level of knowledge and/or familiarity with Scripting in ServiceNow.
In this series of articles I am going to focus on some of the ins-and-outs and observations of the GlideRecord class. I will be starting with the basics and walking all the way through to advanced concepts. I am not the first to address this, but it is my intent to present it in a way that the examples could be used to learn, and understand, the concepts behind using this sometimes confusing class. I will also be throwing in, where appropriate, a SQL translation of what the GlideRecord essentially does under-the-hood when it creates the actual query that is submitted to the MySQL database.
Axioms (things you can hang your hat on)
- ServiceNow uses the hierarchical MySQL database structure. NOT the relational structure! This has the benefits of strikingly fast reads, with the trade-off of slow deletes. If you are interested in digging further read here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
- The GlideRecord object always does a SELECT *. While not very efficient it does have the benefit of still working pretty fast and returning ALL fields in the record.
- You CANNOT DO Stored Procedures. Ugh! Argh! Really?! Wow! Closest you can get is via a Script, or Database View. Crying here (have been for years).
- The GlideRecord object is sealed. What this means is that the structure is immutable. Even though it is a Java object its structure is carefully (sort of) managed under-the-hood. You CAN modify the structure in your own instance via a prototype, but only in Global. You CANNOT modify GlideRecord via prototype in the Scoped version (a huge failing in my book).
- There is no provision for Order-of-Precedence. Period. Sigh. This is especially glaring in the encoded queries. Watch out for it. It WILL bite you eventually. Note: The structure of your GlideRecord query will imply an order-of-precedence, but it is primitive. Encoded queries DO NOT support parenthetical structuring.
- GlideRecords DO NOT usually error out on a bad WHERE clause. Instead the line is ignored! BEWARE: This can cause extreme headaches and other physical ailments! Watch out for this and test, test, test when it comes to Update and Delete GlideRecord queries!!!!!!!!!
- There is no support of BETWEEN. You will need to code this the hard way or with an encoded query.
- There is no support of the WHERE clause AND-OR-AND. i.e. (this AND this) OR (this AND this). Period. Sorry. You will need to SCRIPT this if you need that functionality.
- With testing GlideRecord Scripts - Background, and/or Fix Scripts are your friend. Stay away from Fix Scripts if you are working in a scoped environment. See: ServiceNow Scripting 101: Two Methods for Code Development
- You do not have access to System Tables. This is painful at best. Really?! At least let us see some things read-only anyway. Not everything is a security violation.
- You DO have access to table and field definitions, but you have to know where to look, and how to pull it together (I will be covering that in a further-on article of this series).
- There is a known long-standing issue with GlideRecord where a retrieved variable cannot be accessed correctly directly. For example with a GlideRecord of incidents named "incidents":
while (incidents.next()) {
var number = incidents.number;
gs.info('---> number: ' + number);
}
Will likely print only the last number in the list for all records printed off! This has something to do with the Java -> to JavaScript conversion after the query is run. I never have been given a good reason actually. The cure is to stringify the value (i.e. incidents.number + '' or .toString or to use .getValue('<<field>>')). See: Community Code Snippets: Some Observations on String Conversion Performance
13. There is no UNION analog. Period. You can sort-of get around this by creating a Database View and then referencing that result with a GlideRecord. Another way is to do two GlideRecord pulls from the different tables, convert those to JavaScript Object arrays, then merge the two arrays together. Now THAT is painful!
14. All WHERE statements start with an .addQuery are AND unless specified as .addOrCondition in which case they become OR.
15. There seems to be a nauseating propensity to do the "Lazy Programmer" thing with GlideRecords and use the "gr" naming convention. This violates all sorts of variable naming best practices, and actually leads to quite a few errors. I encounter these often in the field. Don't fall into this practice. It has zero true readability, and is a poor maintenance practice. See: Community Code Snippets: Variable and Function Naming
Setting up your basic query
This is all there is to it! The string can be replaced with a variable, btw. This is a neat trick: Anywhere you see a string, you can replace it with a variable. I will be expanding on this a bit more in another article.
Get ALL records from the Incident table. Note the case of the table name! It is important to use the proper table name. When in doubt go to System Definition > Tables and look it up!
var incidents = new GlideRecord('incident');
incidents.query();
gs.info('---> count: ' + incidents.getRowCount());
This translates to the following MYSQL SQL:
SELECT * FROM incident
Accessing the records in the basic query
I present three ways to lock-in the value with each loop of the while statement. This is important or you WILL run into that pesky "last record" problem with the values (see Axioms above). The .getValue is 16% more efficient than the other methods. I ran the numbers! See: Community Code Snippets: Some Observations on String Conversion Performance
var incidents = new GlideRecord('incident');
incidents.query();
while (incidents.next()) {
// the three ways to "freeze" the value from the GlideRecord
gs.info('---> number: ' + incidents.number + '');
gs.info('---> number: ' + incidents.number.toString());
gs.info('---> number: ' + incidents.getValue('number')); // most efficient
}
Limiting the number of records
Way 1 (my favorite)
Set limit is probably the best way to do this. Notice that you can use variable replacement if you choose.
var limitValue = 1;
var incidents = new GlideRecord('incident');
incidents.setLimit(limitValue);
incidents.query();
while (incidents.next()) {
gs.info('---> number: ' + incidents.getValue('number'));
}
The query translates to SQL:
SELECT * FROM incident LIMIT 1
Way 2 (set to a single record for return)
This is an okay way. It can be a maintenance problem. Your eye can miss the fact that it uses an if, and should you ever remove the .setLimit it could trip you up.
var problems = new GlideRecord('problem');
problems.setLimit(1);
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Way 3 (don't do this)
As with Way 2 this could actually turn into a bug. People, including me, could miss the if and think it was actually a while (it happens!) when debugging the code, or trying to find the issue because it isn't working as expected! Avoid this sort of scripting if possible. Or comment it!
var problems = new GlideRecord('problem');
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Way 4 (don't do this)
Way too much code to accomplish a simple task! Makes my eyes bleed to look at this!
var limitValue = 1;
var problems = new GlideRecord('problem');
problems.query();
var count = 0;
while (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
if (count++ > limitValue) {
break;
}
}
Equals Operator - =
The following query finds the exact match:
var problems = new GlideRecord('problem');
problems.addQuery('number', '=', 'PRB0000001');
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Translates to SQL:
SELECT * FROM problem WHERE number='PRB0000001'
A constrained query: Implied Equals Operator - =
The equals operator is the default operator and really does not need to be included in the .addQuery. I use this method. It is pretty much standard. And takes five fewer strokes to type in! 😀
var problems = new GlideRecord('problem');
problems.addQuery('number', 'PRB0000001');
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
With this query you still are retrieving ALL records with a number of PRB0000001, and only using the first (random) one encountered.
Here is a slightly better way. Note that the .get assumes an implied equals. Btw, you can use ANY comparison in a .get. You are not constrained to just the sys_id.
var problems = new GlideRecord('problem');
if (problems.get('number', 'PRB0000001')) {
gs.info('---> number: ' + problems.getValue('number'));
}
Which translates to the following SQL:
SELECT * FROM problem WHERE number='PRB0000001' LIMIT 1
This query really does not appear any faster, and it is my thought that it is really similar to the if under-the-hood. Just a short-hand way of expressing the same thing.
Note: There is a default field for the .get and that is the sys_id field; something like this will work fine: problems.get('a11cd8b10f1353402ae14e9ce1050ee7')
As a side-note the get takes a maximum of two parameters. So, this does not work! It will return nothing.
This does NOT work:
var problems = new GlideRecord('problem');
if (problems.get('number', '=', 'PRB000000')) {
gs.info('---> number: ' + problems.getValue('number'));
}
Greater Than Operator - >
Pretty straight-forward:
var servers = new GlideRecord('cmdb_ci_server');
servers.addQuery('cpu_count', '>', 2);
servers.query();
while (servers.next()) {
gs.info('---> name: ' + servers.getValue('name'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_server WHERE cpu_count > 2
Less Than Operator - <
Another one that is pretty straight forward:
var servers = new GlideRecord('cmdb_ci_server');
servers.addQuery('cpu_count', '<', 5);
servers.query();
while (servers.next()) {
gs.info('---> name: ' + servers.getValue('name'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_server WHERE cpu_count > 2 AND cpu_count < 5
Less Than or Equal To, Greater Than or Equal To Operators - <=, >=
Of note is that this example is also how you do a BETWEEN. Ugh! See: Mini-Lab: GlideRecord Between Query Extension for a possible Global-only alternative.
var servers = new GlideRecord('cmdb_ci_server');
servers.addQuery('cpu_count', '>=', 2);
servers.addQuery('cpu_count', '<=', 5);
servers.query();
while (servers.next()) {
gs.info('---> name: ' + servers.getValue('name'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_server WHERE cpu_count BETWEEN 2 AND 5
OrderBy
This example gives a list of 10 ascending numbers:
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.setLimit(limitValue);
computers.orderBy('number');
computers.query();
while (computers.next()) {
gs.info('---> serial number: ' + computers.getValue('serial_number'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_computer WHERE ORDER BY number LIMIT 10
Order By Descending - OrderByDesc
This example gives a list of 10 descending numbers:
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.setLimit(limitValue);
computers.orderByDesc('number');
computers.query();
while (computers.next()) {
gs.info('---> number: ' + computers.getValue('number');
}
The query translates to the following SQL:
SELECT * FROM cmdb_ci_computer WHERE ORDER BY DESC number LIMIT 10
OR Command
Notice that you can dot-walk the commands AND since this is JavaScript you can continue the dot-walk onto other lines for better readability (best-practice). Also, note it is NOT necessary to throw anything more complex together to actually do an OR condition. This sort of thing works just fine. It is more readable and much more easy to understand and maintain. See the following link for the more nasty, complex, unrecommended way of doing this: link (you may have to read down a bit).
To run this query you will probably have to fill in the Form Factor field on a few computer records. This field is used by CMDB admins to delineate different types computers and servers (think: sub-class):
// add form factor to the computer form.
// set the form factor on a couple of records. One for desktop, one for laptop
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'desktop')
.addOrCondition('form_factor', 'laptop');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> name: ' + computers.getValue('name') +
', serial_number: ' + computers.getValue('serial_number') +
', form_factor: ' + computers.getValue('form_factor'));
}
The query translates to the following SQL:
SELECT * FROM cmdb_ci_computer
WHERE form_factor = 'desktop' OR form_factor = 'laptop'
ORDER BY name
LIMIT 10
GroupBy
Really does not have any perceivable affect on the retrieved records, and is really meant for GlideAggregates. It is allowed, however. 🤔
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'desktop')
.addOrCondition('form_factor', 'laptop');
computers.groupBy('form_factor')
computers.query();
while (computers.next()) {
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
}
The query translates to the following SQL:
SELECT * FROM cmdb_ci_computer
WHERE form_factor = 'desktop' OR form_factor = 'laptop'
GROUP BY form_factor
LIMIT 10
Null Check
I have presented two different lines with two different methods of doing this. I feel the second method is a bit more readable. There does not appear to be a performance increase with either method.
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'Dell');
computers.addQuery('form_factor', '!=', null); // method 1
computers.addNotNullQuery('serial_number'); // method 2
computers.setLimit(limitValue);
computers.orderBy('name');
computers.query();
while (computers.next()) {
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
}
This query translates to the following SQL:
SELECT cmdb_ci_computer.* FROM cmdb_ci_computer
JOIN core_company AS manufacturer ON cmdb_ci_computer.manufacturer = manufacturer.sys_id
WHERE manufacturer.name = 'Dell'
AND cmdb_ci_computer.form_factor IS NOT NULL
AND cmdb_ci_computer.serial_number IS NOT NULL
ORDER BY cmdb_ci_computer.name
LIMIT 10
Handling a null serial_number value
So if you don't deal with it you can get unexpected or even no values from a returned null or blank field. So as a best-practice deal with this outcome. One method you can use is described in the While statement with this example. There are several approaches to this. Just be sure to handle the potential problem.
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'Dell');
computers.addNotNullQuery('form_factor');
computers.setLimit(limitValue);
computers.orderBy('name');
computers.query();
while (computers.next()) {
var serialNumber = computers.getValue('serial_number');
if (gs.nil(serialNumber )) {
serialNumber = 'Unknown!';
}
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), serialNumber, computers.form_factor]);
}
WHERE Dot-walk
Here is an example of dot-walking a reference field. You can go as deep as you desire. I usually keep it simple (one-deep), but do what you need to; to get the desired result. Note the SQL transliteration below. It has an interesting twist to it. Remember GlideRecords do a lot of the JOIN work for you in regards to reference fields. It is not necessary for you to do the heavy lifting as the query does it for you!
Update the form factor for all apple Macbook machines.
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'apple');
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
// we could have used .setValue here, it has the same affect, but is much more difficult to read and maintain
// no percievable performance increase for decreased readability
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.update(); // test first before making the update!!!
}
The query translates to the following SQL:
UPDATE cmdb_ci_computer
JOIN core_company AS manufacturer ON cmdb_ci_computer.manufacturer = manufacturer.sys_id
SET cmdb_ci_computer.form_factor = 'Laptop'
WHERE manufacturer.name = 'apple'
AND cmdb_ci_computer.form_factor IS NOT NULL
LIMIT 10
Bad Constraint
Here we have a bad dot-walked value. The error is suppressed, and the condition is thrown out; making it much more generic query! This would update all form_factor records that are not null (a much greater sub-set!!!!!). I HATE this feature of GlideRecords. Remember: Test TEST TEST TEST! Did I mention that you should TEST?
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.nam', 'apple'); // bad dot-walk!
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.update(); // test first before making the update!!!
}
Bad constraint - a second example
Here is an example of a bad method: addQuey. The error is suppressed, and the condition is thrown out. This has the same affect as the previous example in that it will update a much larger sub-set than anticipated!
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuey('manufacturer.name', 'apple'); // totally bogus line that is thrown out!
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.update(); // test first before making the update!!!
}
Try Catch fail
Since errors are suppressed the Try/Catch will never occur. This then becomes useless code unless something happens in the While loop.
try {
var limitValue = 10;
// update the form factor for all apple macbook machines. dot-walk example
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuey('manufacturer.name', 'apple'); // this line gets ignored!
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
//computers.update(); // test first before making the update!!!
}
}
catch(err) {
// boom?
gs.info('---> ERROR: {0}', [err]);
}
That's it for the beginner stuff. In my next article I will be digging a bit deeper, and showing some query examples that begin to demonstrate the power of available to you with GlideRecords!
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-08-2018 2:27 PM
I updated the code and brought the article into alignment with my new formatting standard.
