Filter gallery three hierarchical SQL tables (3 levels)
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?
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.