Mastering Data Filtering: Unveiling Records with addNotNullQuery in ServiceNow

Hey there, fellow ServiceNow enthusiast! Ever found yourself needing to sift through mountains of data on the platform, but only caring about records where a certain piece of information actually exists? Maybe you want to find all incidents that have been assigned to someone, or perhaps tasks that actually have a due date set. If so, you’re in the right place!

ServiceNow’s powerful GlideRecord API is our go-to for server-side database interactions, allowing us to perform CRUD (Create, Read, Update, Delete) operations without ever writing a line of SQL. Among its vast array of methods, `addNotNullQuery()` stands out as a deceptively simple yet incredibly potent tool for precise data filtering. Today, we’re going to dive deep into this method, exploring its utility, practical applications, and how it can make your ServiceNow scripts cleaner, more efficient, and more robust.

Whether you’re a seasoned developer or just starting your journey with ServiceNow scripting, understanding methods like `addNotNullQuery()` is fundamental to building scalable and intelligent solutions. So, let’s roll up our sleeves and uncover how to master this essential GlideRecord method!

The Powerhouse: Understanding GlideRecord in ServiceNow

Before we pinpoint our focus on `addNotNullQuery()`, it’s vital to have a solid grasp of its parent – the GlideRecord API. Think of GlideRecord as the bridge between your server-side JavaScript code and the underlying ServiceNow database. It’s a specialized Java class (don’t worry, you’re still writing JavaScript!) that abstracts away the complexities of direct database interaction, giving us a safe, efficient, and ServiceNow-native way to query and manipulate data.

In the ServiceNow world, direct SQL queries are a no-go for security and performance reasons. Instead, we lean heavily on GlideRecord methods. This API is truly the backbone of most server-side customizations, from business rules and script includes to UI actions and scheduled jobs. It’s where the magic happens when you need to fetch user details, update an incident’s state, or create a new task programmatically.

Why GlideRecord? The Untapped Benefits

• Abstracts SQL: You don’t need to be a SQL guru. GlideRecord translates your JavaScript calls into optimized database queries behind the scenes.
• Server-Side Execution: It runs directly on the ServiceNow server, making it fast and secure for large data operations.
• CRUD Operations: Provides methods for every database operation you could need – querying existing records, inserting new ones, updating fields, and deleting entries.
• Foundation for Scripting: Nearly every complex server-side script in ServiceNow will leverage GlideRecord to interact with data. Mastering it is key to advanced ServiceNow scripting and low-code development.

Essential GlideRecord Querying Techniques: A Quick Refresh

Before we zero in on `addNotNullQuery()`, let’s quickly revisit some fundamental GlideRecord querying methods. Understanding these will help us appreciate the nuanced power of our target method and how it fits into the broader querying ecosystem.

The Basic Loop: `query()`, `next()`, and `while()`

At its core, retrieving records involves creating a GlideRecord object, initiating a query, and then iterating through the results. This is often the first thing new developers learn:

var incidentRecords = new GlideRecord('incident'); // Instantiate GlideRecord for the 'incident' table
incidentRecords.query(); // Execute the query (fetches ALL incidents if no filters are added)

// Loop through each record found by the query
while (incidentRecords.next()) {
    gs.info('Incident Number: ' + incidentRecords.number + ' | Short Description: ' + incidentRecords.short_description);
}
    

This simple pattern is your bread and butter for any GlideRecord operation that involves processing multiple records.

Pinpointing Data: `addQuery()` for Specific Filters

Fetching *all* records is rarely practical. We need to filter! The `addQuery()` method is your workhorse for applying conditions. It allows you to specify a field, an operator (optional), and a value to narrow down your results:

var highPriorityIncidents = new GlideRecord('incident');
highPriorityIncidents.addQuery('active', true); // Condition 1: Only active incidents
highPriorityIncidents.addQuery('priority', '=', 1); // Condition 2: Priority is 1 (Critical)
// You can also use the shorthand: highPriorityIncidents.addQuery('priority', 1);

highPriorityIncidents.query();
gs.info('Critical active incidents found: ' + highPriorityIncidents.getRowCount()); // Get total count

while (highPriorityIncidents.next()) {
    gs.print('Incident: ' + highPriorityIncidents.number + ' | State: ' + highPriorityIncidents.state.getDisplayValue());
}
    

You can stack multiple `addQuery()` calls, and GlideRecord will combine them with an `AND` operator, meaning all conditions must be met for a record to be returned.

Advanced Filtering: `addEncodedQuery()` for Complex Conditions

For more intricate filtering, especially when you have many `OR` conditions or complex groupings, `addEncodedQuery()` comes to the rescue. You can build these complex queries directly from any list view in ServiceNow (by right-clicking the filter breadcrumbs and selecting “Copy query”).

var specificIncidents = new GlideRecord('incident');
// Example encoded query: active = true AND (priority = 1 OR priority = 2) AND category = 'software'
var complexQuery = 'active=true^category=software^ORpriority=1^ORpriority=2';
specificIncidents.addEncodedQuery(complexQuery);
specificIncidents.query();

gs.info('Incidents matching complex encoded query: ' + specificIncidents.getRowCount());
while (specificIncidents.next()) {
    gs.print('Incident: ' + specificIncidents.number + ' | Category: ' + specificIncidents.category.getDisplayValue());
}
    

This method is incredibly powerful for mirroring intricate UI filters in your business rules or script includes, ensuring consistent data retrieval.

Shining a Light on Missing Data: Introducing `addNotNullQuery()`

Now, let’s talk about our star player: `addNotNullQuery()`. As the name subtly suggests, this method is designed to find records where a specified field *is not* null. In other words, it helps you identify records that actually have a value (any value!) in a particular field. This is a crucial distinction and immensely useful for ensuring data quality, reporting, and targeted automation.

What is `addNotNullQuery()`?

At its heart, `addNotNullQuery(fieldName)` filters your GlideRecord results to include only those records where the `fieldName` you provide contains some data. It’s the opposite of `addNullQuery()`, which we’ll briefly touch upon later. For example, if you’re looking at a table of tasks, and you only want to see tasks where a ‘short_description’ has been filled out, `addNotNullQuery(‘short_description’)` is your direct path.

Why is this important? In many real-world scenarios, a field being empty or null can mean different things. Sometimes it means “not applicable,” but often it means “data is missing” or “this record is still unassigned/unclassified.” Being able to reliably query for records where a field *does* have a value is critical for many business processes and ITSM, CSM, or HRSD workflows.

The Syntax: Simple and Direct

The beauty of `addNotNullQuery()` lies in its simplicity. You only need to provide the name of the field you want to check:

// Syntax: gr.addNotNullQuery('field_name');

var gr = new GlideRecord('incident');
gr.addNotNullQuery('assigned_to'); // This will find incidents where the 'assigned_to' field is NOT null
gr.query();
while (gr.next()) {
    gs.info('Incident ' + gr.number + ' is assigned to: ' + gr.assigned_to.getDisplayValue());
}
    

It’s straightforward, clear, and highly effective.

Why Not Null? Real-World Scenarios Where It Shines

Let’s consider a few practical situations where `addNotNullQuery()` becomes indispensable:

• Assigned Tasks: You need to generate a report of all active tasks that have actually been assigned to someone. You wouldn’t want to include tasks sitting in an unassigned queue.
• Tasks with Deadlines: Find all project tasks that have a specific ‘due_date’ set, perhaps to send reminders or escalate overdue items.
• Categorized Records: Identify all knowledge articles that have been assigned a ‘kb_category’, ensuring content is properly organized and discoverable.
• Records with Attachments: While not a direct field check, you might combine this with a join query to find records with an attachment record (where the ‘table_sys_id’ in `sys_attachment` is not null).
• Validated Configurations: Retrieve Configuration Items (CIs) that have a ‘managed_by’ group or ‘support_group’ defined, indicating they are actively being managed.
• Parent/Child Relationships: Find child records that are correctly linked to a parent record (e.g., incidents that have a ‘parent’ field populated).

In all these cases, you’re looking for the *presence* of data, not just any data, but specifically the non-absence of it. This is where `addNotNullQuery()` shines.

`addNotNullQuery()` in Action: Practical Examples and Use Cases

Let’s solidify our understanding with some practical, copy-and-paste-ready examples you can try in your ServiceNow instance’s “Scripts – Background” module or within your server-side scripts.

Scenario 1: Finding All Incidents with an Assigned Agent

A common request is to list incidents that are actively being worked on by an individual. This implies the `assigned_to` field must be populated.

// Goal: Find all active incidents that have an assigned user.
gs.info('--- Incidents with an Assigned Agent ---');
var incidentsWithAssignee = new GlideRecord('incident');
incidentsWithAssignee.addActiveQuery(); // Only consider active incidents
incidentsWithAssignee.addNotNullQuery('assigned_to'); // Filter for incidents where 'assigned_to' is not empty
incidentsWithAssignee.query();

var countAssigned = incidentsWithAssignee.getRowCount();
gs.info('Found ' + countAssigned + ' active incidents with an assigned agent.');

while (incidentsWithAssignee.next()) {
    gs.print('Incident ' + incidentsWithAssignee.number + 
             ' is assigned to: ' + incidentsWithAssignee.assigned_to.getDisplayValue() + 
             ' (Short Description: ' + incidentsWithAssignee.short_description + ')');
}
gs.info('--------------------------------------');
    

This script is fantastic for dashboard widgets, performance reports, or driving notifications to assignment groups about their currently assigned workload.

Scenario 2: Identifying Tasks with a Defined Due Date

For effective task management and compliance, it’s often crucial to track tasks that have a specific deadline. Let’s find active, overdue tasks that actually have a due date set.

// Goal: Identify active tasks that have a due date set and are currently overdue.
gs.info('--- Overdue Active Tasks with a Specified Due Date ---');
var overdueTasks = new GlideRecord('task');
overdueTasks.addActiveQuery(); // Must be an active task
overdueTasks.addNotNullQuery('due_date'); // Crucially, ensure a due date exists
overdueTasks.addQuery('due_date', '<', gs.daysAgo(0)); // Filter for tasks whose due date is in the past (overdue)
overdueTasks.orderBy('due_date'); // Order by due date for better readability
overdueTasks.query();

var countOverdue = overdueTasks.getRowCount();
gs.info('Found ' + countOverdue + ' active, overdue tasks with a due date.');

while (overdueTasks.next()) {
    gs.print('Task: ' + overdueTasks.number + 
             ' | Due: ' + overdueTasks.due_date + 
             ' | Short Description: ' + overdueTasks.short_description);
}
gs.info('----------------------------------------------------');
    

This kind of query is perfect for scheduled jobs that send out daily overdue task reminders or for reports highlighting potential bottlenecks in a workflow.

Scenario 3: Combining `addNotNullQuery` with Other Filters for Precision

`addNotNullQuery()` is rarely used in isolation in complex workflow automation. It truly shines when combined with other `addQuery()` methods to create highly precise data sets.

// Goal: Find the 5 most recently updated published knowledge articles that have a category defined.
gs.info('--- Recently Published Knowledge Articles with a Category ---');
var categorizedKnowledge = new GlideRecord('kb_knowledge');
categorizedKnowledge.addQuery('workflow_state', 'published'); // Only published articles
categorizedKnowledge.addNotNullQuery('kb_category'); // Ensure a category is set for proper classification
categorizedKnowledge.orderByDesc('sys_updated_on'); // Get the most recently updated first
categorizedKnowledge.setLimit(5); // Limit to the top 5 for a quick snapshot
categorizedKnowledge.query();

gs.info('Top 5 recently published articles with a category:');
while (categorizedKnowledge.next()) {
    gs.print('Article ' + categorizedKnowledge.number + 
             ' - "' + categorizedKnowledge.short_description + '" ' + 
             '(Category: ' + categorizedKnowledge.kb_category.getDisplayValue() + 
             ', Last Updated: ' + categorizedKnowledge.sys_updated_on + ')');
}
gs.info('----------------------------------------------------------');
    

This example demonstrates how `addNotNullQuery()` contributes to data integrity and ensures that only well-classified, relevant information is retrieved for reporting or knowledge base management.

The Counterpart: `addNullQuery()` and When to Use It

While we're focusing on `addNotNullQuery()`, it's worth briefly mentioning its counterpart: `addNullQuery()`. As you might guess, this method does the exact opposite – it retrieves records where the specified field *is* null or empty.

// Goal: Find all active incidents that are currently unassigned.
gs.info('--- Active Incidents Without an Assignee ---');
var unassignedIncidents = new GlideRecord('incident');
unassignedIncidents.addActiveQuery(); // Only active incidents
unassignedIncidents.addNullQuery('assigned_to'); // Filter for incidents where 'assigned_to' IS empty/null
unassignedIncidents.query();

var countUnassigned = unassignedIncidents.getRowCount();
gs.info('Found ' + countUnassigned + ' active incidents with NO assigned agent.');

while (unassignedIncidents.next()) {
    gs.print('Unassigned Incident: ' + unassignedIncidents.number + ' | Priority: ' + unassignedIncidents.priority.getDisplayValue());
}
gs.info('------------------------------------------');
    

This is incredibly useful for identifying records that need attention, like unassigned tasks, incomplete configuration items, or users without an assigned manager. Both `addNullQuery()` and `addNotNullQuery()` are vital tools for maintaining data quality and driving proactive actions within your ServiceNow instance.

Best Practices and Performance Considerations

When working with GlideRecord, especially in a production ServiceNow environment, it's not just about getting the script to work, but also about making it work *well*. Here are some best practices and performance tips:

• Always Test in Sub-Production: This cannot be stressed enough. Always develop and test your GlideRecord queries in a non-production instance first. An incorrectly constructed query can lead to performance degradation or, worse, unintended data modifications or loss.
• Be Mindful of Large Datasets: If your query is expected to return thousands or millions of records, consider using `setLimit()` to fetch only a manageable subset if your process doesn't require all of them. For instance, `setLimit(100)` is great for quick debugging or displaying top results.
• Optimize Your Queries: While `addNotNullQuery()` is generally efficient, combining it with many other complex queries can impact performance. Ensure that fields you frequently query (including those checked for null/not null) are indexed in the database for faster lookups. ServiceNow handles many core field indexes automatically, but for custom fields, you might need to consider this.
• Understand "Null" vs. Empty Strings: A common pitfall! `addNotNullQuery()` looks for actual database NULL values. However, sometimes a field might *appear* empty in the UI but actually contain an empty string (e.g., `''`) rather than a true `null`. For String fields, `addNotNullQuery()` will treat an empty string as a non-null value because it's still a value. If you need to check for *both* nulls and empty strings, you might need a more elaborate query, perhaps using `addQuery(fieldName, '!=', '')` in conjunction with `addNotNullQuery()` or an encoded query like `fieldNameISNOTEMPTY`.
• Use `gs.getLog()` or `gs.info()`: During development, liberally use `gs.info()` or `gs.print()` to output what your script is doing, what values it's getting, and the results of your queries. This is invaluable for debugging.

Troubleshooting Common Issues with `addNotNullQuery()`

Even with simple methods, issues can arise. Here's a quick guide to common pitfalls and how to troubleshoot them when using `addNotNullQuery()`:

• Incorrect Field Name:

  Symptom: Your query returns zero results, or you get an error indicating an invalid field.
  Solution: Double-check the exact database name of the field. Navigate to the table configuration (e.g., `incident.LIST` -> right-click header -> Configure -> Table) or list view (right-click column header -> Configure Dictionary) to confirm the field name (e.g., `assigned_to`, not `Assigned To`). Typographical errors are common culprits.

  
  var inc = new GlideRecord('incident');
  inc.addNotNullQuery('assign_to'); // Typo here! Should be 'assigned_to'
  inc.query();
  gs.info('Count with typo: ' + inc.getRowCount()); // Will likely be 0
  gs.info('Actual encoded query: ' + inc.getEncodedQuery()); // Check what SQL is being generated
              

• Misunderstanding Null vs. Empty String:

  Symptom: You expect records to be returned by `addNotNullQuery()`, but they aren't, or vice-versa, because a field that *looks* empty is actually an empty string.
  Solution: As discussed in best practices, `addNotNullQuery()` looks for true database NULLs. If a string field has `''` (an empty string), `addNotNullQuery()` will consider it "not null." If you want to exclude records with *either* `null` *or* an empty string, you often need to combine queries. For example, for a string field:
  gr.addQuery('my_string_field', '!=', ''); (This will exclude both actual nulls and empty strings for most field types, effectively making it "IS NOT EMPTY").
  Or, for a very specific case where you only want non-empty string *values*:
  gr.addNotNullQuery('my_string_field');
gr.addQuery('my_string_field', '!=', '');
  This combination ensures the field is not null *and* not an empty string. For non-string fields (like date/time or reference fields), `addNotNullQuery()` behaves as expected for actual `null` values.

• Permissions Issues:

  Symptom: Your script runs, but returns 0 results, even when you know data exists.
  Solution: The user context under which your script runs (e.g., system, specific user, background script user) might not have read access to the table or the specific field you're querying. Check ACLs (Access Control Lists) for the table and field. If running in Scripts - Background, ensure you have appropriate roles (like `admin` or `script_runner`).

• Data Type Mismatches (in combined queries):

  Symptom: While `addNotNullQuery()` itself is robust, combining it with other `addQuery()` conditions that have incorrect data types (e.g., comparing a string to an integer) can cause the entire query to fail or return unexpected results.
  Solution: Always ensure your query values match the field's data type. For example, compare a date field to a date value, a reference field to a sys_id, and an integer field to an integer. If in doubt, use `gs.print(gr.getEncodedQuery())` to see the actual query string being passed to the database, which can often reveal subtle issues.

`addNotNullQuery()` in the Interview Room: Why It Matters

If you're interviewing for a ServiceNow developer or administrator role, understanding GlideRecord is paramount. Questions about filtering data, ensuring data quality, and writing efficient server-side scripts are common. Demonstrating proficiency with `addNotNullQuery()` can set you apart.

• Precision in Data Retrieval: Knowing this method shows you understand how to precisely target data, which is crucial for building accurate reports, driving specific workflows, and ensuring data integrity.
• Problem-Solving for Data Quality: It highlights your ability to address real-world data challenges, such as identifying incomplete records or ensuring essential fields are populated before processing.
• Efficiency and Best Practices: Using specialized methods like `addNotNullQuery()` is often more efficient and readable than trying to construct complex `addQuery()` statements or `addEncodedQuery()` strings for the same purpose. It showcases your familiarity with scripting best practices.
• Foundational Knowledge: It's a key part of the GlideRecord API. A candidate who understands and can articulate the use cases for `addNotNullQuery()` demonstrates a solid grasp of server-side scripting fundamentals, a critical skill for any ServiceNow platform role.

Being able to discuss when and why you'd use `addNotNullQuery()` versus, say, `addQuery('field', '!=', '')` (and understanding the subtle differences) demonstrates a deeper, more nuanced understanding of GlideRecord capabilities and database interaction on the ServiceNow platform.

Conclusion

And there you have it! `addNotNullQuery()` might seem like a small piece of the extensive GlideRecord puzzle, but its utility for efficient and accurate data filtering cannot be overstated. From ensuring task assignments to validating knowledge article categories, this method empowers you to write cleaner, more targeted, and more performant server-side scripts in ServiceNow.

By understanding its purpose, mastering its simple syntax, and applying it with best practices, you'll significantly enhance your ability to interact with the ServiceNow database, leading to more robust platform analytics, reliable ITOM automations, and overall better digital transformation solutions. Keep experimenting, keep learning, and keep building amazing things on the ServiceNow platform!