Sorting Records Using the orderBy Method: A Complete Guide

By /






Mastering Data Order: Sorting Records with GlideRecord’s orderBy() in ServiceNow


Mastering Data Order: Sorting Records with GlideRecord’s `orderBy()` in ServiceNow

Ever found yourself staring at a wall of data, wishing it would just make sense? In the world of enterprise service management, particularly within ServiceNow, data is king. But raw, unsorted data can be more confusing than helpful. That’s where the magic of sorting comes in! If you’ve spent any time working with ServiceNow’s server-side scripting, you’ve likely encountered GlideRecord – the cornerstone for interacting with the platform’s database.

Today, we’re diving deep into some truly indispensable GlideRecord methods: orderBy() and orderByDesc(). These aren’t just fancy commands; they’re essential tools that empower you to present information in a logical, user-friendly manner, whether you’re building a report, populating a widget, or simply debugging a script. We’ll also touch upon setLimit(), a handy companion for managing how much data you actually retrieve. So, grab a coffee, and let’s unravel the art of ordering your ServiceNow data!

The Core of the Matter: Why Sorting?

Before we jump into the code, let’s take a moment to appreciate why sorting is so crucial. Imagine a list of 1000 incidents. If they’re just pulled in a random order, finding the most urgent ones, the oldest ones, or even just incidents related to a specific category, becomes a Herculean task. Data, by itself, is just a collection of facts. When organized, it transforms into valuable information, telling a story and guiding decisions.

  • Readability & User Experience: Users expect data to be presented logically. A list of tasks sorted by due date, or incidents by priority, is far more intuitive.
  • Reporting & Analytics: Many reports require data to be presented in a specific order – think “Top 10 Most Critical Incidents” or “All Resolved Tickets by Date.”
  • Performance & Efficiency: While sorting primarily affects the *presentation* of data, combining it with methods like setLimit() can significantly reduce the amount of data processed, improving script performance.
  • Debugging & Development: When troubleshooting, seeing records in a consistent, sorted order can help you quickly identify patterns or anomalies.

In ServiceNow, GlideRecord is your go-to class for performing database operations like querying, inserting, updating, and deleting records. It’s like your personal digital librarian for the ServiceNow database. And just like a good librarian, it knows how to keep things in order.

Getting Started with orderBy(): Ascending Order

The orderBy() method is your primary tool for arranging records in ascending order. Think of it as telling your digital librarian, “Hey, please line up these books from A to Z, or from 1 to 10, or from the earliest date to the latest.” It’s the most common way to sort data, providing a natural flow for most lists.

How it Works:

You call orderBy() on your GlideRecord object and pass it the field name you want to sort by. ServiceNow then fetches the records and arranges them alphabetically for strings, numerically for numbers, and chronologically for dates, all in an increasing sequence.


var gr = new GlideRecord('table_name');
gr.orderBy('field_name');
gr.query();
// ... then loop through results

Practical Example: Ordering Incidents by Short Description (Ascending)

Let’s revisit Exercise 11 from our reference material. We want to display all incidents with a priority of 1 (Critical) and category ‘software’, sorted by their short_description in ascending order.


var inc = new GlideRecord('incident');
inc.addQuery('priority', '1'); // Using two arguments for clarity, equivalent to 'priority=1'
inc.addQuery('category', 'software');
inc.orderBy('short_description'); // Here's our star!
inc.query();

gs.print('--- Incidents sorted by Short Description (Ascending) ---');
while(inc.next()){
    gs.print(inc.number + ' ' + inc.short_description);
}

Let’s break down what’s happening here, line by line:

  1. var inc = new GlideRecord('incident');
    We’re creating a new GlideRecord object named inc, specifically targeting the incident table. This is our handle to interact with incident records.
  2. inc.addQuery('priority', '1');
    We’re adding a filter. We only want incidents where the priority field is set to ‘1’ (which typically corresponds to ‘Critical’).
  3. inc.addQuery('category', 'software');
    Another filter, narrowing our search to incidents categorized as ‘software’. We’re already making our data retrieval quite specific!
  4. inc.orderBy('short_description');
    This is the key! We’re telling ServiceNow, “Once you find all those priority 1 software incidents, please arrange them alphabetically based on their short_description.” So, “Application Crash” would come before “Database Error.”
  5. inc.query();
    This line executes all the filters (addQuery) and the sorting instruction (orderBy) against the database. It fetches the matching records, already sorted.
  6. while(inc.next()){ ... }
    This loop iterates through each record that matches our query and sorting criteria. For each record, inc.next() moves to the next one, making its field values available.
  7. gs.print(inc.number + ' ' + inc.short_description);
    Inside the loop, we’re simply printing the incident number and its short description to the console (or script output). You’ll notice the output is now perfectly alphabetized by short description.

Real-world Application: Imagine building a custom portal widget that displays “Your Top Priority Incidents.” Using orderBy('priority').orderBy('opened_at') would allow you to show the most critical and oldest incidents first, providing immediate value to the user. This simple sort dramatically improves usability!

Flipping the Script with orderByDesc(): Descending Order

Sometimes, you don’t want things from A to Z. You want them from Z to A. Or from the newest to the oldest. Or from the highest value to the lowest. That’s where orderByDesc() shines! It’s the inverse of orderBy(), arranging your records in descending order.

How it Works:

Just like orderBy(), you pass the field name to orderByDesc(). However, this time, strings will be sorted from Z to A, numbers from highest to lowest, and dates from the latest to the earliest.


var gr = new GlideRecord('table_name');
gr.orderByDesc('field_name');
gr.query();
// ... then loop through results

Practical Example: Ordering Incidents by Short Description (Descending)

Let’s take our previous scenario and simply flip the sorting order using Exercise 12.


var inc = new GlideRecord('incident');
inc.addQuery('priority', '1');
inc.addQuery('category', 'software');
inc.orderByDesc('short_description'); // Our new star!
inc.query();

gs.print('--- Incidents sorted by Short Description (Descending) ---');
while(inc.next()){
    gs.print(inc.number + ' ' + inc.short_description);
}

The structure is almost identical to the orderBy() example, with one crucial difference:

  • inc.orderByDesc('short_description');
    Instead of arranging alphabetically A-Z, this tells ServiceNow to sort from Z-A. So, “Web Server Issue” would now appear before “Application Crash.” This is incredibly useful for things like showing the “most recent” or “highest priority” items first.

Real-world Application: Need to see the *most recent* tickets that were closed? orderByDesc('closed_at') is your best friend. Want to highlight the *highest priority* incidents first? Combine orderByDesc('priority') with your query. It’s all about presenting the most relevant information front and center.

Adding a Dash of Control: setLimit()

While not strictly a sorting method, setLimit() often goes hand-in-hand with sorting. Imagine you’re building a dashboard. Do you really want to show all 5000 critical incidents? Probably not. You likely want the “Top 10” or “Most Recent 5.” That’s where setLimit() comes in – it tells GlideRecord to retrieve only a specified number of records.

How it Works:

You call setLimit() on your GlideRecord object and pass it an integer representing the maximum number of records you want to retrieve.


var gr = new GlideRecord('table_name');
gr.setLimit(10); // Get only the first 10 records
gr.query();
// ... then loop through results

Important Note: setLimit() retrieves the *first* N records *after* any sorting has been applied. This is crucial for getting “Top N” or “Latest N” results.

Practical Example: Displaying the Latest 10 Incidents (Priority 1)

Exercise 13 demonstrates combining sorting with limiting the results.


var inc = new GlideRecord('incident');
inc.addQuery('priority', '1');
inc.orderByDesc('opened_at'); // Sort by opened_at in descending order to get the latest first
inc.setLimit(10);             // Get only the top 10 from that sorted list
inc.query();

gs.print('--- Latest 10 Priority 1 Incidents ---');
while(inc.next()){
    gs.print(inc.number + ' ' + inc.short_description);
}

Here’s the breakdown:

  1. var inc = new GlideRecord('incident');
    Standard GlideRecord initialization.
  2. inc.addQuery('priority', '1');
    Filtering for critical incidents.
  3. inc.orderByDesc('opened_at');
    This is key for getting “latest” records. We sort by the opened_at timestamp in descending order. This means the newest incidents will appear at the top of our result set after the query.
  4. inc.setLimit(10);
    Now, this tells ServiceNow: “From that list of critical incidents, sorted newest to oldest, I only want the first 10.” Without this, we’d get all priority 1 incidents, sorted, which could be thousands.
  5. inc.query();
    Executes the query, sorting, and limit.
  6. while(inc.next()){ ... }
    Loops through the 10 (or fewer, if less than 10 match) retrieved records.

Why is this powerful? It’s a performance booster! Instead of fetching potentially thousands of records and then having your script or UI logic pick out the top 10, the database does the heavy lifting, sending over only what’s absolutely necessary. Less data over the wire means faster scripts and a more responsive instance.

Beyond the Basics: Advanced Sorting Tips & Tricks

While `orderBy()` and `orderByDesc()` are straightforward, there are nuances that can make your scripts even more sophisticated.

Multiple Order By Clauses: Secondary Sorting

What if you need to sort by one field, and then, for records that have the same value in that first field, sort by a second field? No problem! You can chain multiple orderBy() or orderByDesc() calls.


var inc = new GlideRecord('incident');
inc.addQuery('active', true);
inc.orderByDesc('priority');    // Primary sort: Critical first
inc.orderBy('opened_at');       // Secondary sort: For incidents with the same priority, sort by oldest first
inc.query();

gs.print('--- Active Incidents by Priority (Desc) then Opened At (Asc) ---');
while(inc.next()){
    gs.print('Priority: ' + inc.priority.getDisplayValue() + ', Opened: ' + inc.opened_at + ', Number: ' + inc.number + ' ' + inc.short_description);
}

In this example, all priority 1 incidents would come first. Among those priority 1 incidents, they would then be sorted by their opened_at date from oldest to newest. After all priority 1 incidents, priority 2 incidents would follow, again sorted by opened_at, and so on.

Sorting by Display Values (Advanced Consideration)

Sometimes you need to sort by the *display value* of a reference field or a choice list, rather than its raw internal value. For instance, you might want to sort incidents by the display name of the assigned group, not its sys_id. GlideRecord offers orderByDisplayValue() and orderByDescDisplayValue() for this purpose.


// Example using orderByDisplayValue (not directly from reference but good for human-like article)
var inc = new GlideRecord('incident');
inc.addQuery('active', true);
inc.orderByDisplayValue('assigned_to'); // Sorts by the display name of the assigned user
inc.query();

gs.print('--- Active Incidents by Assigned To (Display Name) ---');
while(inc.next()){
    gs.print('Assigned To: ' + inc.assigned_to.getDisplayValue() + ', Number: ' + inc.number);
}

This is particularly useful when the display values are more intuitive for sorting than the underlying technical values.

Common Pitfalls and Troubleshooting

Even with seemingly simple methods, things can sometimes go awry. Here’s a look at common issues and how to troubleshoot them.

1. Incorrect Field Name

Problem: Your script runs, but the data isn’t sorted, or you get an error that a field doesn’t exist.

Cause: You’ve misspelled the field name or used the label instead of the actual column name. Remember, GlideRecord methods usually expect the database column name (e.g., short_description, not “Short Description”).

Solution: Double-check the field name. The easiest way is to navigate to the table’s dictionary entry (e.g., incident.LIST -> Right-click header -> Configure -> Table -> Columns) or simply right-click the field label on a form and select “Show XML” or “Configure Dictionary.”

2. Sorting on a Non-Indexed Field (Performance Impact)

Problem: Your script is correctly sorted but takes an unusually long time to execute, especially with a large number of records.

Cause: Sorting on a field that isn’t indexed in the database can be very resource-intensive for the database. When a field is indexed, the database can quickly look up and sort values; without an index, it has to scan the entire table.

Solution: For critical or frequently sorted fields, consider adding a database index. This is an administrative task, typically performed by a ServiceNow admin or developer with proper justification. Be cautious, as too many indexes can slow down writes (inserts/updates).

3. Unexpected Case Sensitivity

Problem: “Apple” and “apple” are sorted separately, or not as expected.

Cause: Database collation settings can influence how strings are sorted, including case sensitivity.

Solution: ServiceNow’s database typically handles sorting in a case-insensitive manner for most default string fields in English locales. However, if you’re dealing with specific language settings or custom configurations, this might vary. If strict case-insensitive sorting is critical, you might need to use client-side array sorting after data retrieval or normalize values during your query (though this adds complexity).

4. Order of Operations with addQuery() and orderBy()

Problem: Your query seems correct, but the sorting isn’t applied to the expected subset of data.

Cause: While the order of chaining addQuery() and orderBy() typically doesn’t matter (GlideRecord intelligently builds the query before execution), it’s good practice to place your addQuery() statements before orderBy() calls for readability and logical flow. The database processes the WHERE clause (from addQuery) *before* the ORDER BY clause.

Solution: Ensure your addQuery() statements correctly filter the data you intend to sort. The sorting will always apply to the records *that pass the query filters*.

5. Not Looping Through All Results When Expecting a Full Set

Problem: You expect 100 records but only get 10.

Cause: You might have inadvertently left a setLimit(10) from a previous test or example in your current script.

Solution: Always review your entire GlideRecord chain. If you’re not using setLimit(), make sure it’s not present. If you are, ensure the limit is what you truly intend.

Interview Relevance: Why This Matters for Your Career

If you’re aspiring to be a ServiceNow developer or administrator, understanding GlideRecord methods like orderBy() and orderByDesc() isn’t just a nicety – it’s a fundamental requirement. Interviewers frequently use questions involving these methods to gauge a candidate’s practical scripting skills and understanding of data manipulation within the platform.

What interviewers look for:

  • Basic Syntax: Can you correctly write a simple GlideRecord query with sorting?
  • Use Case Understanding: Do you know when to use orderBy() versus orderByDesc()? Can you articulate a real-world scenario for each? (e.g., “I’d use orderByDesc('opened_at') to show the most recent incidents on a dashboard.”)
  • Problem Solving: Given a scenario (e.g., “How would you get the top 5 most critical incidents?”), can you construct the complete query including addQuery(), orderBy()/orderByDesc(), and setLimit()?
  • Performance Awareness: Do you understand the implications of sorting large datasets, or the benefits of setLimit() for performance? (This shows a more mature understanding of scripting best practices).
  • Debugging Skills: If presented with a script that’s not sorting correctly, can you identify potential issues (e.g., wrong field name, case sensitivity)?

Mastering these methods not only makes you a more effective developer but also demonstrates a solid foundational knowledge that will impress potential employers.

Wrapping It Up

There you have it! The seemingly simple act of sorting data, when applied through GlideRecord’s orderBy() and orderByDesc() methods, becomes a powerful tool for clarity, efficiency, and user satisfaction within ServiceNow. Whether you’re arranging a list alphabetically, chronologically, or by numerical value, these methods give you precise control over your data’s presentation.

Remember that setLimit() is an excellent companion, helping you manage the volume of data retrieved, which is crucial for building performant and user-friendly solutions. Keep practicing these concepts, experiment with different fields and combinations, and you’ll soon find yourself crafting elegant and effective scripts that make your ServiceNow instance sing!

Happy sorting, and keep coding!


Scroll to Top