Solved: combo boxes filtering on different combo boxes

Dan14 · ‎05-26-2020

Hello everyone.

I am building an app with several combo boxes. The gallery gets filtered based on the combo boxes. But i also need the combo boxes to depend on one another. When I am filtering on combo box 1, I need to see in combo box 2 only the values linked to the selection in combo box 1.

The items on my combo boxes are all Collections.

Could you help me out with a way to link the collections together and make them dependent on the selection in each combo box?

Thank you,

Dan

v-xida-msft · ‎05-28-2020

Hi @Dan14 ,

Could you please share a bit more about cProgram, cInitiative and cBundle? Are they all data sources?

Based on the formula you provided, I think there are something wrong with the formula you typed within the Items property of the cbProgram_1, cbInitiative_1 and cbBundle_1.

For cbProgram_1 Items property:

Distinct(
     Filter(
        '[dbo].[SQL Table]',
        If(
           !IsBlank(SelectedColumn2),
           INITIATIVE = SelectedColumn2,
           true
        ),
       If(
          !IsBlank(SelectedColumn3),
          CURRICULUM_BUNDLE_NAME = SelectedColumn3,
          true
       )
    ),
    PROGRAM_NAME
)

For cbInitiative_1 Items proeprty:

Distinct(
     Filter(
        '[dbo].[SQL Table]',
        If(
           !IsBlank(SelectedColumn1),
           PROGRAM_NAME = SelectedColumn1,
           true
        ),
       If(
          !IsBlank(SelectedColumn3),
          CURRICULUM_BUNDLE_NAME = SelectedColumn3,
          true
       )
    ),
    INITIATIVE
)

For cbBundle_1 Items property:

Distinct(
     Filter(
        '[dbo].[SQL Table]',
        If(
           !IsBlank(SelectedColumn1),
           PROGRAM_NAME = SelectedColumn1,
           true
        ),
       If(
          !IsBlank(SelectedColumn2),
          INITIATIVE = SelectedColumn2,
          true
       )
    ),
    CURRICULUM_BUNDLE_NAME
)

Please consider take a try with above solution, check if the issue is solved.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Drrickryp · ‎05-26-2020

Hi @Dan14

What you need is called "Cascading comboboxes". Please review this thread for how to set them up. Come back if you have a problem. https://powerusers.microsoft.com/t5/Building-Power-Apps/Cascading-Combo-Box/td-p/88172

eka24 · ‎05-26-2020

There is a video on filtering multiple dropdown and comboboxes by Shane Young:

https://youtu.be/kLGglidmPxg

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

v-xida-msft · ‎05-26-2020

Hi @Dan14 ,

Based on the needs that you mentioned, I think you want to create a cascading Dropdown within your canvas app. Please check and see if the following video resource could help in your scenario:

https://www.youtube.com/watch?v=wkEC_Wf69P0

For ComboBox1 Items property:

Distinct(Collection1, ColumnA)

For ComboBox2 Items property:

Distinct(
          Filter(Collection2, ColumnA_FK = ComboBox1.Selected.Result),
          ColumnB
)

....

same logic should be applied to your rest of your ComboBoxes.

Then within your Gallery, set the Items property to following:

Filter(
       'Your Data Source',
        If(
           IsBlank(ComboBox1.Selected.Result),
           true,
           Column1 = ComboBox1.Selected.Result
        ),
        If(
           IsBlank(ComboBox2.Selected.Result),
           true,
           Column2 = ComboBox2.Selected.Result
        ),
        ...
        ...
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dan14 · ‎05-27-2020

Thank you.

That is very helpful and also confusing 🙂

I already have the Items in Combo boxes set up as distinct in the collection Sort(cProgram,Result,Ascending)

And I already have the Items in Gallery set up as follows:

(Filter('[dbo].[Data_Source]',(If(IsBlank(cbProgram_1.Selected.Result),true,cbProgram_1.Selected.Result in PROGRAM_NAME) && If(IsBlank(cbInitiative_1.Selected.Result),true,cbInitiative_1.Selected.Result in INITIATIVE) && If(IsBlank(cbBundle_1.Selected.Result),true, cbBundle_1.Selected.Result in CURRICULUM_BUNDLE_NAME))

In your IF statement, false is

Column1 = ComboBox1.Selected.Result

In my IF Statement, false is cbProgram_1.Selected.Result in PROGRAM_NAME, since I am already filtering the Gallery based on whatever is selected in the combo boxes one by one.

What i additionally need is to show up only certain values in Initiative combo box for example, based on what's filtered in Program Name and vice versa.

If I use the formula you mentioned for the second combo box, I cannot select anything, even though the formula seems to be correct.

Really appreciate more feedback on this.

Thank you,

Dan

v-xida-msft · ‎05-27-2020

Hi @Dan14 ,

Do you want to filter ComboBox2 records based on ComboBox1 selected value, and also could Filter ComboBox1 records based on ComboBox2 selected value?

Based on the needs that you mentioned, I afraid that there is no direct way to achieve your needs. The user @Ashok2 has faced similar needs with you, please check my alternative solution within the following thread:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Cascading-Dropdown-in-powerapp/m-p/497202

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dan14 · ‎05-27-2020

Yes, Thank you! I will give it a try and let you know!

Dan

v-xida-msft · ‎05-27-2020

Hi @Dan14 ,

Sure. Please take a try with the alternative solution I provided above. If you have any question about it, please feel free to contact me here.

If the solution I provided above is helpful in your scenario, please consider go ahead to click "Accept as Solution" to identify my solution as helpful.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dan14 · ‎05-28-2020

Hi again,

Not sure what you mean by SelectedColumn1,2,3,4?

Thanks,

Dan

v-xida-msft · ‎05-28-2020

Hi @Dan14 ,

According to your needs, we could not reference the ComboBox Selected value within these ComboBox directly. Because, ComboBoxA is dependent on ComboBoxB, ComboBoxB is dependent on ComboBoxA, we could not reference the ComboBox.Selected.Value within these ComboBox directly, it would cause circle reference error.

So I used SelectedColumn1,2,3,4 variable to store the selected value within individual ComboBox, then reference the variable within the Items property of the ComboBox, it would get rid of the circle reference error between these ComboBox controls.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

combo boxes filtering on different combo boxes

Helpful resources

New Solution Badges!

Congratulations!

Power Apps Community Call: February

Microsoft Ignite