Select Page

SharePoint

Below is a simple SharePoint list called “Cars”

IsNew is a column of type “Yes/No”

When trying to filter the SharePoint list view using the Yes/No column the filter formula in the browser url evaluates to – useFiltersInViewXml=1&FilterField1=IsNew&FilterValue1=1&FilterType1=Boolean&FilterOp1=In
where FilterValue1 = 1 specifies that the filter condition selected is “Yes” &
FilterValue1 = 0 relates to “No”


PowerApps

Connect to the SharePoint data source list “Cars”

Add a Gallery to the screen and associate the “Cars” list to the Items property of the Gallery

Add 2 labels to the gallery to show the “Title” and “IsNew” columns

As shown in the image below the IsNew(Yes/No) column shows data as true or false (Boolean format) in PowerApps

The Bug

In order to filter the results in the Gallery based on the IsNew(Yes/No) column, update the Items property of the Gallery to
Filter(Cars,IsNew = true)

However,as shown in the figure below, the filtered results returned are incorrect as it shows only results for value false(No)

This is because SharePoint stores the Yes/No field data as 1 or 0

The Fix (not ideal)

Updating the Filter condition to Filter(Cars,IsNew = 1) returns the correct results as shown in the figure below

Filter(Cars,IsNew = 0) returns the correct results as shown in the figure below

However, PowerApps shows a delegation warning for incompatible types since we are trying to compare a Boolean ( Yes/No column) to a Number field (0 or 1) as shown in figure below

Although filtering based on a Number type (0 or 1) does work, there is a delegation warning and hence this is not an ideal solution for cases where the data source items exceeds the delegation limit (base limit 500 items, can be extended to 2000 items)
Click here to learn about PowerApps delegation

I came across this scenario for one of my clients last week wherein the list items exceeds the delegation limits and I had no option but to avoid using the “Yes/No” field until this bug is fixed

This bug was reported on the PowerApps Ideas Forum and is currently marked as “Planned”
https://powerusers.microsoft.com/t5/PowerApps-Ideas/Properly-filter-SharePoint-Yes-No-column/idi-p/158375

Thanks,
Reza