Using GlideRecord (and its friends)

The previous two chapters demonstrated that ServiceNow is built around data. Background scripts, and scripts in general, can use the excellent database connectivity capabilities built into ServiceNow to easily access whatever information we want. GlideRecord is instrumental in doing this.

GlideRecord is a class that lets you work with a database table. With it, you can create, read, or update records in a database. In general, you work one record at a time, iterating through a result set.

It is easy to use, but I recommend reviewing the basics from the product documentation or a scripting course. In this chapter, we'll quickly run through the basics and start to explore the gotchas as well as some very interesting features that are misunderstood.

Tip

For full details, investigate the documentation at https://developer.servicenow.com/app.do#!/api_doc?v=helsinki&type=server&scoped=true&to=class__scoped_gliderecord__helsinki.

Let's run some code in Background Scripts to allow us to understand how GlideRecord works. This code is a little artificial, but the aim is to find out the last two times Alice checked in:

var results = []; 
var gr = new GlideRecord('x_hotel_check_in'); 
gr.addQuery('guest.name', 'CONTAINS', 'Alice'); 
gr.setLimit(2); 
gr.orderByDesc('sys_created_on'); 
gr.query(); 
while(gr.next()) { 
  results.push(gr.sys_created_on + ''); 
} 
gs.info(results.join(', ')); 

Now, let's look at the code in detail:

1. Perhaps it'd be useful to know when Alice is checking in to the hotel. The third line adds a filter that means the result set will only contain records where the guest's name contains Alice. You may recognize this as a SQL WHERE clause.
2. A GlideRecord object is always instantiated with the table you want to work with. The resulting object is a representation of that table. However, it isn't usable until a function is called to either make a new record (gr.newRecord()) or perform a query (gr.query()).
3. The very first line is a standard line of JavaScript. It creates a variable named results, making it an array. The output will be stored here.

   Tip

   Note that the condition can use dot-walking to navigate through reference fields. In the background, ServiceNow performs a join on the referenced table, allowing it to filter the results. This is invisible to the script writer since the resulting GlideRecord has exactly the same structure. This functionality is incredibly useful and can save significant development effort. However, because you are performing a join, the database does need to expend more effort, so be smart about how you do it.

   Generally, you either pass two or three parameters to addQuery. The first and last are the fieldname and the value you are searching for, respectively. The optional middle value is which type of clause you want, from standard operations such as greater than and equal to. For example, using this on a Room GlideRecord query will return floors above 10:

   gr.addQuery('floor', '>=', 10)

   In this CONTAINS example, a pattern-matching text search is run (equivalent to the LIKE '%<value>%' SQL clause). It is looking for a name that contains Alice anywhere in it; Alice Cooper will match, as will Steve Doralice.

4. Next, the script instructs ServiceNow to only return a maximum of two records, no matter how many matching entries there might be in the database. When writing scripts, it is always a good idea to test them on a limited subset before unleashing your query on thousands of records.

   Tip

   Once the query has been returned, you can use getRowCount to return the number of rows that actually have been found. However, only use this function if you are actually going to deal with the records anyway, since it is rather inefficient. For example, in a script that deletes records, use getRowCount to check how many you will be deleting. If you are expecting to delete five, but you see that there are instead 5,000, then make it stop!

   GlideAggregate, discussed later in this chapter, is a more efficient way of counting if you are only interested in the number of results.

5. The next step is to order the results. Choose any column to order the result set.
6. Once the query has been set up, it's time to execute it and ask the database to get the information. From now on, the GlideRecord object is the result set.
7. A while loop is then executed to deal with all the data. The next function will iterate in the result set returning whether it was successful. This is a very common pattern for stepping through all the records.

   Tip

   Sometimes, you only want to get a single record in a table. On such an occasion, you can call the get function, which rolls several commands together-setLimit(1), query, and next-skipping up to three lines. It returns a Boolean true/false value indicating if it found a record.

   If you pass a single parameter through, get will assume it is a sys_id value, allowing you to grab a record with little fuss. If you pass two parameters, the first allows you to specify the field, the second the value. This is a real timesaver. Consider the following code snippet:

   var gr = new GlideRecord('u_check_in');
   gr.get('guest.name', 'Alice Richards');
   gs.info(gr.sys_created_on + '');

8. Some standard JavaScript functions are used for each result, appending the information we are interested in (the built-in Created On field) to the array. The script ensures that each value is converted to an string. The next section looks at the reason in much more detail.
9. After the loop is closed, the result set is joined together. Finally, it is written to the system log using the gs function we've already seen.

Understanding iterators

An iterator such as GlideRecord can be difficult to get your head around, but an analogy may help. Imagine that all our data is stored in a big filing cabinet, and in that cabinet is a file. Inside the file are sheets of paper, with each sheet of paper being a reservation for one of our fine rooms at Gardiner Hotels. When we want to know which room they are checked in to, we must consult the right bit of paper. But how to find it?

GlideRecord might be considered a remarkably efficient office administrator that does the searching for you. You provide the parameters of the search (such as the guest name being Alice, as seen previously), and they will come back with a stack of paper. Our office superstar shows you each sheet of paper, one by one. When you have that sheet, you can read or change the information on it as you wish, and when you are done, you hand it back and get the next one. Of course, while the paper is in your hand, you could also scrunch it up and toss it impressively into the bin.

Note

This repetitive process of looking at a single sheet of paper in turn is like "iterating" over a result set. You are only working with the data from a single record at a time. If you want to compare two records together, you need save the information you want out in a separate variable. This is covered in the next section.

Accessing data from GlideRecord

As you iterate over a result set, you get access to all the fields in the table. But you don't just get the data itself-you also get information about the data. For example, you can protect data from changing or control who can see it. GlideRecord (using functions such as canRead and canWrite) lets you know what you are allowed to do.

To achieve this, GlideRecord uses an unusual ability of JavaScript to redefine objects on the fly. Every time the next function is called, the ServiceNow platform grabs the data in the table and attaches them as properties to the GlideRecord object. These properties are stored as GlideElement objects, which represent the field in the database.

Some of the most useful functions you can call on a GlideElement object are the changes and changesTo functions. These return true if the field value has been altered. As we'll see, this is invaluable for knowing what data is being altered, be it by the user or someone else.

Tip

The product documentation has more information on GlideElement objects: https://developer.servicenow.com/app.do#!/api_doc?v=helsinki&type=server&scoped=true&to=class__scoped_glideelement__helsinki.

The field information (the GlideElement object) can be accessed in two ways: using the typical dot notation (gr.sys_created_on) or through a square-bracket notation (gr['sys_created_on']). This is very useful if you need to access fields programmatically:

var f = 'my_field'; 
gr[f] // is the same as: 
gr.my_field 

This allows you to quickly, for example, loop through several fields, clearing them in a fairly concise way:

var fields = ['u_field_1', 'u_field_2', 'u_field_3, u_field_4']; 
for (var i = 0; i < fields.length; i++) { 
  gr[fields[i]] = ''; 
} 

Here, a for loop iterates through the array using the bracket notation to access the right property. The alternative, which just repeats code, is not as elegant.

Tip

Most of the time, you will just be interested in the value of the field. But it's important to understand what you are actually accessing, since it can lead to surprising results, as we'll soon see.

Walking through reference fields

Dot-walking lets you access information through reference fields using the dot notation. Accessing the name of a guest from a GlideRecord object of Check-in is as simple as using gr.guest.name.

The reference field has access to the fields of the table it's pointing to. A reference field pointing to the User table will have access to the user fields and not to any field in the Guest table, such as the Membership number field created in the previous chapter.

Tip

It is possible to access extended fields in a script using a special notation: ref_<tablename>.<extended field>. So, the Membership number field could be accessed through a User reference field such as gr.ref_x_hotel_guest.membership_number. Note you only need this syntax if the reference field points to the base table.

Converting data types

JavaScript is loosely typed. This means that it will convert values into the relevant types wherever it can. If you are performing arithmetic, it will convert it to a number according to the rules. GlideElement returns the value of the field when you access the object when you request a string. Most of the time, that's great. But the line where we got data from the database specifically converted the value into a string by adding the empty string (+ ''). This is important to do; if not done, weird stuff happens.

The surprising results of GlideElement

Line 8 of the previous script is this:

  results.push(gr.sys_created_on + ''); 

Try changing it to this:

  results.push(gr.sys_created_on); 

Only a single tiny difference has been made, but it makes a big difference to the output. If you run it with the adjusted code (and you have two matching records), you will get two identical outputs. I now get this:

*** Script: 2016-08-16 21:06:23, 2016-08-11 22:12:41 

With the original code, I would've got this:

*** Script: 2016-08-11 22:12:41, 2016-08-11 22:12:41 

Why?

The key to understanding this is knowing what is being pushed into the results array. When an object (the GlideElement representing the sys_created_on field) is pushed into the array, it actually puts an object pointer into the array. This means that the two elements of the array populated are actually pointing to the same object. Therefore, when the contents of the array are output, the same value is printed twice.

To reiterate, when you get the repeated value, you are not storing the value of the field in the array, but the GlideElement object. And this matters, because when gr.next() is called, the value of sys_created_on changes.

Sometimes, this is exactly what you want. However, when you are manipulating a GlideRecord value, this is often not the case. The way to avoid this is to get the value of the field. An easy way to do that is to ensure that the data put into the array is converted into something you want to work with, such as a string.

Tip

Strings are primitive types. They don't use pointers, and so they don't exhibit this behavior. This is why you should convert GlideElement into strings unless you are careful. Check out the information on pointers at http://cslibrary.stanford.edu/.

Getting the value another way

Of course, there are alternatives. There are some functions in GlideRecord and GlideElement that can help-specifically, getValue and toString- that perform the conversions for you. You may want to always do this so you don't forget. Both these lines are drop-in replacements for line 8:

results.push(gr.getValue('sys_created_on')); 
results.push(gr.sys_created_on.toString()); 

But programmers generally avoid keystrokes whenever necessary (and so may prefer + ''), and I certainly prefer manipulating GlideElement directly. Whatever you choose, it is always important to know why you follow certain conventions.

Dealing with dates

Dates and times are generally tricky to work with, since while the idea of 60 minutes, 24 hours, 7 days, and 12 months is very normal to a person, the almost arbitrary nature of the amounts causes some consternation through code. Therefore, ServiceNow provides access to GlideSystem and GlideDateTime, both of which contain many functions to generate and deal with time.

Tip

For more information, check out the ServiceNow developer site: https://developer.servicenow.com/app.do#!/api_doc?v=helsinki&type=server&scoped=true&to=class_scoped_glidedatetime_helsinki.

The developer site provides many examples of how to perform date arithmetic. This is notoriously tricky to do manually, so I suggest using them if possible. The most useful functions are available through GlideDateTime. For a date/time field, you can extract the GlideDateTime object through a GlideElement object. For example, to increment a date by a day, (and change the value of the sys_created_on field) you can use the following code:

gr.sys_created_on.getGlideObject().addDays(1); 

Tip

Of course, you can also create your own GlideDateTime object, and make it do what you want, as the documentation shows.

If you go beyond simple situations (such as "How many weeks in a month?"), it can be advantageous to extract information in Unix time and work from there. (Unix time is the number of seconds since midnight, 1 Jan, 1970 UTC, which may be described as when time began for computers!) This can then be manipulated like any other number. The following line of code will give you the time in Unix time:

gr.sys_created_on.getGlideObject().getNumericValue();