cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alockar2
Regular Visitor

Filter will not filtering properly with Sharepoint list boolean value

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?

1 ACCEPTED SOLUTION

Accepted Solutions
DerekN
Frequent Visitor

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

View solution in original post

12 REPLIES 12
v-yamao-msft
Community Support
Community Support

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

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yamao-msft
Community Support
Community Support

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

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Frequent Visitor

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

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

Hi,

 

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

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.

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

DerekN
Frequent Visitor

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

DerekN
Frequent Visitor

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (1,692)