cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ameiya30
Regular Visitor

Filtering multiple comboboxes based on lookup columns dataverse on teams

Hi all, 

 

my first post here and relatively new to powerapps and dataverse and I've been stuck on how to filter multiple comboboxes based on each other. 

Scenario: 

I have multiple tables created on dataverse. Table 1 - Area, Table 2  - Category and Table 3 - Subcategory. Relation can be as below where in the Table 3 area and category are lookup columns of the previous tables. Table 3 is my primary data source.

AreaCategorySubcategory
AA1A1x
AA1A1y
AA2A2z
AA2A2w
BB3B3q
BB4B4a
CC5C5b

 

My comboboxes are 1. Area, 2. Category, 3. Subcategory. I'm trying to build formula where if in combobox 1 Area is selected combobox 2 Category will only show the options corresponding to that and combobox 3 Subcategory will also show only the subcategories based on category selected. 

Catch - if any previous combobox is not selected (any option chosen) I want the subsequent comboboxes to show all the possibilities. Anything like that possible? 

 

I got it partially working where 

Combobox 1 Items = Choices(Table3.Areacolumn)

Combobox 2 Items = Filter(Table3, Area.areacolumn = combobox1.Selected.areacolumn)

However I'm not sure how to add the option if combobox1 is not selected show all possibilities in combobox2 and so on. 

 

Any help/idea highly appreciated! Thank you!

5 REPLIES 5
v-jefferni
Community Support
Community Support

Hi @Ameiya30 ,

 

Based on your description, please try below steps:

1. Combobox1 Items: 

 

Area.areacolumn

 

 

2. Combobox2 Items:

 

If(
   IsBlank(Combobox1.Selected.areacolumn),
   Table2.category,
   Filter(Table3,
          Area.areacolumn = combobox1.Selected.areacolumn
   ).Category.category
)

 

 

3. Combobox3 Items:

 

If(
   IsBlank(Combobox1.Selected.areacolumn) || IsBlank(Combobox2.Selected.category),
   Table3.Subcategory,
   Filter(Table3,
          Area.areacolumn = combobox1.Selected.areacolumn
          &&
          Category.categoty = combobox2.Selected.category
   ).Subcategory
)

 

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni , 

 

Thanks for you reply! I followed the steps but I still can't quite get it working, not sure if I'm making some mistake or what is the problem. 

 

It works if I follow your steps and use only 2 tables and 2 comboboxes where the second one looks like this:

Ameiya30_1-1636653219529.png

 

 

If I'm trying to expand it as per your guidance for 3 comboboxes and using all 3 tables I have following problem:

Combobox 2 ->  while using Filter(Table3..) if combobox 1 selected, combobox 2 is blank/empty, meaning doesn't display any options. However, if nothing is selected in combobox 1 then combobox 2 shows all the options as expected.

AND

Combobox 3 -> doesn't give me option to choose any column/anything after Selected.____ as shown in red below ergo I cannot finish the formula. When I try to type it out myself error message saying that "Identifier isn't recognized"

 

Ameiya30_2-1636653678231.png

 

Again thanks in advance for any help or guidance! Much appreciated!

Hi @Ameiya30 ,

 

Please provide all the three tables including all columns and column types that related to this thread so that I would understand your scenario more clearly.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni , 

 

Table 1 Area

  • only one column Area, text type, primary column

Table 2 Category

  • primary column Category, text type;
  • Area column lookup from Table 1

Table 3 Subcategory 

  • primary column Subcategory, text type;
  • Category column lookup from Table 2;
  • Area column lookup from Table 1;
  • additional columns 2 text types and 1 URL , which are used for Gallery to display info based on the comboboxes selection. 

 

Note - I was also considering using choice columns in table 3 for area and category instead of separate tables but having separate tables and using lookup columns seemed as a better option at the time.

Hi @Ameiya30 ,

 

Sorry for my late. But I would say that your table structure need to be modified because if you have defined the corresponding area of each category in table 2, then you should not make the area column in table 3 look up from table1 and let users select freely (It might conflict with table2 items, right?). So please reduce a look up column in table3, it will tremendously simplify the formulas for the 3 combo boxes.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (2,941)