cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OwninJoe
Helper I
Helper I

Filter Items for DropDown list based on SharePoint list

I have a Gallery linked to a SharePointList that I want to show, but I need this gallery to be filterable based on a multi-select dropdown. One column that I want to filter on is a text column that can be empty, so I have the following code to create a collection based on the column from the SharePoint list, and then filter that column down to only entries that are not blank, empty, or null. However this isn't working and I'm still getting the full list, which means the dropdown has empty values inserted into it.

 

 

Refresh('MySPList')
ClearCollect(FilterCollection,'MySPList'.'Name');
Filter(FilterCollection, Not(IsBlank('Name')));

 

 

Am I doing something wrong? This code is located in the OnVisible method of the screen the gallery and search box are contained in. I would also like to go further and only get distinct, non-empty values to display in the dropdown. Is this possible?

 

1 ACCEPTED SOLUTION

Accepted Solutions
CapstoneAdmin
Advocate II
Advocate II

It looks like you're filtering, but not setting that to anything you can use. Something like this would make it usable:

 

Refresh('MySPList')
ClearCollect(FilterCollection,'MySPList'.'Name');
ClearCollect(DropDown1Items,Filter(FilterCollection, Not(IsBlank('Name'))));

 

Then you can set the dropdown items to DropDown1Items. Alternatively, you can set the dropdown items to:

Filter(FilterCollection, Not(IsBlank('Name')));

 

 Filter is not a permanent function, it doesn't filter the collection for good unless you set it to something.

View solution in original post

10 REPLIES 10
CapstoneAdmin
Advocate II
Advocate II

It looks like you're filtering, but not setting that to anything you can use. Something like this would make it usable:

 

Refresh('MySPList')
ClearCollect(FilterCollection,'MySPList'.'Name');
ClearCollect(DropDown1Items,Filter(FilterCollection, Not(IsBlank('Name'))));

 

Then you can set the dropdown items to DropDown1Items. Alternatively, you can set the dropdown items to:

Filter(FilterCollection, Not(IsBlank('Name')));

 

 Filter is not a permanent function, it doesn't filter the collection for good unless you set it to something.

the filter should go on the items property of the gallery. the ClearCollect should go in the OnVisible property of the form or the on start of the app or on some event on a button or other event in the app.

Thanks so much! this worked, I had no idea that I wasn't actually filtering the list like I thought I was. If I wanted to take this further, how would I filter the distinct values? Would it be like this:

 

ClearCollect(DropDown1Items,Distinct(FilterCollection, 'Name'));

 

however this doesn't seem to work. Any thoughts? 

distinct just returns a single value.

 

You can use GroupBy to get distinct with multiple columns. see https://powerusers.microsoft.com/t5/Building-Power-Apps/Distinct-with-multiple-columns/td-p/322204

It was my understanding that Distinct returned all the distinct values in the column specified.

ClearCollect(DropDown1Items,Distinct(FilterCollection, 'Name'));

From this code, FilterCollection is a 1 column table:

ClearCollect(FilterCollection,'MySPList'.'Name of Patient (or Visitor) Involved');

ClearCollect(DropDown1Items,Filter(FilterCollection, Not(IsBlank('Name'))));

 So I would assume that by running the code above, the DropDown1Items would be updated with the Distinct values from the only column in the table, which would be 'Name's. However this isn't returning any values at all.

If this is your code:

 

ClearCollect(FilterCollection,'MySPList'.'Name of Patient (or Visitor) Involved');
ClearCollect(DropDown1Items,Filter(FilterCollection, Not(IsBlank('Name'))));
ClearCollect(DropDown1Items,Distinct(FilterCollection, 'Name'));

 

I see some issues. After line 1, FilterCollection will be a one-column collection with the only column titled "Name of Patient (or Visitor) Involved". So, line 2 looks like it will be looking to filter on a column called "Name" which it won't be able to find. Line three will basically discard the filtering of line 2 because both lines 2 and three point back to line one but do different transformations on it. This is probably what you need:

 

ClearCollect(FilterCollection,'MySPList'.'Name of Patient (or Visitor) Involved');
ClearCollect(DropDown1Items,Filter(FilterCollection, Not(IsBlank('Name of Patient (or Visitor) Involved'))));
ClearCollect(DropDown1Items,Distinct(DropDown1Items, 'Name of Patient (or Visitor) Involved'));

Or this:

ClearCollect(FilterCollection,'MySPList'.'Name of Patient (or Visitor) Involved');
ClearCollect(DropDown1Items,Distinct(Filter(FilterCollection, Not(IsBlank('Name of Patient (or Visitor) Involved')),'Name of Patient (or Visitor) Involved'));

I apologize, those typos were my fault, all the columns should be named 'Name'. Regardless, doing what you suggested actually emptied out the Dropdown giving me no options to select.

I have it working on my side:

 

ClearCollect(FilterCollection,LikelihoodEntryList);
ClearCollect(Dropdown1Items,Distinct(Filter(FilterCollection,Not(IsBlank(Title))),Title));

 

CapstoneAdmin_0-1595444570532.png

Where this is my list:

CapstoneAdmin_1-1595444608966.png

Can you share screenshots from your end?

 

Oh and I should say that after using "Distinct" that the output column name becomes "Result" so you need to update the dropdown properties to reference the "Result" column:

 

CapstoneAdmin_0-1595444717006.png

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,255)