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

combo boxes filtering on different combo boxes

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

12 REPLIES 12
Super User II
Super User II

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 

Super User III
Super User III

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.

Community Support
Community Support

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.

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

 

 

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.

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

 

Dan

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.

Hi again,

 

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

 

Thanks,

Dan

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.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (12,961)