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.

View solution in original post

jlindstrom
Super User
Super User

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (39,882)