cancel
Showing results for 
Search instead for 
Did you mean: 

Properly filter SharePoint Yes/No column.

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. 

 

Théo

Status: New

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.

Comments
Anonymous
Not applicable

Hello,

 

I agree this should be fixed,

just wanted to add that strangely the following also works:

Filter(SPList,YesNoColumn <> false)

Super User

Yes it works but can't be delegated

Power Automate
Status changed to: Planned

Adding @GregLi as an FYI. 

Post Prodigy

Just ran into this today, glad to hear a fix is planned.

 

I have a somewhat mission critical app in production. Does anyone know if there are any other (perhaps less obvious) implications to using the suggested workaround... i.e. Filter(SPList,YesNoColumn = 1)? Also wondering if the fix will break galleries that use the 1/0 or whether the solution will be gracefully compatible with this workaround.

 

Using 1/0 appears to return the correct set but the designer error is a bit unerving on some of the main galleries in my app.

 

Post Prodigy

After some more testing on this, it is clear that the workaround suggested does NOT reliably return the correct results in cases and I think one should be cautious with its use, at least based on my results. In my experience, only Filter(SPList,YesNoColumn = false) reliably returns the correct results in ALL situations. All other combinations (including YesNoColumn = true) fail when combined with other circumstances (other filters, sorts, etc.)

 

It seems that when the error states the formula cannot be evaluated, it is telling the truth.

Advocate III

Just write that:

 

Filter(SPList,YesNoColumn.Value = 1)

Post Prodigy

Ah ha! Thanks @Ethan0079 I will try this and see... there is definitely some odd behavior around this. Thanks for the suggestion.

Advocate III

For me it works great:Smiley Very Happy

Tell me again if it works for you.

Filter('Liste de projets imputable';Statut.Value="Actif")
Super User

It is about filtering a YesNo column -using a boolean in PowerApps-, not a Choices column. 

Filtering a Choices column is not a problem since you're comparing strings. 

 

Théo

Advocate III

Oh yes you're right I didn't understand it like that but I actually had this problem too