Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Boolean Blitz: When Two Options Are Actually Three

Post Author: Joe D365 | August 2nd, 2016

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.

Boolean

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:

Boolean

All of the null values, as well as the other value that was not excluded, will be returned as results.

Boolean

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:

Happy CRM'ing

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

3 comments on “Boolean Blitz: When Two Options Are Actually Three”

  1. A possible workaround to avoid the null value to records created prior the addition of the boolean field would be configure and run a workflow to set the value to "no" to those records.

    However, if you have closed opportunities or leads, it would be necessary to reopen those records and then close then again after update the boolean field. Sometimes this option is not viable as per the business rules of the organization because it will modify the last modification date of the record and also generate - in case you are doing this with opportunities, quotes, cases... etc - new close records.

  2. Thanks for the post! I found this out by trial and error. My fix was to change my Advanced Find from.... where field equals "no" ....to.....where field does not equal "yes". That way, the results give me all of the "no" and "null" fields.

  3. Thanks for the post! I found this out by trial and error. My fix was to change my Advanced Find from.... where field equals "no" ....to.....where field does not equal "yes". That way, the results give me all of the "no" and "null" fields.

PowerObjects Recommends