How the IN Operator Works in ServiceNow (with Examples)

By /






Mastering the ‘IN’ Operator in ServiceNow: A Human Guide to Efficient Data Querying



Mastering the ‘IN’ Operator in ServiceNow: A Human Guide to Efficient Data Querying

Hey there, fellow ServiceNow enthusiast! Ever found yourself needing to filter records based on a whole bunch of values for a single field? You know, like “show me all incidents where the category is Software, Hardware, or Network” instead of just one? If your immediate thought was a series of clunky OR conditions, then you’re exactly where I was once upon a time. But fear not, because ServiceNow offers a much cleaner, more elegant solution: the mighty IN operator.

In the world of ServiceNow scripting, especially when you’re knee-deep in GlideRecord, efficiency and readability are golden. The IN operator is one of those unsung heroes that dramatically improves both. It allows you to check if a field’s value exists within a specified list of values, making your queries not just shorter, but also much easier to understand and maintain.

This isn’t just a dry technical dive into syntax. We’re going to explore why the IN operator is your friend, how it works under the hood, walk through practical, real-world scenarios, discuss performance, tackle common troubleshooting headaches, and even touch upon why this particular operator might pop up in your next ServiceNow interview. Ready to become a querying ninja? Let’s dive in!

The “Why”: Escaping the `OR` Clause Labyrinth

Before we dissect how the IN operator works, let’s appreciate the problem it solves. Imagine you’re building a report or a script that needs to identify all incidents related to “Software,” “Hardware,” or “Network” issues. Without the IN operator, your addQuery statements might look something like this:

var grIncident = new GlideRecord('incident');
grIncident.addQuery('category', '=', 'Software');
grIncident.addOrCondition('category', '=', 'Hardware');
grIncident.addOrCondition('category', '=', 'Network');
grIncident.query();
// ... loop through results

See how quickly that can become cumbersome? If you add another category, you need another addOrCondition. What if you have ten categories? Or twenty? Not only does the code get lengthy, but it also becomes harder to read and debug. This is where the IN operator shines like a beacon.

It provides a concise, SQL-like way to say, “Is the value of this field part of this collection?” This isn’t just about saving lines of code; it’s about writing more expressive, maintainable, and often, more performant queries. Databases are optimized to handle IN clauses efficiently, often transforming them into highly optimized internal operations.

Deconstructing the `IN` Operator: Your New Best Friend for Multi-Value Filtering

At its core, the IN operator in ServiceNow’s GlideRecord is used to query records where a specific field’s value matches any value within a given array (or list). Think of it as a compact way of expressing multiple OR conditions, but in a much more elegant fashion.

The Syntax Unpacked

When you’re working with addQuery('String', 'Operator', 'Value') in ServiceNow, the structure for the IN operator is specific. Here’s how it generally looks:

gr.addQuery('field_name', 'IN', array_of_values);

Let’s break down each part:

  • gr: This is your GlideRecord object, initialized for the table you’re querying (e.g., new GlideRecord('incident')).
  • 'field_name': The exact name of the field on the table you want to filter. This must be a string.
  • 'IN': The operator itself, always passed as a string in uppercase.
  • array_of_values: This is the crucial part. It must be a JavaScript array containing all the values you want to match against the field_name. The values within the array should match the data type of the field you’re querying (e.g., strings for string fields, numbers for integer fields, sys_ids for reference fields).

A Walkthrough with the Example

Let’s take a look at the provided example and dissect it line by line to truly understand its mechanics:

var cat = ['software', 'hardware'];
var inc = new GlideRecord('incident');
inc.addQuery('category', 'IN', cat);
inc.query();
while(inc.next()) {
  gs.print(inc.getValue('number') + ' ' + inc.getValue('short_description')) ;
}
  1. var cat = ['software', 'hardware'];
    This line initializes a JavaScript array named cat. This array holds the specific values we’re interested in for the ‘category’ field. Notice the values are strings, correctly enclosed in single quotes, and separated by commas. This array is the “list of values” that the IN operator will use for comparison.
  2. var inc = new GlideRecord('incident');
    Here, we create a new GlideRecord object, telling ServiceNow that we want to query records from the ‘incident’ table. This is our handle to interact with the incident data.
  3. inc.addQuery('category', 'IN', cat);
    This is where the magic happens! We’re telling our GlideRecord object (inc) to add a query condition.

    • The first argument, 'category', specifies that we want to filter based on the ‘Category’ field of the incident record.
    • The second argument, 'IN', is our operator, indicating that we’re looking for values within a list.
    • The third argument, cat, is our array. ServiceNow will now look for incident records where the ‘category’ field’s value is either ‘software’ OR ‘hardware’.
  4. inc.query();
    This line executes the query we’ve built. ServiceNow goes to the database, applies the filter, and retrieves all matching incident records.
  5. while(inc.next()) { ... }
    After the query, we use a while loop to iterate through each record returned by the query. The inc.next() method moves the record pointer to the next record in the result set and returns true if there’s a record, false otherwise.
  6. gs.print(inc.getValue('number') + ' ' + inc.getValue('short_description')) ;
    Inside the loop, for each matching incident, we print its number and short description to the system logs (or the console if run from a background script). This line demonstrates how to access the field values of the current record.

The result, as expected, will be a list of incident numbers and short descriptions where the category is either “Software” or “Hardware.” Simple, clean, and incredibly powerful!

Data Types and `IN`

While the example uses strings, the IN operator is versatile. You can use it with various data types:

  • Strings: Most common, like our ‘category’ example. Values should be in single quotes within the array: ['value1', 'value2'].
  • Numbers: For integer or decimal fields. Values don’t need quotes: [1, 5, 10].
  • Sys_IDs: For reference fields (e.g., ‘assigned_to’, ‘assignment_group’). These are also strings, so they need quotes: ['sys_id1', 'sys_id2'].

Practical Explanations & Real-World Examples

Now that we’ve grasped the core concept, let’s explore some everyday scenarios where the IN operator can make your ServiceNow scripts and queries shine. These aren’t just theoretical; they’re situations you’ll likely encounter regularly as a ServiceNow developer or administrator.

1. Streamlining Incident Reporting and Management

Beyond our initial ‘Software/Hardware’ example, incidents often need more complex multi-value filtering.

Use Case: Critical or High Priority Incidents from Specific Locations

Imagine your service desk manager wants a list of all active incidents that are either ‘Critical’ or ‘High’ priority, and originating from a list of key locations like ‘New York’, ‘London’, or ‘Sydney’.

// Define the priorities and locations we're interested in
var importantPriorities = ['1 - Critical', '2 - High'];
var keyLocations = ['New York', 'London', 'Sydney'];

var grCriticalIncidents = new GlideRecord('incident');
grCriticalIncidents.addActiveQuery(); // Only active incidents
grCriticalIncidents.addQuery('priority', 'IN', importantPriorities);
grCriticalIncidents.addQuery('location', 'IN', keyLocations);
grCriticalIncidents.query();

gs.print('--- Critical/High Incidents from Key Locations ---');
while (grCriticalIncidents.next()) {
    gs.print('Incident: ' + grCriticalIncidents.number +
             ', Priority: ' + grCriticalIncidents.priority.getDisplayValue() +
             ', Location: ' + grCriticalIncidents.location.getDisplayValue() +
             ', Short Description: ' + grCriticalIncidents.short_description);
}

This script efficiently filters by two different fields using the IN operator for each, providing a highly specific result set.

2. Enhancing Change Management Workflows

Change management often involves tracking changes across various states or affecting particular configuration items (CIs).

Use Case: Changes in Specific Stages Affecting Key Servers

A change manager needs to review all changes that are currently in ‘Scheduled’, ‘Implement’, or ‘Review’ states and are targeting a set of critical production servers.

// Define the change states and sys_ids of critical servers
var relevantChangeStates = ['scheduled', 'implement', 'review']; // Internal values for state field
var criticalServerSysIds = ['sys_id_of_server_A', 'sys_id_of_server_B', 'sys_id_of_server_C']; // Replace with actual sys_ids

var grChanges = new GlideRecord('change_request');
grChanges.addActiveQuery(); // Only active changes
grChanges.addQuery('state', 'IN', relevantChangeStates);
grChanges.addQuery('cmdb_ci', 'IN', criticalServerSysIds); // 'cmdb_ci' is the field for Configuration Item
grChanges.query();

gs.print('--- Changes Affecting Critical Servers in Key States ---');
while (grChanges.next()) {
    gs.print('Change: ' + grChanges.number +
             ', State: ' + grChanges.state.getDisplayValue() +
             ', CI: ' + grChanges.cmdb_ci.getDisplayValue() +
             ', Description: ' + grChanges.short_description);
}

Here, we see the IN operator used with both choice list values (states) and reference field values (CI sys_ids), showcasing its versatility.

3. Optimizing Request Fulfillment and User Management

For service catalog items or user administration, the IN operator is invaluable.

Use Case: Requests for Specific Catalog Items from Certain Departments

Your IT director wants to see all open requests for a new laptop, monitor, or docking station, specifically those placed by users in the ‘Marketing’ or ‘Sales’ departments.

// Define catalog item names and department sys_ids
var desiredItems = ['New Employee Laptop', 'External Monitor', 'Docking Station'];
var targetDepartmentSysIds = ['sys_id_of_marketing_dept', 'sys_id_of_sales_dept']; // Replace with actual sys_ids

var grRequests = new GlideRecord('sc_req_item'); // Requested Item table
grRequests.addActiveQuery();
grRequests.addQuery('cat_item.name', 'IN', desiredItems); // Querying a dot-walked field for item name
grRequests.addQuery('request.requested_for.department', 'IN', targetDepartmentSysIds); // Dot-walking to get department of requester
grRequests.query();

gs.print('--- Specific Requests from Marketing/Sales Departments ---');
while (grRequests.next()) {
    gs.print('RITM: ' + grRequests.number +
             ', Item: ' + grRequests.cat_item.name +
             ', Requester: ' + grRequests.request.requested_for.name +
             ', Department: ' + grRequests.request.requested_for.department.getDisplayValue());
}

This example beautifully demonstrates using IN with dot-walked fields, which is a common and powerful technique in ServiceNow to access related record information.

Beyond the Basics: Advanced Tips & Considerations

While the IN operator simplifies many queries, a true ServiceNow master understands the nuances that can further optimize its use and avoid potential pitfalls.

Performance Implications: Don’t Abuse the Power!

The IN operator is generally more performant than a long chain of addOrCondition calls, as databases are optimized for it. However, “generally” is the keyword. There are still limits:

  • Very Large Arrays: If your array_of_values contains hundreds or thousands of elements, performance can degrade. The underlying SQL query generated can become quite long, potentially hitting database limits or causing slow execution.
  • Indexing: Ensure the `field_name` you’re querying against is indexed. Just like a book’s index helps you find information quickly, a database index helps the `IN` operator locate matching records much faster. ServiceNow automatically indexes many common fields, but for custom fields or highly queried standard fields, verifying indexing is a good practice.

When to be cautious: If you’re building an array from *all* records in another table (e.g., getting all user sys_ids from a department with 10,000 users) and then using that in an IN query on a huge table, consider alternatives like a related list query if applicable, or more complex database query optimizations for highly scaled environments.

Dynamic Arrays: Building Your List on the Fly

The real power of the IN operator often comes when the list of values isn’t hardcoded but generated dynamically. This is a common pattern in business logic:

Example: Find Incidents Assigned to Members of Specific Groups

Let’s say you want to find all incidents assigned to any member of the ‘Service Desk’ or ‘Network Operations’ groups. You can’t just query the `assigned_to` field directly with the group names. Instead, you first need to find all users belonging to those groups, collect their sys_ids, and then use those sys_ids in an IN query on the incident table.

var userSysIds = [];
var grUser = new GlideRecord('sys_user');
grUser.addQuery('group.name', 'IN', ['Service Desk', 'Network Operations']); // Find users in these groups
grUser.query();

while (grUser.next()) {
    userSysIds.push(grUser.sys_id.toString());
}

if (userSysIds.length > 0) { // Only proceed if we found users
    var grIncidents = new GlideRecord('incident');
    grIncidents.addActiveQuery();
    grIncidents.addQuery('assigned_to', 'IN', userSysIds);
    grIncidents.query();

    gs.print('--- Incidents Assigned to Service Desk or Network Operations Members ---');
    while (grIncidents.next()) {
        gs.print('Incident: ' + grIncidents.number +
                 ', Assigned To: ' + grIncidents.assigned_to.name +
                 ', Short Description: ' + grIncidents.short_description);
    }
} else {
    gs.print('No users found in the specified groups.');
}

This dynamic approach is incredibly flexible, allowing your scripts to adapt to changing data without needing code modifications.

The Inverse: `NOT IN` Operator

Just as useful is its counterpart, the NOT IN operator. It does exactly what it sounds like: it returns records where the field’s value is not present in the specified array. The syntax is identical, just replace 'IN' with 'NOT IN'.

var excludedCategories = ['Facilities', 'Human Resources'];
var grInc = new GlideRecord('incident');
grInc.addQuery('category', 'NOT IN', excludedCategories); // Get incidents NOT in these categories
grInc.query();
// ... process results

Server-Side Only for `addQuery`

Remember that GlideRecord and its addQuery method are server-side APIs. This means you’ll use them in business rules, script includes, fix scripts, background scripts, workflow activities, and server-side UI Actions. You won’t directly use addQuery in client-side scripts (e.g., Client Scripts, UI Policies), where different client-side APIs or GlideAjax might be employed to fetch filtered data from the server.

Troubleshooting Common Pitfalls with the `IN` Operator

Even the most seasoned developers stumble sometimes. Here are some common issues you might encounter when using the IN operator and how to fix them:

1. Incorrect Array Format

This is probably the most common mistake. The third argument to addQuery for the IN operator *must* be a JavaScript array.

  • Wrong: inc.addQuery('category', 'IN', 'software,hardware'); (This is a string, not an array).
  • Wrong: inc.addQuery('category', 'IN', 'software'); (This is a single string, use = instead, or wrap it in an array: ['software']).
  • Correct: inc.addQuery('category', 'IN', ['software', 'hardware']);

2. Data Type Mismatch within the Array

Ensure the values in your array match the expected data type of the field you’re querying.

  • Field: Priority (integer type)
    • Wrong: ['1 - Critical', '2 - High'] (These are display values/strings for an integer field).
    • Correct: [1, 2] (These are the actual integer values).
  • Field: Assigned To (reference type, stores sys_id)
    • Wrong: ['Fred Luddy', 'Don Goodfellow'] (These are names, not sys_ids).
    • Correct: ['sys_id_of_fred', 'sys_id_of_don'] (These are the actual sys_ids).

Always verify the underlying data type and value for the field you’re targeting. Using .getDisplayValue() on a GlideRecord object can help you see the user-friendly value, but remember to query using the actual stored value.

3. Empty Array Passed to `IN`

What happens if your dynamically built array ends up being empty? If you pass an empty array to the IN operator:

var emptyArray = [];
var gr = new GlideRecord('incident');
gr.addQuery('category', 'IN', emptyArray);
gr.query(); // This will typically return ZERO records.

This is generally desired behavior (no values to match, so no records). However, if you *expect* results and get none, check if your array is being populated correctly. It’s often good practice to add a check like if (myArray.length > 0) { gr.addQuery(...); } to avoid querying with an empty array if you have other conditions that should still apply.

4. Case Sensitivity (or lack thereof)

For string fields in ServiceNow, the IN operator (like the = operator) is typically case-insensitive in most standard installations. This means ‘software’ will match ‘Software’, ‘SOFTWARE’, etc. However, it’s always best practice to normalize your input (e.g., to lowercase) or query using the exact values if you’re dealing with very specific data scenarios where case might legitimately differentiate records, or if your instance has custom database collation settings. For choice list fields, always use the *internal* value, not the display value, for consistency.

5. Typos in Field Name or Operator

A simple yet frustrating error. Double-check your field name (e.g., `categor` instead of `category`) and ensure the operator is correctly spelled as 'IN' (uppercase).

6. Dot-Walking Gone Wrong

When using dot-walking (e.g., 'request.requested_for.department'), ensure each link in the chain is valid and that you’re referencing the correct field at the end of the walk. Incorrect dot-walking will often result in the query simply failing to return records or throwing an error, depending on the context.

Interview Relevance: Why It Matters to Know the `IN` Operator

If you’re interviewing for a ServiceNow developer, administrator, or consultant role, expect questions that probe your scripting and data querying skills. The IN operator is a prime candidate for such discussions, and here’s why:

  • Efficiency and Best Practices: Knowing IN demonstrates your understanding of efficient querying. Interviewers want to see that you can write clean, performant code, avoiding verbose OR chains.
  • GlideRecord Mastery: It’s a fundamental part of the GlideRecord API. A solid grasp of IN shows you’re comfortable with one of ServiceNow’s most critical server-side APIs.
  • Problem-Solving Acumen: If presented with a scenario (“How would you find all incidents with category A, B, or C?”), using IN showcases a more sophisticated problem-solving approach compared to chaining addOrCondition.
  • Understanding Data Structures: The need to pass an array to IN implies an understanding of JavaScript data structures, which is essential for scripting in ServiceNow.
  • Dynamic Querying: Discussing how you’d dynamically build an array for an IN query (like finding users in a group) demonstrates advanced scripting and logical thinking.

How to Discuss `IN` in an Interview Setting:

When asked about querying techniques or specific operators, don’t just state the syntax. Instead:

  1. Explain its Purpose: “The IN operator allows me to query a field against multiple possible values, acting as a concise alternative to a series of OR conditions.”
  2. Provide a Simple Example: “For instance, to find incidents in ‘Software’ or ‘Hardware’ categories, I’d use gr.addQuery('category', 'IN', ['Software', 'Hardware']);“
  3. Highlight Advantages: “It leads to cleaner, more readable code and is generally more performant as databases are optimized for `IN` clauses.”
  4. Mention Dynamic Use Cases: “Its real power often comes from dynamic arrays, like first querying for all users in a specific department, collecting their sys_ids, and then using those sys_ids in an IN query to find tasks assigned to them.”
  5. Acknowledge Limitations/Considerations: “While powerful, I’d be mindful of passing extremely large arrays, as that could impact performance. Also, ensuring the correct data types within the array is crucial.”
  6. Bring up `NOT IN`: “I’d also mention its inverse, NOT IN, for when I need to exclude specific values.”

Demonstrating this comprehensive understanding will leave a strong impression on your interviewer.

Conclusion: Embrace the Power of `IN`

The IN operator in ServiceNow is more than just another piece of syntax; it’s a testament to efficient, readable, and powerful scripting. By mastering it, you’re not just writing better code; you’re thinking more effectively about how you interact with and retrieve data from your ServiceNow instance.

From simplifying multi-category incident reports to dynamically filtering records based on complex relationships, IN empowers you to tackle common challenges with elegance. Remember to practice, experiment with dynamic arrays, and keep those troubleshooting tips handy. The more you use it, the more intuitive it becomes, and the faster you’ll become at crafting robust and reliable ServiceNow solutions.

So, go forth and query! Your ServiceNow instance (and your colleagues) will thank you for the cleaner, more efficient code. Happy scripting!


Scroll to Top