There is a well known "bug" when working with SharePoint list Yes/No column :
The Yes/No value can not be compare to boolean type true/false.
Filter(SPList,YesNoColumn = true) will not return all the items with a Yes value .
The workaround is to use 1 or 0:
Filter(SPList,YesNoColumn = 1)
This will return all items with a true value, but the designer shows an error because we are comparing a boolean to a number.
It would be great to fixe that, as a Yes/No column is indeed a boolean value, we need to be able to compare that to a boolean value in PowerApps.
Apologies resetting this status. Adding @GregLi and @Emma to update with accurate status. Both TopShelf ad Audrie-MSFT are not longer able to provide an update for Power Apps ideas. @GregLi & @Emma please provide inputs and/or comments. Thank you.
So this was logged almost a year ago and still seems to be a bug.
You might also note that
Doesn't return an error, it just doesn't work - the edit form however seems quite happy reading and saving this value as a boolean, so after spending two weeks working on an app, when I get to the screen that requires this filtering functionality - I only see now it doesn't actually work. Based on the delegation issues of the workarounds, I now have to go back to my source and rewire my app to use text columns instead - and any piece of code and form that references this column, and my PBI dashboards, AND my flows.
@Audrie-MSFT - does a bug have to have a lot of votes to be fixed?
This seems to be a bug to me as well. It has said "Planned" since 2017, and it is now August of 2019, with no changes. Any updates on the implementation of this fix from @microsoft?
I can't even believe we need to raise this as an 'idea' to improve PowerApps. This is a BUG, and needs to be fixed quickly.
The inconsistency in handling Yes/No fields in PowerApps formulas makes it almost impossible to use those fields. A Filter operation that was working fine with (SharePoint column) 'Active=true', suddenly stopped working when another condition was added to the filter. In fact it started selecting items matching the opposite condition, i.e. 'Active=false'.
Changing it to 'Active=1' gives an incompatible type comparison warning, yet works sometimes. Changing it to 'Active<>0' also works only sometimes, particularly when multiple conditions are combined. And of course it gives a delegation error. All these are serious but basic bugs.
@Audrie-MSFT Is there any sort of ETA for fixing this?
@Anonymous Could you help @rjgeeky with this question? or Redirect to someone who would be able to estimate if a change is planned please?
Thank you for your patience,
Yeah, this one seems a beyond silly to STILL be in the backlog. So, @Anonymous can you give an answer on timeline...and take some pressure off @Audrie-MSFT, please?
Why this double standard still exists? Why we are shown an error where it should work? It has been over a year since this bug has been reported...
My position with this issue now:
- Filter(SharpointList, YNColumn=false)…. works properly and is delegable
…. doesen't show any error, but shows the "false records", it is delegable and the delegation works
- Filter(SharpointList, YNColumn=0)…. works properly, is delegable, but shows error "not compatible"
- Filter(SharpointList, YNColumn=1)…. works properly, is delegable, but shows error "not compatible"
- Filter(SharepointList, YNColumn<>0)…. works properly for 500, not delegable
- Filter(SharepointList, Text(YNColumn)=true) …. shows only delegation error, but does not work at all
…. shows only delegation error, but does not work at all
- Filter(ShaprointList, YNColumn.Value=1)…. does not work
I don't know, if it makes any difference, but I am coding in Austria.
I hope, this list gives a contribution to the fixing of this bug.
Thank you for all hints, to use "1" instead of "true" saved my work!!!
This worked for me - Filter(ShaprointList, YNColumn.Value="Yes")
Since Microsoft chooses to keep hiring utterly incompetent programmers, a workaround is to eliminate use of the broken Yes/No list value type and just use a numeric field with a range of 0 to 1 and with 0 digits to the right of the decimal point - then use a simple numeric comparison in filters (0=false, 1=true).
We should open a gofundme fundraising campaign to buy a birthday cake for this "idea" and send it to MS so they can fix such an annoying issue.