How Filters and Report Criteria Handle null Values - Support and Troubleshooting

How Filters and Report Criteria Handle null Values Issue  Overview Although null values may be quite common in field on tables and forms, the way these null values are handled in many queries within the system may be somewhat counter-intuitive.

Many issues and questions are fielded in regards to a certain query submitted in a filter or a report is not returning the expected results. This issue is often due to the fact that query may be intentionally or inadvertently comparing null values which may not be handled in the query as one would initially expect. The following article will describe how nulls are handled in certain filter or report queries in the system.

The Issue The issue lies not in the ServiceNow product itself, but rather is a result of how the underlying databases are designed to handle certain queries which may be comparing null values. The issue cannot really be attributed to the DBMS (DataBase Management System) either, as these queries are actually being handled as per the current Standard Compliance.

In a Database, a field containing a null value can be considered to contain nothing or be empty. As such, in the standard, comparing nothing against an actual value is essentially returning an undefined value, which in certain cases may not be exactly the results expected from the query.

Thus, within the ServiceNow platform, the following filter criteria when used within a report or list filter and comparing against a field that may contain null values, will not return fields that are used within the criteria that have a value of null or empty.

'is not"

"is not one of"

"is different from"

"is same as"

When any of these queries are applied in a report or a list, any records in which the value being compared is an empty or null value, that row will not be returned, thus resulting in a list that may appear partially incomplete.

Example As an example, perhaps you have the Incident able in which you want to return all the records in the table in which the Company value "is not" a specific value, say "ServiceNow". When the query is run, it will not return any rows that happen to have a null Company value, which may not necessarily be the value expected.

Potential Work Around As a work-around, if the null values should be included in the list or report, an additional OR clause should be added to the query criteria in the report or filter specifying that the same field "is empty". As an example, using the query from above, the query should be modified as follows:

Company "is not" ServiceNow

OR

Company "is empty"

This change would ensure that any values for which the Company value is null would also be returned as a result of the record query.