I modified the concepts app to have 2 levels of filters. First there is the existing Region filter. When you select a Region, it will modify a relation column that is used to show the Divisions within that region. You can then further filter to a specific Division within that Region. It’s a little hard to explain, but I’ll give it my best shot.
- My first Choice Component List uses the Region column in DynamicFilterChoices. This is the easy part.
- Next In the DynamicFilterUser sheet, I have columns for Region and Division. This holds the filter selections that the user selected from the two Filter Choice Components.
- Next I created a tmp-RegionDivision Template Column that joins the Region and Division. I’ll explain the use of this later.
- Next I created the rel-RegionStateList Relation Column that links the Region Column to the Region Column in the DynamicFilterStateDetails sheet. This relation contains all of the State rows within that selected Region and is used for an inline list with the map style layout that will show all of the states in that region.
- Next I created a lkup-RegionStateDivision Lookup Column that uses the rel-RegionStateList relation to get an array list of Divisions that are contained in the selected Region’s States.
- Next I created the rel-RegionDivisionFilter Relation Column which links the array list of Divisions in the lkup-RegionStateDivision Lookup Column to the Divisions Column in the DynamicFilterChoices sheet. This relation gives me all of the Division Choice rows that match all of the Divisions in the State rows from the selected Region This relation is used to build the second Choice Component of Divisions that are contained within the selected Region.
- Next I created the rel-RegionDivisionStateList relation column. This links the tmp-RegionDivision Template Column created earlier to a tmp-RegionDivision column created in the DynamicFilterStateDetails sheet. This is also uses to create an inline list with the map style layout to list all of the States in the selected Region and Division.
- Now that I have 2 inline list maps, I change the Visibility on the first one (that uses rel-RegionStateList) to only display if the rel-RegionDivisionStateList is empty. This means that this map will only show only if the relation built from the selected Region and Division DOES NOT have any matching Region and Division combinations in the DynamicFilterStateDetails sheet. If the user changed regions, this updates the Region column, but the Division column still contains a value from the previously selected Division. This means that the Division is not found in the newly selected Region and the relation will be empty.
- Finally I change the Visibility on the second inline list (that uses rel-RegionDivisionStateList) to only display if the rel-RegionDivisionStateList is NOT empty. This means that this map will show only if the relation built from the selected Region and Division DOES have any matching Region and Division combinations in the DynamicFilterStateDetails sheet.
I hope this makes some sense and I think it’s totally possible with the 3 levels of filters you want. It may take an exponential amount of columns to achieve the same thing I did, but it might also be simpler if your requirement is to have all 3 filters selected before showing results. I hope this helps and gives you ideas. Thanks for the challenge!
