In the course of implementing Microsoft Dynamics CRM, you will inevitably make configuration changes after records have been added to the system. When the configuration change is the addition of a Boolean field (also referred to as a two-option or yes/no field), it can present problems for anyone attempting to build a query that references this field. A Boolean field should only have two values. As such, it should not matter whether you use “equals” or “does not equal” as the operator in your query. However, if a record exists before the Boolean field is added to the entity, that field will have a null value.
This creates a situation where there are actually three possible values for the field within the system: No (0), Yes (1), and null. If a query uses “does not equal” as shown below:
All of the null values, as well as the other value that was not excluded, will be returned as results.
When including Booleans as part of a query, whether via an Advanced Find, in workflow check conditions, or in SQL queries, you should always keep in mind the possibility of a null value and choose your operators with care.
Want to learn more about Advanced Find in Dynamics CRM? Check out these articles for more handy tips and tricks:
- How to Find CRM Records without Activities using Advanced Find
- Aging Using the “Older Than” Clause within Advanced Find
- New Operators in the Advanced Find in Dynamics CRM 2015