cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alockar2
Level: Powered On

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

Re: Filter will not filtering properly with Sharepoint list boolean value

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

11 REPLIES 11
Community Support Team
Community Support Team

Re: Filter will not filtering properly with Sharepoint list boolean value

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.
Community Support Team
Community Support Team

Re: Filter will not filtering properly with Sharepoint list boolean value

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.
alockar2
Level: Powered On

Re: Filter will not filtering properly with Sharepoint list boolean value

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

Re: Filter will not filtering properly with Sharepoint list boolean value

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

Re: Filter will not filtering properly with Sharepoint list boolean value

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

LauraLee
Level: Powered On

Re: Filter will not filtering properly with Sharepoint list boolean value

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.

NigelP
Level 8

Re: Filter will not filtering properly with Sharepoint list boolean value

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

DerekN
Level: Powered On

Re: Filter will not filtering properly with Sharepoint list boolean value

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

DerekN
Level: Powered On

Re: Filter will not filtering properly with Sharepoint list boolean value

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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 93 members 4,004 guests
Please welcome our newest community members: