Filter will not filtering properly with Sharepoint list boolean value

[alockar2]

Hi, I have something of an odd situation. I'm trying to setup my BrowserGallerie's Data Filter so that it is delegated. I have a Yes/No (boolean value) item in a sharepoint list (Task Completed) that I am trying to filter on. However, when using the only delegation method, according to this list: https://powerapps.microsoft.com/en-us/tutorials/delegation-list/, I have available to me (=) it isn't working.

For example. I have a view where I want to see recently completed tasks so for the filter part I am trying to use Filter('Task List', Task_x0020_Completed = true) however it does not show tasks that are completed but rather only those that are not completed. It seems to only provide results that are false rather than true and I can't seem to figure out why. Does anyone have any idea why this is happening?

[v-yamao-msft]

Hi alockar2,

Thanks for feedback.

I can reproduce this issue on my side.

I have a SharePoint list which has a Yes/No column in the list.

When I filter using the Yes/No column, it only returns results that are false rather than true.

I have seen similar issue on other threads. I will help report this issue on my side and back to you once I got any updates.

Best regards,
Mabel Mao

[v-yamao-msft]

Hi alockar2,

I have some updates for you.

Please try to filter using 0 or 1 instead of true/false.

I have tested it on my side and it is working.

Please also try it on your side and let me know if it also works for you.

If I have any other updates on this issue, I will back to you.

Best regards,
Mabel Mao

[alockar2]

Using a 1 or 0 appear to work. However, it does complain about mismatched types. The left being a boolean and the right being a number. Is that something I can fix? Is it something I should worry about? According to the IDE it's simply a suggestion vs an error.

[LauraLee]

Hello Alockar2,

Did you find a solution to this?

I am seeing the same problem and it is impossible to show results that are true.

Many thanks

Laura

[alockar2]

Yep, switching to using 0 for false and 1 for true did the trick for me.

[LauraLee]

Hi,

Thanks for that idea, it does work, but then my patch function no longer updates SharePoint with the checkbox values 

If I use True / False in the filter then patch function works.

It seems like using 0/1 changes the control.

I am looking at workarounds - maybe another checkbox with duplicate values to use for the filter if I have to.

[NigelP]

Has this been fixed yet ?  Do we have an ETA ?

[DerekN]

0/1 does not work for me.  I am blown away that this doesn't work.

[DerekN · Accepted solution]

I found a really reasonable workaround for this issue.  The key is to create a new collection from the SharePoint list data source that you are tyring to filter on.  Since collections are internal to PowerApps you don't run into any delegation issues when filtering.  Rather than making assumptions about the OP's full scenario, I'll describe what I did for mine.

I use a SharePoint list called "Time Entry" to track the time I spend for each project.  The "Time Entry" list has a "Client Name" field that is a lookup to a SharePoint list called Clients, and a Project field that is a lookup to a SharePoint list called "Projects".  In the "Projects" list the "Title" field holds the name of the project, the "Client" field holds the name of the client, and I have an "Active" field that is a boolean that indicates if the project is still in progress.

On my "Time Entry" form I wanted to filter the list of projects in the "Project" field by the client selected in the "Client Name" field and only show projects that are still active.  Here are the steps I took.  You can use the attached screenshot as a visual reference:

1. Create a new data source that connects to the SharePoint list that holds the values you want to filter on.  In my case I created a data source that connects to a list called "Projects".
2. Create a collection to hold a copy of the list that you want to filter based on.  In this case I created a collection called localProjects that holds a copy of my SharePoint data source list "Projects".  I did this by putting the following formula in the OnSelect action for the Project dropdown on my "Time Entry" form: ClearCollect(localProjects, Projects)
3. Next setup the filters.  The formula requires an inner Filter and an outer Filter.  Here is the formula I used: Filter(Choices('Time Entry'.Project), Value in Filter(localProjects, Active=true && Client.Value=DataCardValue1.Selected.Value).Title)

The formula can be kind of confusing if you are new to PowerApps (like me).  I got the idea for using "Value in Filter..." from this post https://veenstra.me.uk/2018/06/21/powerapps-filter-your-drop-downs-by-other-fields/.

The basic breakdown of the formula is that it filters the Choices based on whether or not they can be matched to an active project in the localProjects collection.  The .Title on the end of the inner Filter returns the names of projects (recall from above that my Projects list uses the Title field to hold names of projects) from the localProjects collection that match the filter I specified (ie. "Active=true && Client.Value=DataCardValue1.Selected.Value").  Then, the outer Filter checks to see if there is a  match for the Value in the Project dropdown.

I hope that is all clear enough!

Derek