cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
galdezanni
Advocate II
Advocate II

Filter gallery three hierarchical SQL tables (3 levels)

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"

Powerapps_Filter_Gallery_II.PNG

 

 

 

 

Powerapps_Filter_Gallery.PNG

 

 

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?

2 REPLIES 2
v-xida-msft
Community Support
Community Support

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,

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.
PaulD1
Super User
Super User

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,733)