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: Powered On

I just ran into this today and then did some digging to find this was discovered and reported over a year ago...can't believe it's not fixed yet.  1/0 seems to work the best for me.  Hoping, this will be fixed soon.

Level: Power Up

After much headbashing I finally ran across this post.


Filter(SPList,YesNoColumn = 1)     is what did the trick for me.

 

Thanks for explaining this and saving further headache Smiley Happy 

 

 

Filter(SPList,YesNoColumn = 1) as well as Filter(SPList,YesNoColumn <> false) are NOT delegable. This should be fixed immediately.

Super User

I always find that resorting back to text helps get around things.  I tried in this case the following:

    Filter(SPList, Text(YesNoColumn)="true") 

and that gave me what I needed.  Of cuorse the complaint was a delegation warning.  Not an issue in my case, but to clear that, I went with:

   Filter(AddColumns(SPList, "txtYesNoColumn", Text(YesNoColumn)), txtYesNoColumn="true")

That cleared up the warning and worked just as well.
And, if the new column tricks up things then I went with this little charm:

  DropColumns(Filter(AddColumns(SPList, "txtYesNoColumn", Text(YesNoColumn)), txtYesNoColumn="true"), "txtYesNoColumn")

 

YMMV

 

Level 10

Hi @RandyHayes

The AddColumn function cannot be delegated. The warning has disappeared but the problem is still there. You create a column only for the 500 first items of the list so your filter output is incomplete.

 

Theo@

Super User

@tchin-ninyes totally agree on the delegation part, and mentioned that was not so much an issue in our cases, but yes, it is a factor.

I have also found that filtering on the opposite will provide accurate results. 

Ex:   Filter(dataSource, YesNoColumn<>true) for when you want "no" fields

and Filter(dataSource, YesNoColumn<>false) for when you want "yes" fields.

These example return accurate results at least.

However again...delegation issue.

The only workaround I know of for that, right now, is to go to the data source and create a calculated numeric column based on the value of the Yes/No column.  Numeric columns are delegable. 

Level: Power Up

This is a no-brainer, why isn't this fixed yet?

JRG
Level: Powered On

@RandyHayes  
This worked for me, Thank you!

I always find that resorting back to text helps get around things.  I tried in this case the following:

    Filter(SPList, Text(YesNoColumn)="true") 

Level: Powered On

Need it to work!