Boolean Blitz: When Two Options Are Actually Three

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

Happy CRM’ing