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: Planned

Adding @GregLi as an FYI. 

Comments
Level 8

Hello,

 

I agree this should be fixed,

just wanted to add that strangely the following also works:

Filter(SPList,YesNoColumn <> false)

Level 10

Yes it works but can't be delegated

Flow Staff
Status changed to: Planned

Adding @GregLi as an FYI. 

Level 8

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.

 

Level 8

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.

Level: Powered On

Just write that:

 

Filter(SPList,YesNoColumn.Value = 1)

Level 8

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

Level: Powered On

For me it works great:Smiley Very Happy

Tell me again if it works for you.

Filter('Liste de projets imputable';Statut.Value="Actif")
Level 10

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

Level: Powered On

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