cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Filter Dropdowns based on other dropdown selections?

Hi, I am having trouble with filtering my data table correctly. First, when on screen it should just show default all the data and then if you choose a selection from the first dropdown then all the following dropdown choices will be pre filtered based on whats available given your first dropdown selection. 

 

here are the dropdown selections 

bhanney2323_0-1599761446770.png

 

Another user on here helped me out and came up with this formula, which works to a degree, but there are delegation issues which is fine, but if its possible to do this without delegation issues that would be great. And second, this formula seems to work for one or two dropdowns but the more dropdowns you add, it just seems to fall apart and not work. 

 

This is the inner diameter dropdown items property

Filter(
        In_Diam,
        Result in Filter(
                         '[dbo].[970_item_format_item_mast]',
                         If(
                            mat_compounddd.Selected.Result = " - ",
                            true,
                            material_compound = mat_compounddd.Selected.Result
                         ),
                         If(
                            outerdd.Selected.Result = 0,
                            true,
                            outer_diameter = outerdd.Selected.Result
                         ),
                         If(
                             cross_sectiondd.Selected.Result = 0,
                             true,
                             cross_section = cross_sectiondd.Selected.Result
                         ),
                         If(
                             Heightdropd.Selected.Result = " - ",
                             true,
                             height = Heightdropd.Selected.Result
                         ),
                         If(
                             mat_manufacturerdd.Selected.Result = " - ",
                             true,
                             material_manufacturer = mat_manufacturerdd.Selected.Result
                         )
                  ).inner_diameter
)

 

This is the outer diameter items property

Filter(
        Out_Diam,
        Result in Filter(
                         '[dbo].[970_item_format_item_mast]',
                         If(
                            mat_compounddd.Selected.Result = " - ",
                            true,
                            material_compound = mat_compounddd.Selected.Result
                         ),
                         If(
                             cross_sectiondd.Selected.Result = 0,
                             true,
                             cross_section = cross_sectiondd.Selected.Result
                         ),
                         If(
                             Heightdropd.Selected.Result = " - ",
                             true,
                             height = Heightdropd.Selected.Result
                         ),
                         If(
                             mat_manufacturerdd.Selected.Result = " - ",
                             true,
                             material_manufacturer = mat_manufacturerdd.Selected.Result
                         )
                  ).outer_diameter
)

This is the cross section items property

Filter(
        Cross,
        Result in Filter(
                         '[dbo].[970_item_format_item_mast]',
                         If(
                            mat_compounddd.Selected.Result = " - ",
                            true,
                            material_compound = mat_compounddd.Selected.Result
                         ),
                         If(
                             Heightdropd.Selected.Result = " - ",
                             true,
                             height = Heightdropd.Selected.Result
                         ),
                         If(
                             mat_manufacturerdd.Selected.Result = " - ",
                             true,
                             material_manufacturer = mat_manufacturerdd.Selected.Result
                         )
                  ).cross_section
)

And finally this is the height items property

Filter(
        colheight,
        Result in Filter(
                         '[dbo].[970_item_format_item_mast]',
                         If(
                            mat_compounddd.Selected.Result = " - ",
                            true,
                            material_compound = mat_compounddd.Selected.Result
                         ),
                         If(
                             mat_manufacturerdd.Selected.Result = " - ",
                             true,
                             material_manufacturer = mat_manufacturerdd.Selected.Result
                         )
                  ).height
)

The last dropdown doesnt really matter because theres only 4 options to begin with and the first dropdown if I could get that to be filterable like the other ones that would be fantastic too. btw this data is pulling from SQL server. Hope I provided enough details and wasnt too confusing

 

 

7 REPLIES 7
Highlighted
Dual Super User III
Dual Super User III

Hi @bhanney2323 

 

Can you share more details about where your formula are breaking? At a first glance, I think the expressions are syntactically correct. Regarding delegation, can you confirm if the Delegation limit is set to 2000 in the app settings? If you can share more details about the issue, we will be able to help you better.

 

Hope this helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted

So yes i can confirm that the delegation settings are max at 2000 and it shouldn't be pulling back that many anyway. The delegation warning is the usual "The filter part of this formula might not work correctly on large data sets" 

 

The errors I get is for example if I were to add in the inner diameter to the outer diameter formula like so: 

Filter(
        Out_Diam,
        Result in Filter(
                         '[dbo].[970_item_format_item_mast]',
                         If(
                            mat_compounddd.Selected.Result = " - ",
                            true,
                            material_compound = mat_compounddd.Selected.Result
                         ),
                         If(
                             cross_sectiondd.Selected.Result = 0,
                             true,
                             cross_section = cross_sectiondd.Selected.Result
                         ),
                         If(
                             Heightdropd.Selected.Result = " - ",
                             true,
                             height = Heightdropd.Selected.Result
                         ),
                         If(
                             mat_manufacturerdd.Selected.Result = " - ",
                             true,
                             material_manufacturer = mat_manufacturerdd.Selected.Result
                         ),
                         if(
                             innerdd.Selected.Result = 0,
                             true,
                             inner_diameter = innerdd.Selected.Result
                         )
                  ).outer_diameter
)

I will get an error message like this: "This rule creates a circular reference between properties, which is not allowed. A property cannot reference itself or other properties affected by its value."

Like i said before too, this is a failed formula, so I'm more than open to a completely new way to go about doing this, I'm just kinda stumped. The goal here is to just have it so when you choose a filter off of one of the dropdowns the others will populate only what is now available based on the selection of each other. Thank you so much for the help btw! Let me know if you need anything else from my end to make it more clear.

Highlighted

Not trying to push this, but any updates? If more information or context is needed I'd be happy to help, I just want to know if this something that is even possible

Highlighted

@bhanney2323 

 

As the error suggests, there is a circular reference created, if Indiameter is changed then it will change the Out-Diameter options and then when Out-Diameter is changed, it will again change the In-Diameter. There is an infinite loop that will be followed due to this. Can you share the order in which you want to cascade the dropdown, we can help you build that hierarchy without the circular reference.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted

Thanks for that explanation, that makes sense. I guess for order it would be inner, outer, crosssection, height and then the others but those would be the core filters needed 

Highlighted

@bhanney2323 

 

are you able to resolve the issue? If not, please share more details so that we might be able to help you better.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted

No I am not able to resolve this, you told me to give you the order of the drop downs to try cascading it, please see my above post.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (15,942)