Power Apps: filtering a SharePoint list on a multiple values lookup column using a multiple selections Combo Box or Drop Down
In this scenario, we have a Document library with the Document Set content type added and we are not allowing folder creation, so any folders in the library are document set.
The Scenario:
We have a lookup list that contains all the items a document set can be associated with. These could be categories, physical items, individuals, teams, locations, etc. In this example, we are using a list of Medical Supplies that could be ordered for various rooms in a medical facility. Each document set represents a room in the facility.
The lookup list looks like this (we will use the single line of text column called “Supply” as the content for the document sets’ lookup column:
Now, in our document library with the “Document Sets” feature enabled, we will allow, via the lookup column “Supplies” one or more of these supplies to be associated with the Doc Set as shown below:
The Challenge:
The challenge for our Power App is that we want a user to be able to select (using a combo box) from one or more of the supplies available in the MedicalSupplyCatalog list, and have a second combo box show a list of document sets that contain one or more of those supplies in the Supplies lookup column.
The challenge here is that we are filtering an array of choices using an array of choices, so we can’t do something simple like Filter(DocSets, ComboBox1.SelectedItems.Value in Supplies), as this is not valid functionality. There is no built-in way to handle filtering an array of values using one or more of the values from another array.
A solution:
At a high level, we will use the OnChange for the first Combo Box (we can call it cbxSupplies) and we will clear a collection ( we have called colDocSets), then loop through each selected Supply in cbxSupplies and append to the colDocSets collection all of the rows from the DocSets library that have that supply associated with them. This will build a collection that has a lot of duplicate rows, but DOES contain all of the matching rows from each selected supply.
NOTE: we could just pull the DocSets row ID into this colDocSets collection and not the whole row. Also any additional filtering you want applied can be applied at this step
Next, we will get just the distinct rows (and really just the ID column for those rows) into a second collection (here we called that collection colDistinctDocSetIDs).
Finally, our second Combo Box (we can call is cbxDocSets) will filter the DocSets library using the logic: DocSets.ID in colDistinctDocSetIDs, and this will return the Document Sets matching our collection of Library row IDs (the built in ID column).
Details:
The first Combo Box, cbxSupplies
==================================
Items:
//////////////////
Choices(DocSets.Supplies)
//////////////////
OnChange:
//////////////////
Clear(colDocSets);
ForAll(
CbxSupplies.SelectedItems.Value,
Collect(
colDocSets,
Filter(
DocSets As DS,
ThisRecord.Value in DS.Supplies.Value
&& DS.IsFolder = true
//&& DS.’Content type’.Id = “0x0120D52000086BFBB12BBD47408B786494624C2E54”
//Document Set content type ID (could use instead of isFolder if the library allows folders)
///Place any additional filtering requirements here.
)
)
);
ClearCollect(
colDistinctDocSetIDs,
Distinct(
colDocSets,
ID
)
);
//////////////////
Sometimes color makes the logic more clear. Here is the “OnChange” property again. Note that in my App, i also put this logic on the Screen->OnVisible property to handle choices being pre-populated in the first drop down.
Next, the second Combo Box cbxDocSets
===========================
Items:
///////////////////////
Filter(DocSets As DS, DS.ID in colDistinctDocSetIDs)
///////////////////////
Conclusion:
Here, we have shown how you can filter an array of user selected choices into a column with a mismatched array of values and identify all of the rows in the list or library that have one or more of the user selected values in the list’s multiple values choice or lookup column.
This same logic can be used for any scenario where you have a multi-valued column that you want to filter on using a multi-select drop down and you want to match on ANY of the choices selected in the drop down. i.e. a many to many filtering scheme using Power Apps cascading Drop Downs.