SN Pro Tips

View Original

GlideRecord Pagination - Page through your GlideRecord query

Perhaps you've got an exceptionally long query you'd like to run.
Perhaps you need to gobble up large amounts of data via scripted REST API.
Or, perhaps you're building a neat little UI to display an arbitrary number of records.
There are a dozen reasons why you might want to "page" through a list of records in a script, but unfortunately there hasn't been a good way to accomplish this. There are nigh-undocumented APIs like "saveLocation()", but they don't tend to work consistently, or quite accomplish our goal of pagination. 

Instead, we've decided to write our own GlideRecord page-turning utility, and make it freely available here along with our other free tools. Read on for more info! 

Pro TipIf you haven't checked out our other tools yet, hover your mouse over the Tools option at the top of this page. You'll find highly useful and FREE tools, such as the Update Set Collision Avoidance Tool, the Better One-Click Approvals Tool, the Update Relocator Tool, and the Temporary Permissions Utility

This tool functions a lot like the GlideRecord class (in fact, it uses GlideRecord), except that it exposes an additional method for "turning the page", and accepts some other arguments as to how large each page should be and so on. 

The API is called PagedGlideRecord, and is represented as a Class inside a script include. 
First, I'll walk you through the code piece-by-piece, and explore how it works; then, I'll show you how to use it. Finally, I'll give you a link to an update set that'll allow you to deploy it into your own instance, in case you're lazy and don't want to copy paste my code segment-by-segment. ðŸ˜‰

Constructor

The first method in our PagedGlideRecord class is, of course, the initialize method. As with any class constructor, this method is automatically invoked when a new instance of our class is instantiated using the new keyword like so:

See this content in the original post

First I'll paste the whole initialize function (the constructor), then we'll go over it piece by piece. 

See this content in the original post

As you can see from the usage example above, the constructor (initialize) method requires at least one argument: tableName. This is the system name (not the "label") of the table to run the PagedGlideRecord on. 

However, as you can see from the arguments that the initialize method accepts (and from the JSDoc notes above it), this is not the only argument that the constructor accepts. You can also pass in an encoded query string (queryString), and an integer representing the number of records that should appear per-page (pageSize).

So let's break this function down, and go over it piece by piece. 

First, we want to enforce the mandatory argument (tableName), so we check that the tableName argument exists and is a valid value. If it doesn't/isn't, then we both log and throw an error. 

See this content in the original post

Next, we declare and initialize some additional values using the "this" keyword. "this" refers to the current PagedGlideRecord class object. By declaring them this way, we make their values accessible by any other code inside our PagedGlideRecord class. This is scoping and I promise that when used correctly, it isn't always an annoyance. Sometimes, it's even useful! 

Pro Tip: When declaring variables in the parent scope using t he this keyword, you don't need to use "var"! In fact, technically, you never need to use var. Declaring a variable without using the var keyword (while I don't recommend it generally) creates that variable in the Global scope. Which is usually not what you want, but hey, sometimes it is! 
See this content in the original post

Rather than declaring these class-level values using the arguments that were passed in, I've just initialized them using default values. That way, I can validate the passed-in arguments, and only re-assign these values if the arguments are valid. Like so: 

See this content in the original post

'Stephen Colbert' == 'truthy'

The first thing we do in the code above, is check that queryString contains a valid value. Any truthy value, actually. If its' truthiness is beyond reproach, then we overwrite the default value in the class-level variable this.queryString with the value in the variable of the same name, but in the current function scope, queryString (which we expect to have been passed into the constructor as an argument). 

We also take care to explicitly cast queryString to a string (using .toString()), because of the fact that Helsinki sometimes has difficulty - due to the new version of Rhino it's running behind the scenes - interpreting a JavaScript String from a ConsString. 

Pro TipIf you're getting the below error after upgrading to Helsinki, it means that Mozilla Rhino is failing (just as it is designed to do, now) to implicitly coerce a value of one type (such as a ConsString) to a JS String. To fix this, just find the script that's throwing the error, and cast every string explicitly, using .toString()
org.mozilla.javascript.ConsString cannot be cast to java.lang.String

Next, we want to check the final optional constructor parameter, pageSize. Here's the code, followed by an explanation:

See this content in the original post

As you can see, we first check if "pageSize && pageSize > 0". This first checks that pageSize exists and is a truthy value, and then validates that it is a number - and a positive one at that - by checking that it is greater than zero. Non-or-negative-numbers will return a falsy value from this comparison, but any value we can coerce to a positive number - even one inside a string - will return true.

Once we determine that pageSize is indeed a valid value, we explicitly parse an integer from it, just in case the user uncleverly entered a float value or a number inside of a string. As you can see from my notes on that line, I had intended to use the Number.isInteger() method to validate that pageSize was a number rather than going this route, but since the isInteger method was only added in ECMA2015, it isn't available in Geneva or earlier versions of ServiceNow running a much older version of Rhino. 

Next, in the following line that I'll re-post here, we use a ternary operator to check whether the property glide.db.max_view_records exists. If it does, we explicitly parse an int from its' value, and set the maxViewRecords variable to that value. However, if it is not set, then we set maxViewRecords to the system default maximum number of records that can be returned from a GlideRecord query in a script, which is 10,000.

See this content in the original post

Once we've got the maxViewRecords variable set to the correct value, we compare it to the pageSize argument. If pageSize is larger than the maximum number of records returnable from a GlideRecord query in your instance, then we set pageSize to whatever the maximum is. We log an error when this happens, just so the user can check if they notice something wrong, but we continue execution without halting. 

Then, once we've got a good value in pageSize, which isn't larger than the maximum allowable records in a query, we set the Class-scope level variable this.pageSize, to the value in the local function-level variable of the same name, pageSize. 

Pro TipThe maximum number of records that can be returned from a GlideRecord query, is determined by the system property " glide.db.max_view_records". If this property does not exist, then the default is 10,000 records. 

Finally, we return the current Class so that the user can assign it to some object which they can use to call the nextPage method (discussed presently). 

See this content in the original post

nextPage

The method our PagedGlideRecord Class exposes, is called nextPage. It works similarly to how the GlideRecord ".next()" method works, except that it iterates over n GlideRecords (where n is the pageSize passed into the constructor), passing each of them to the callback function in turn. 

Speaking of callback functions, let's have a look at how this function works. As before, I'll paste the whole function in and then we can go over it piece by piece. 

See this content in the original post

You may have noticed that in addition to the arguments specified in the constructor when instantiating a new instance of the PagedGlideRecord Class, the nextPage method also accepts one semi-optional argument: a callback function. 

See this content in the original post

This little code block actually just calls another method of our PagedGlideRecord class (which the user can also call to specify the callback function if they like) called setCallback().

See this content in the original post

This method does the work of validating the callbackFunction, returns true if the argument is valid, returns false if the argument is falsey but the callback function has already been specified, and throws an error if both are invalid/non-existent. 

I referred to the callbackFunction argument of the nextPage method as semi-optional, because it must be specified the first time nextPage() is called (unless the setCallback() method is called, but can actually be omitted on subsequent calls. Basically, all we're looking for in the code above, is that either the callbackFunction argument is specified, or the parent-scope this.callbackFunction property exists. Whether it was specified by passing that argument into setCallback() or nextPage() doesn't matter all that much, and I don't (in this script) use the returned boolean value. 

Next up, we just set up the GlideRecord and add the encoded query, if one was specified. 

See this content in the original post

Pretty standard stuff using the GlideRecord API, except for this new (and relatively undocumented) method: .chooseWindow(). This is actually specific to the new-ish scoped API, and is the key to avoiding building this whole thing hackily around .getLocation, .setLocation, .saveLocation, and .restoreLocation. As the code-comments state, .chooseWindow() allows us to specify the exact span of the "page". This should also give you a hint as to why we used class-level properties, rather than function-level variables to contain the currentLocation and pageSize values - because we want to be able to call .nextPage() over and over, without re-specifying (or indeed, re-acquiring) these values. This allows us to avoid several major performance-degrading loops. 

After we query, inside of a try block, we then take several careful steps... First, we check if the query returned any actual records. If not, we go ahead and return false. 

See this content in the original post

We return false in this case, because we want to be able to call .nextPage() inside of a loop just like .next() from a GlideRecord. In other words, I want to be able to call it like this: 

See this content in the original post

Next, we increment the counter this.page, mainly just for logging and record keeping, but also so the user can access that property using the .getPage() method. 

See this content in the original post

And then we iterate over each returned GlideRecord object -- calling the callback function once for each record, and passing in the GlideRecord. We then iterate a counter for reasons that will become clear momentarily. 

See this content in the original post

I wanted this to pass each record, one at a time, into the callback function for my purposes. So for example, if I specified my pageSize as 200 (and the table had at least that many records matching my query), then my callback function would be called 200 times - once for each record - and the records would each be passed in, one at a time. It would however, be trivially easy to modify this so that it passed in the entire un-iterated 'gr' object, then called gr.getRowCount() and added that number to this.currentLocation, and you'd be able to call the callback function only once for each page. You could then do your iterating (while (gr.next()) {}) inside the callback function. The performance however, would be pretty much identical. 

Next, we re-set this.currentLocation to the new current location (the last record iterated over), and add a message to the system logs to track our progress like so:

See this content in the original post

Now we just need to check whether there are any records left to be iterated over, return true if so, and return false if not. Here's the code, followed by an explanation of how we did it: 

See this content in the original post

The if block checks whether the counter (which you'll remember, was counting the number of records we looped over) is less than the value in pageSize. If that's the case, then that means we didn't find enough records to finish even the current page, so it's a fair bet that there isn't a next page. 

Now in 999 cases out of a thousand, if the counter is the same as the pageSize, we can assume that there are more records left after the end of this page. However, we can't be 100% certain unless we check... but, we don't want to impact performance. 

The else block does this by setting a new window that will contain only one record (to minimize the size of the query).
In fact, it occurs to me that we could further reduce the performance hit by a tiny amount if we instead set the first window to one more than the pageSize limit, stopped iterating over the GlideRecord once the counter hit the same value as pageSize, and returned the value of gr.hasNext() at that point, but I've already written most of this article so I'm not going to go back and change it! I will however, include the improved-performance logic in the tool you can download from the link at the end of this article. ðŸ˜‰

So that's all there is for the core logic. Let's have a look at the whole thing, all put together! 

See this content in the original post

Beautiful, isn't she? Man, I love coding. 

Want to deploy this into your instance, so that you too can run paginated GlideRecord queries? No problem! Just head on over to this page, or hover your mouse over Tools in the navigation bar at the top of this page, and click on Paginated Glide Record Utility

Pro Tip: Pairing this callback with a javascript debounce function (as in underscore.js) would be a fantastic way to page through a huge number of records, SAFELY!

Have you got questions or comments? Want us to work with you, or just get our advice? We are here to help! Click on Contact Us to schedule some time to chat with us any time that's convenient for you. We'd love to hear from you!
If you'd love to hear from us too, use the form below or click here to subscribe

See this content in the original post