Blog

Sharepoint - Filtering records by yes/no columns bug - now fixed!

If you've been using SharePoint and Power Apps for some time, you may be aware of the bug that prevents the correct filtering of Yes/No columns. The good news is that this has now been fixed and this post describes in further detail, the past and new behaviour.

For at least five years (since around 2016) there has been a bug that has prevented the ability to correctly filter SharePoint lists by yes/no columns. The great news is that this has now been fixed!

This post examines the behaviour of this bug, the previous workarounds, and the current correct behaviour following this fix (May 2021).

For background reading, there are several posts that document this bug.
https://powerusers.microsoft.com/t5/Building-Power-Apps/BUG-YESNO-SharePoint-Filtering/m-p/111123

What was the SharePoint Yes/No bug?

Let's take the case of a SharePoint list of products. This list contains a yes/no column called 'Active', which stores whether the product is an active product.

The following, syntactically correct formula, would not return any records.
Filter(Products, Active=true)

Yet, the following formula would return all records that were not active. 
Filter(Products, Active=false)

What were the workarounds?

The first common workaround was to filter by the number 1 or 0, rather than a Boolean true/false value.

The following formula would correctly return the active products. However, the designer would show a warning because the formula compares a Boolean to a number.

Filter(Products, Active=1)
A second, more effective workaround, was to not include the equality operator in the condition. This formula would correctly return active products.
Filter(Products, Active)

The problem here was that appending additional conditions would result in expressions that were not delegable, even although the target column types and operators were delegable. 

The third workaround was to avoid the use of yes/no columns, and to use a text column to store the text 'yes' or 'no'. This wasn't ideal because it wasn't applicable against existing lists with existing yes/no columns. Also, this solution made it more difficult to build forms with toggle/checkbox controls, because the default control type for a card would be a text input control.

Why was this bug such a problem?

Whilst the workarounds above were helpful, this bug made it difficult to filter SharePoint lists effectively by yes/no columns.

It was a struggle to construct multiple filter conditions that included yes/no columns, in a delegable way. Additionally, it was difficult to filter yes/no columns by variables or other control values, particularly when using the workaround that excluded the equalities (=) operator.

What this does fix mean to app builders?

Following this fix, the ability to filter by yes/no columns work exactly as expected. As the screenshot beneath shows, we can now filter the product list for records where the active value is true, and Power Apps returns all expected records.


The fix also means that we can filter more easily by variables and control values. As an example, the screenshot beneath shows how we can add a checkbox control to display active records only. When this checkbox is unchecked, the gallery control displays all records (where the active value is true or false)

Conclusion

There has been a long-standing bug that has affected the ability to filter SharePoint yes/no columns correctly. This bug has now been fixed, and we can now filter records using the syntax we would expect.
Previous
Next
Related posts