cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KamhinHo
Frequent Visitor

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

Hello all, 

 

I have two SharePoint lists:

 List A:

Function NameFunction ID
Func_A0
Func_B1
Func_C2

 

List B:

Category NameCategory IDFunction ID
Cat_A01
Cat_B11
Cat_C22
Cat_D30

 

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. 🙂 

2 ACCEPTED SOLUTIONS

Accepted Solutions
TheRobRush
Super User
Super User

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>)
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

View solution in original post

RandyHayes
Super User
Super User

@KamhinHo 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
TheRobRush
Super User
Super User

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>)
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
RandyHayes
Super User
Super User

@KamhinHo 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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 NameSubCategory IDCategoryID
Subcat_A01
Subcat_B11
Subcat_C22
Subcat_D31

 

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

RandyHayes
Super User
Super User

@KamhinHo 

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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,279)