Mastering Unique Value Retrieval in ServiceNow: A Developer’s Essential Guide

In the vast ocean of data that resides within every ServiceNow instance, finding specific pieces of information is often just the beginning. More often than not, you’ll need to extract not just data, but unique data. Picture this: you need a list of all distinct categories that have ever been used in incident records, or perhaps a list of all unique departments associated with active users. This isn’t just a niche request; it’s a fundamental requirement for accurate reporting, populating dynamic dropdowns, ensuring data integrity, and building robust applications.

While ServiceNow provides powerful tools for database interaction, directly running SQL queries isn’t an option. Instead, we lean on the platform’s native JavaScript APIs. This article will guide you through the intricacies of retrieving unique values in ServiceNow, focusing on the server-side tools that make it possible. We’ll explore the foundational GlideRecord, the specialized GlideAggregate, and practical scripting techniques to ensure your data is not just retrieved, but truly distinct.

The ServiceNow Data Layer: Your Gateway with Glide API

ServiceNow developers frequently leverage the Glide API to extend and customize the platform’s default behavior. These Glide classes are your programmatic interface, offering a flexible way to interact with the underlying database without ever having to write a single line of SQL. Instead, you’re writing JavaScript, which the platform then translates into optimized database operations.

Client-Side vs. Server-Side Scripting: Knowing Your Tools

Before diving into unique value retrieval, it’s crucial to understand the two main theaters of operation for Glide APIs:

• Client-Side APIs (e.g., GlideForm, GlideUser, GlideAjax): These run directly in the user’s browser, typically interacting with forms, UI elements, and user preferences. They are primarily for enhancing the user experience and form interactions.
• Server-Side APIs (e.g., GlideRecord, GlideSystem, GlideAggregate): These execute on the ServiceNow server. They are your workhorses for database operations, backend logic, integrations, and anything that requires extensive data processing or interaction with the core system. Retrieving unique values fundamentally involves interacting with the database, making server-side scripting your go-to.

Diving Deep with GlideRecord: Your Foundation for Data Interaction

At the heart of server-side data manipulation in ServiceNow lies the GlideRecord API. If you’re going to interact with any table on the platform, whether it’s to read, create, update, or delete records (CRUD operations), GlideRecord is your primary tool. It’s a special JavaScript class that abstracts away the complexities of direct database interaction, allowing you to work with rows and columns using familiar object-oriented paradigms.

Think of GlideRecord as a bridge. On one side, you have your JavaScript code; on the other, the relational database. GlideRecord takes your JavaScript instructions, translates them into efficient SQL queries, executes them against the database, and then brings the results back to your script as a convenient object you can iterate through.

// Basic GlideRecord instantiation
var grIncident = new GlideRecord('incident');
// 'incident' is the table name we want to interact with

Essential GlideRecord Methods for Data Retrieval

To set the stage for finding unique values, let’s quickly review some critical GlideRecord methods you’ll use constantly:

• new GlideRecord('table_name'): Initializes a new GlideRecord object for a specific table. This is always your starting point.
• addQuery('field_name', 'operator', 'value') / addQuery('field_name', 'value'): Adds a query condition. You can specify a field, an operator (like ‘=’, ‘!=’, ‘STARTSWITH’, ‘CONTAINS’), and a value. If no operator is specified, it defaults to ‘=’.
• addEncodedQuery('encoded_query_string'): Allows you to paste in complex queries generated from list views (e.g., active=true^category=software^priority=1). Extremely powerful for quickly building intricate queries.
• query(): Executes the query you’ve built. Without this, your addQuery calls are just declarations.
• next(): Advances the GlideRecord pointer to the next record in the result set. It returns true if there’s another record, false otherwise. This is used within a while loop to process each record.
• getValue('field_name'): Retrieves the actual database value of a specified field for the current record.
• getDisplayValue('field_name'): Retrieves the user-friendly display value of a field (e.g., “Software” instead of “software_value” for a choice list, or “System Administrator” instead of “admin” for a reference field).
• getRowCount(): Returns the total number of records found by your query.
• setLimit(number): Restricts the number of records returned by the query. Essential for performance with large datasets.
• orderBy('field_name') / orderByDesc('field_name'): Sorts the query results in ascending or descending order based on a specified field.

Here’s a simple example of how these methods work together to retrieve incident numbers:

var grIncident = new GlideRecord('incident');
grIncident.addQuery('active', true);
grIncident.addQuery('priority', 1);
grIncident.setLimit(5); // Get only the first 5 active, critical incidents
grIncident.orderByDesc('number'); // Order by incident number descending
grIncident.query(); // Execute the query

gs.info('--- Active Critical Incidents (Top 5) ---');
while (grIncident.next()) {
    gs.info('Incident Number: ' + grIncident.number + ', Short Description: ' + grIncident.short_description);
}

The Core Challenge: Retrieving Truly Unique Field Values

Now, let’s get to the crux of our topic. You’ve seen how to query and iterate through records, but how do we get a list of distinct values from a particular field? For instance, if your incident table has 100 records but only 5 unique categories, how do you get just those 5 categories?

It’s important to clarify one point early on: the GlideRecord method getUniqueValue(). This method returns the unique identifier (typically the sys_id) of the current record in your GlideRecord object. It does not provide a list of distinct values from a field across multiple records. That’s a common misconception!

Method 1: Scripting with GlideRecord and Arrays (The Manual Way)

This is often the first approach developers consider, and it’s perfectly valid for smaller datasets. The idea is simple: query all relevant records, iterate through them, extract the value of the field you’re interested in, and then add it to an array only if it doesn’t already exist in that array. This effectively creates a unique list.

Example: Get Unique Categories from the Incident Table

var uniqueCategories = [];
var grIncident = new GlideRecord('incident');
grIncident.addNotNullQuery('category'); // Only get records where category is not empty
grIncident.query();

while (grIncident.next()) {
    var categoryValue = grIncident.getValue('category'); // Get the internal value
    if (uniqueCategories.indexOf(categoryValue) === -1) { // Check if value is already in the array
        uniqueCategories.push(categoryValue); // Add if it's unique
    }
}

gs.info('--- Unique Incident Categories (Internal Values) ---');
for (var i = 0; i < uniqueCategories.length; i++) {
    gs.info(uniqueCategories[i]);
}

// If you want display values:
var uniqueCategoryDisplayValues = [];
var grIncidentDisplay = new GlideRecord('incident');
grIncidentDisplay.addNotNullQuery('category');
grIncidentDisplay.query();

while (grIncidentDisplay.next()) {
    var categoryDisplayValue = grIncidentDisplay.getDisplayValue('category'); // Get the display value
    if (uniqueCategoryDisplayValues.indexOf(categoryDisplayValue) === -1) {
        uniqueCategoryDisplayValues.push(categoryDisplayValue);
    }
}
gs.info('--- Unique Incident Categories (Display Values) ---');
for (var j = 0; j < uniqueCategoryDisplayValues.length; j++) {
    gs.info(uniqueCategoryDisplayValues[j]);
}

Pros of the Array Method:

• Simplicity: Easy to understand and implement for JavaScript developers.
• Flexibility: You have fine-grained control over how uniqueness is determined (e.g., case-sensitive vs. case-insensitive checks).

Cons of the Array Method:

• Performance for Large Datasets: For tables with thousands or millions of records, querying all records and then iterating through them in JavaScript can be very slow and memory-intensive, potentially leading to timeouts or poor performance.
• Client-side Use (with caution): While shown here on the server, a similar approach could be attempted client-side via GlideAjax, but the performance implications are even more severe as it would require multiple server calls or large data transfers. Generally, for distinct values, stick to server-side.

Method 2: The Power of GlideAggregate (The Database-Optimized Way)

When dealing with large datasets, the array-based method quickly loses its appeal. This is where GlideAggregate steps in. GlideAggregate is a server-side API specifically designed for performing aggregate functions on data (like COUNT, SUM, MIN, MAX, AVG). Crucially for our task, it also supports the equivalent of a SQL GROUP BY clause, which is precisely what you need to get a list of distinct values efficiently.

When you use groupBy('field_name') with GlideAggregate, the platform executes a highly optimized database query that returns only the unique values for that field, along with any aggregate calculations you might have requested (though we often just need the distinct field itself). This delegates the heavy lifting to the database, which is far more efficient.

Example: Get Unique Categories from the Incident Table using GlideAggregate

var uniqueCategoriesAggregate = [];
var gaIncident = new GlideAggregate('incident');
gaIncident.addNotNullQuery('category'); // Filter out empty categories
gaIncident.groupBy('category'); // Group by the category field to get unique values
gaIncident.query();

gs.info('--- Unique Incident Categories (Internal Values via GlideAggregate) ---');
while (gaIncident.next()) {
    uniqueCategoriesAggregate.push(gaIncident.category.getValue()); // Or just gaIncident.category
}

for (var i = 0; i < uniqueCategoriesAggregate.length; i++) {
    gs.info(uniqueCategoriesAggregate[i]);
}

// To get display values using GlideAggregate, it's slightly different
var uniqueCategoryDisplayValuesAggregate = [];
var gaIncidentDisplay = new GlideAggregate('incident');
gaIncidentDisplay.addNotNullQuery('category');
gaIncidentDisplay.groupBy('category');
gaIncidentDisplay.query();

gs.info('--- Unique Incident Categories (Display Values via GlideAggregate) ---');
while (gaIncidentDisplay.next()) {
    // When grouping, the field itself represents one of the grouped unique values.
    // To get its display value, you can often directly access it.
    uniqueCategoryDisplayValuesAggregate.push(gaIncidentDisplay.category.getDisplayValue());
}

for (var j = 0; j < uniqueCategoryDisplayValuesAggregate.length; j++) {
    gs.info(uniqueCategoryDisplayValuesAggregate[j]);
}

Pros of the GlideAggregate Method:

• Performance: Significantly faster and more memory-efficient for large datasets because the database handles the distinct grouping.
• Scalability: Better suited for production environments where performance is critical.
• Conciseness: Often requires less JavaScript code to achieve the same result compared to manual array manipulation.

Cons of the GlideAggregate Method:

• Learning Curve: Slightly more specialized API than basic GlideRecord.
• Less Control over “Uniqueness”: Database-level distinctness is usually what you want, but if you needed extremely custom logic for what defines “unique” (e.g., ignoring specific substrings), the array method might offer more programmatic control.

Method 3: Understanding getUniqueValue() (for Single Record Sys_ID)

As mentioned earlier, getUniqueValue() is a GlideRecord method, but it serves a different purpose than retrieving a list of distinct field values. It returns the sys_id (the unique identifier) of the current record you are working with in your GlideRecord object.

This is incredibly useful when you’ve just created a new record and need its sys_id, or when you’ve queried a specific record and need to reference it elsewhere. It confirms the identity of that single record.

Example: Getting the Sys_ID of a New Incident

var grNewIncident = new GlideRecord('incident');
grNewIncident.initialize(); // Prepares a new, empty record
grNewIncident.short_description = 'My brand new incident.';
grNewIncident.category = 'network';
var newIncidentSysId = grNewIncident.insert(); // Inserts the record and returns its sys_id

gs.info('Newly created incident Sys_ID: ' + newIncidentSysId);

// You can also call getUniqueValue() on the GlideRecord object after next()
var grSingleIncident = new GlideRecord('incident');
grSingleIncident.setLimit(1);
grSingleIncident.query();
if (grSingleIncident.next()) {
    gs.info('Sys_ID of the first incident: ' + grSingleIncident.getUniqueValue());
}

So, while crucial for managing individual records, remember that getUniqueValue() is not the tool for generating a distinct list of values from a field across many records.

Practical Applications and Best Practices

Now that we’ve demystified how to retrieve unique values, let’s look at where you’ll apply this knowledge and how to do it effectively.

Real-World Scenarios

• Dynamic Dropdowns/Choice Lists: Populate a dropdown on a form with only the unique values from a specific field in another table. For example, a “Requested For” field might only show unique department names from the User table that have active employees.
• Reporting and Analytics: Generate unique lists for reports, dashboards, or data exports (e.g., “List all unique Configuration Item classes in use,” “Show all distinct locations with critical incidents”).
• Data Cleansing and Validation: Identify variations or inconsistencies in data entry (e.g., “Software,” “software,” “Software related”) that should ideally be standardized.
• Integration Mapping: When integrating with external systems, you might need to provide a list of unique values from a ServiceNow field to map against an external field.

Performance Considerations

Performance is paramount in ServiceNow development. Here are golden rules:

1. Filter Early and Aggressively: Use addQuery() and addEncodedQuery() to narrow down your result set as much as possible before the query() call. Less data retrieved means faster execution.
2. Use setLimit() When Appropriate: If you only need a sample or a specific number of records, use setLimit() to avoid fetching unnecessary data.
3. Prefer GlideAggregate for Large Datasets: For true distinct value retrieval across many records, GlideAggregate is almost always the superior choice due to its database-level optimization.
4. Avoid Unnecessary Loops: If you can achieve your goal with a single query and iteration, don’t break it into multiple steps.
5. gs.info() vs. gs.print(): In modern ServiceNow, prefer gs.info() for logging, as gs.print() is considered legacy. In production, avoid excessive logging that can impact performance.
6. Leverage Database Indexes: Ensure the fields you are querying and grouping by are indexed. ServiceNow typically manages this for standard fields, but custom fields might benefit from an index if queries are slow.

Security and Access Controls

Remember that all GlideRecord and GlideAggregate operations respect ServiceNow’s Access Control Lists (ACLs). The script will only be able to see and retrieve records that the user running the script has permission to view. If you’re running a script as an admin, it will see everything. If it’s running in the context of a less privileged user, it will only see what that user can see. This is a critical security feature, not a bug!

Troubleshooting Common Pitfalls

Even with the best intentions, things can go awry. Here are some common issues you might encounter and how to troubleshoot them:

“Why isn’t my unique list truly unique?”

• Case Sensitivity: If you’re manually adding to an array, 'Software' and 'software' will be treated as distinct values. Decide if you need to standardize case (e.g., .toLowerCase()) before adding to your array. GlideAggregate typically respects database collation, which might or might not be case-sensitive depending on your instance’s configuration.
• Leading/Trailing Spaces: Invisible characters like spaces can make values appear unique when they shouldn’t be. Use .trim() on your field values before comparison or addition to an array.
• Misspelled Field Names: A simple typo in getValue('catagory') instead of getValue('category') can lead to unexpected results (often null values being added repeatedly). Double-check your field names.
• Incorrect Filtering: If your addQuery or addEncodedQuery isn’t precise, you might be querying more (or fewer) records than intended, affecting the distinct set.

“My script is too slow!”

• Large Dataset with Array Method: This is the most common culprit. If you’re using GlideRecord + array for thousands of records, switch to GlideAggregate immediately.
• Inefficient Queries: Ensure your addQuery statements are effective. Are you querying unindexed fields? Are you missing critical filters that would reduce the dataset?
• Unnecessary gs.log() / gs.info(): While great for debugging, too many log statements (especially inside a loop) can significantly slow down execution. Remove them before moving to higher environments.
• Script-Background vs. Business Rule: If you’re running heavy scripts in a Business Rule or UI Action, consider if a Scheduled Job or a Script Include called by GlideAjax might be a better fit to avoid impacting user experience.

Acing the Interview: Unique Value Retrieval Questions

Questions about retrieving unique values are common in ServiceNow developer interviews because they test fundamental understanding of data interaction and performance best practices. Be prepared to discuss:

• “How would you get a list of all unique ‘Category’ values from the Incident table?”
  • Good Answer: Explain both the GlideRecord + array method (mentioning its limitations for large datasets) and the GlideAggregate method (highlighting its efficiency for scale). Provide clear code examples.
• “What is the difference between getUniqueValue() and retrieving a distinct list of field values?”
  • Excellent Answer: Clearly articulate that getUniqueValue() provides the sys_id of a single record, while a distinct list involves unique values of a specific field across many records, achieved via GlideAggregate or manual array methods.
• “When would you choose GlideAggregate over a regular GlideRecord loop for this task?”
  • Key Point: Emphasize performance and scalability for large datasets as the primary reason for GlideAggregate. Mention that for very small, controlled datasets, the array method might be simpler but generally discouraged for best practice.
• “What are the performance implications of your chosen method, and how would you optimize it?”
  • Highlight: Query filtering, setLimit(), use of GlideAggregate, avoiding excessive logging, and checking for field indexing.

Conclusion

Retrieving unique values in ServiceNow is a critical skill for any developer looking to build efficient, robust, and data-driven solutions. While the platform abstracts away direct SQL, understanding how to leverage GlideRecord and especially GlideAggregate for distinct data extraction is invaluable. Whether you’re populating dynamic dropdowns, creating insightful reports, or ensuring data quality, mastering these techniques will empower you to interact with your ServiceNow data layer like a seasoned pro.

Remember to always test in non-production environments, prioritize performance, and choose the right tool for the job. Now go forth and make your ServiceNow data work smarter for you!