Filter Items for DropDown list based on SharePoint list

[OwninJoe]

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?

[CapstoneAdmin · Accepted solution]

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.

[jlindstrom]

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.

[OwninJoe]

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? 

[jlindstrom]

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

[OwninJoe]

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.

[CapstoneAdmin]

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'));

[OwninJoe]

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.

[CapstoneAdmin]

I have it working on my side:

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

Where this is my list:

Can you share screenshots from your end?

[CapstoneAdmin]

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: