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
