Filtering List Box based on the multiple selected values from another Combo Box

[Anonymous]

Hello all, 

I have two SharePoint lists:

 List A:

Function Name	Function ID
Func_A	0
Func_B	1
Func_C	2

List B:

Category Name	Category ID	Function ID
Cat_A	0	1
Cat_B	1	1
Cat_C	2	2
Cat_D	3	0

I have a Combo Box (CB_Function) created for List A in which the user will select multiple Function Names. I want to filter the List Box (LB_Category) based on the user-selected Function Names from the combo box. For example, if the user selected "Func_A" & "Func_B", then the List Box will have Cat_A, Cat_B, Cat_C based on the Function ID from List 2. 

I was using Filter(List B, 'Function ID' = CB_Function.Selected.'Function ID') in the List Box (LB_Category) Item attribute, but it only works if the user selected one Function. If I selected multiple functions in the combo box, I only get the associated category of the last selected function. 

My question is how do I achieve the example mentioned above?

Thank you for responding to the post in advance. 🙂 

[TheRobRush · Accepted solution]

ComboBox set to my SPList TMROSTER

Gallery items set to (Yours will swap to the Secodn Datasource, but just check and make the First part of title in the column from that datasource, then second part references the combobox)

Filter(TMROSTER, Title in ComboBox11.SelectedItems.Title)

Filter(<Second Datasource>, <Second Datasource Column> in ComboBox11.SelectedItems.<ComboBox datasource column to check>)

[RandyHayes · Accepted solution]

@Anonymous 

Set your Items property of the Combobox to:

AddColumns(ListA As _item,
    "_categories", Filter(ListB, 'Function ID' = _item.'Function ID')
)

Then set your Listbox Items to:

Ungroup(ForAll(yourComboboxName.SelectedItems, {Items: _categories}), "Items")

I hope this is helpful for you.

[Anonymous]

Hello Randy, 

Thank you for your help! It works! Do you mind explaining to me what is the concept behind your solution? Are we adding a column to my ListA? Is the column temporary or it will be stored in the ListA? What does the Listbox syntax do?

Also, I have a follow-up question:

I have another Sharepoint List which is one granularity down:

List C:

SubCategory Name	SubCategory ID	CategoryID
Subcat_A	0	1
Subcat_B	1	1
Subcat_C	2	2
Subcat_D	3	1

How could I utilize your solution to get the data for the subcategory name with a List Box?

[RandyHayes]

@Anonymous 

When working with data in PowerApps it is all about data shaping - creating tables and records in a way that you can best use them through your app.  These formulas are important to know when working with PowerApps as they will be a key element of your design.

The functions do NOT alter the datasource.  They alter the table returned from the datasource to return a table that is shaped as you like.  In this case, adding a column.  

Example:

Set(aList, ListA);
Set(anotherList, AddColumns(ListA, "_columnX", ColumnValue))

This is not a practical example, just a demonstration!

From this formula, there will be two variables produced.  aList will be just a table of records duplicated from ListA datasource.  anotherList is based on the same datasource but will have one additional column called _columnX.

The method that I demonstrated is one that encapsulates the actions to the datasource in one place.  Be cautious about this.  It is highly practical in many places, but if the datasources grow to large sizes, then it can also be a source of performance issues.

There are essentially two ways to go about it:

1 Is the method that I showed, where you gather all the data in one place and then reference it from other controls.

2 is the method that @TheRobRush demonstrated, where you have your other controls filter from the datasource based on the value selected in the primary control.  That method works just as well.  In that case though, every time the control value is changed, the other control will need to perform datasource actions to get its values.

Always choose the pattern that is best based on your data source...or you can use a combination of the two.

So for your sub categories, you can use either method as well.

Using a combination would be like this - Since you are showing a listbox for the categories, then you can establish another listbox for the subcategories. 

You would then change the categories listbox items property to:

AddColumns(
    Ungroup(
        ForAll(yourComboboxName.SelectedItems, {Items: _categories}), 
        "Items"
    ) As _item,
    "_subcategories", Filter(ListC, 'Category ID' = _item.'Category ID')
)

You will notice above that the Filter function (a datasource action) is now in this Items, so it will need to perform each time the primary selections change.  This is a hybrid of the two methods.

The Subcategory list would have an Items property of:

Ungroup(ForAll(yourCategoryListName.SelectedItems, {Items: _subcategories}), "Items")

You could have ALL of the datasource actions in one place, the primary combobox Items property, but then you will have a lot of data actions all at once, and that starts to eat into performance.

Performance in PowerApps is an art...not a science, so you always have to evaluate methods that work best and be in a position to change those patterns based on your results.