Filter gallery three hierarchical SQL tables (3 levels)

[galdezanni]

Good afternoon,

I need to filter a gallery from three hierarchical SQL tables (3 levels) and related by the respective ID, which are:

1: "Area"
2: "Subarea"
3: "Theme"

The gallery is being fed by another "Demand" table that has the column with ID only from the "Theme" table (level 3), however I need it to be possible for me to filter all the items in this "Demand" table through the dropdown of three other tables ("Area", "Subarea" and "Theme").

I wish the user could filter using one, two or three dropdowns - remembering that the three are cascading.

Would it be possible to create a collection combining the three pieces of information?

Would you run this filter without having to create a collection?

[v-xida-msft]

Hi @galdezanni ,

Are these Dropdown boxes cascading Dropdown box?

Do you want to filter the Gallery Items based on the 'idTema' column in your "Demand" table?

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the "Area" Dropdown to following:

'[dbo].[Area]'

Set the Items property of the "Subarea" Dropdown to following:

Filter('[dbo].[Subarea]', idArea = AreaDropdown.Selected.idArea)

Set the Items property of the "Theme" Dropdown to following:

Filter('[dbo].[Tema]', idSubarea = SubareaDropdown.Selected.idSubarea)

Set the Items property of the gallery to following:

Filter('[dbo].[Demanda]', idTema = ThemeDropdown.Selected.idTema)

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

Best regards,

[PaulD1]

Create a View in SQL that joins the tables so that you have columns for the AreaID and SubAreaID (containing their IDs).

In the Items property of you Gallery place a nested set of If statements that first tests to see if the theme Combo is populated, if so filter the ThemID where it is equal to the cboTheme selected ID. - else -

If SubArea is selected, filter by SubArea else 

If Area is selected filter by Area else

Apply no filter (show all records)

Hope that makes sense, if it doesn't put you on the right path reply here and I'll put an example together.