Filtering on Booleans from SharePoint lists

One of the easiest variables to use/test against in all of programming are Boolean values.  Those are true/false, yes/no or the classic ones and zeros.  And yet, things are not so simple when trying to filter records returned from SharePoint.

When working with lists returned from a Data Source, you commonly link these to a Data Table in PowerApps.  The standard functions you use are outlined here:

• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

They are:

• Filter: Only return records who match certain criteria
• Search: Similar to Filter but more search-field friendly
• Lookup: Only return one record and optionally return a single field from that record

For the most part, these work pretty well.  However, there's a weird little problem when it comes to doing a Filter on Boolean values.  

The problem

You cannot use a Boolean value from PowerApps to match against a Boolean in SharePoint.  Attempting to do this will return results from SharePoint that are False every time.  It will never return values that are true.  What's even sillier is that the results from SharePoint which are False are returned in all cases.  You might even want to read that again before pounding your head on your desk.

So this will not work:

• Filter('MySharePointList',MyBooleanColumn=true)

Where MySharePointList is the name of your data source and MyBooleanColumn is the name of your column in SharePoint.

The Fix

When comparing using a Filter use a 1 or 0 vs. a Boolean.

• Filter('MySharePointList',If(true,MyBooleanColumn=1,MyBooleanColumn=0))

Now of course, you'd replace true above with your global variable or Boolean test, but this will at least confirm that the fix works.

Final Thoughts

This does actually lead into a decent consideration of how to structure your SharePoint data.  Many people have run into repeated issues w/ SharePoint's data types and that some of them are non-standard.  The fact that BOOOOOOOLEANS are somehow nonstandard should be a good reminder.

When in doubt and for data that your application cares about, try to use plain text and number values.  Even Rich Text and HTML values can cause problems for PowerApps so best to steer clear where you can (until they ultimately sort aspects of this out in some future release).