Building hierarchical queries

Simplify and build more efficient queries by leveraging hierarchical relationships in the condition builder.

Key benefits

• Filter table data in the condition builder based on a record hierarchy.
• Search through an entire hierarchy with a single condition.
• Streamline query building with less ongoing maintenance.

Crafting queries in condition builder can become cumbersome when you need to search through each level of a hierarchical relationship using multiple OR conditions. Hierarchical queries streamline this process by allowing you to specify a single node and search down the hierarchy from there, saving time and effort.

Record hierarchies

By default, the following record hierarchies are included with your instance:

• The Department Hierarchy on the Department [cmn_department] table
• The Location Hierarchy on the Location [cmn_location] table
• The Manager Hierarchy on the User [sys_user] table

You can view these predefined record hierarchies by navigating to All > System Definition > Record hierarchy.

Each record hierarchy is based on a reference field that contains parent-child relationships between records in the same table.

• The Department Hierarchy is based on the Parent reference field in the Department [cmn_department] table.
• The Location Hierarchy is based on the Parent reference field in the Location [cmn_location] table.
• The Manager Hierarchy is based on the Manager reference field in the User [sys_user] table.

For example, the Location Hierarchy on the Location [cmn_location] table uses the Parent reference field. Each location has a parent, which is another record in the Location [cmn_location] table. For example, the Chicago and Springfield location records have the Illinois location record's sys_id value in their parent field. Street addresses have sys_id locations for the cities they belong to in their parent field.

You can use the Location Hierarchy in the condition builder to create targeted queries. For example, you can specify a starting point in the Location Hierarchy and query down the hierarchy to retrieve all assets associated with locations throughout that portion of the hierarchy.

Hierarchical paths

Each record that belongs to a record hierarchy stores its hierarchical information in a path field. The path field is used for searches throughout the hierarchy in the condition builder.

Paths for each predefined record hierarchy are automatically generated by the ServiceNow AI Platform and stored in the path field on the Department [cmn_department], Location [cmn_location], and User [sys_user] tables.

• The path for departments is stored in the Parent HP1 field in the Department [cmn_department] table.
• The path for locations is stored in the Parent HP1 field in the Location [cmn_location] table.
• The management path is stored in the Manager HP1 field on the User [sys_user] table.

For example, the ServiceNow AI Platform automatically builds the hierarchy path for each location record in the Location [cmn_location] table. This creates a nested structure where each location can have sub-locations, forming a tree-like hierarchy. The ServiceNow AI Platform also updates these paths when records are added, changed, or removed.

Many other tables contain self-referential fields, indicating a parent-child relationship between records. However, hierarchical paths aren't generated by the ServiceNow AI Platform in those tables until you define a hierarchy in the Record Hierarchy [sys_record_hierarchy] table.

Use cases

The Department [cmn_department], Location [cmn_location], and User [sys_user] tables contain reference fields with parent-child relationships by default.

Department Hierarchy

Search for assets associated with departments in your company using the Department Hierarchy record hierarchy.

Each department record contains a hierarchical path, allowing you to create queries in the condition builder based on the department hierarchy. Since asset records have a Department reference field, you can query assets that belong to a specific department.

• Find all assets that belong to the IT department using a query like:

  [Department] [is in hierarchy] [Department Hierarchy] starting at [IT] which is [Included]

  In this example, searching the hierarchy returns assets associated with the IT department, including assets associated with departments that are part of the IT department, and so forth.

• Find all departments under the IT department by filtering directly on the Departments [cmn_department] table using a query like:

  [Parent] [is in hierarchy] [Department Hierarchy] starting at [IT] which is [Included]

Location Hierarchy

Search for records according to location using the Location Hierarchy record hierarchy.

Each location record contains a hierarchical path, allowing you to create queries in the condition builder based on the location hierarchy. Since incident records have a Location reference field, you can search for incidents based on a caller's location.

• Find all incidents from callers based in Illinois using a query like:

  [Location] [is in hierarchy] [Location Hierarchy] starting at [Illinois] which is [Included]

  This query returns incidents where the caller's location is Illinois, any city in Illinois, or a street address in any city in Illinois.

• Find incidents for cities and street addresses in Illinois, but not incidents where the caller location is simply Illinois using a query like:

  [Location] [is in hierarchy] [Location Hierarchy] starting at [Illinois] which is [excluded]

• Find all incidents based on your location as the logged-in user using a dynamic filter in a query like:

  [Location] [is in hierarchy (dynamic)] [Location Hierarchy] starting at [My Location] which is [included]

Manager Hierarchy

Search for records throughout the management chain in your organization using the Manager Hierarchy record hierarchy.

Each user record contains a hierarchical path, allowing you to create queries in the condition builder based on the management hierarchy. Querying any table and selecting a reference field that points to the Users [sys_user] table enables you to search through the management chain.

• Find all incidents assigned to users who report to Bud Richman using a query like:

  [Assigned to] [is in hierarchy] [Manager Hierarchy] starting at [Bud Richman] which is [Included]

  In this example, searching the hierarchy returns incidents assigned to Bud Richman, including incidents assigned to users who report to Bud and their direct reports, and so forth.

• Find all incidents assigned to you and users in your own organization using a dynamic query like:

  [Assigned to] [is in hierarchy (dynamic)] [Manager Hierarchy] starting at [Me] which is [included]

• View the management chain itself by filtering directly on the Users [sys_user] table using a query like:

  [Manager] [is in hierarchy] [Manager Hierarchy] starting at [Bud Richman] which is [included]

• View the users who report to you by filtering directly on the Users [sys_user] table using a dynamic query like:

  [Manager] [is in hierarchy (dynamic)] [Manager Hierarchy] starting at [Me] which is [included]

Overview of building a record hierarchy

In addition to the predefined record hierarchies that are included with your instance, you can build a record hierarchy on a table of your choice.

Building a hierarchy between related records in the same table requires a self-referencing field. When building the hierarchy, you can either use an existing reference field that already defines parent-child relationships or create a self-referencing field and populate it with the appropriate values for each record.

1. Identify a table that contains parent-child records that you want to use for building hierarchical queries. For example, to build queries based on related assets, you can define a record hierarchy based on the Asset [alm_asset] table.
2. Determine which reference field in the table defines the relationships between records. For example, the Parent field in the Asset [alm_asset] table describes the parent asset of an asset.
3. Create a hierarchy in the Record Hierarchies [sys_record_hierarchy] table and specify the table and reference field that you want to use. The ServiceNow AI Platform automatically adds hierarchical path information to each record in the table.
4. Create hierarchical queries in the condition builder by selecting the hierarchy that you created. Use operators to search through the hierarchy.