5 Replies Latest reply on Jan 20, 2012 12:52 PM by bdm4639

    Cascading / Conditional / Tiered Drop Down  Dropdown (8-levels)


      I am having trouble setting up a parts database that will allow data entry via cascading dropdowns.

      As an example, a person may need to enter from 8 different dropdown selctions as follows:










      I have attempted to create this by using the "Include only related values starting from" option in Value Lists. It works for the first couple of levels, but then the dropdown starts showing a conglomerate of options not associated with the previous dropdown.

      I probably do not have the tables set up and joined correctly, or else I am using the wrong approach altogether.


      I have found numerious threads discussing multi-tiered drop-downs, but not with this many levels. I have searched and tried many different suggestions, but I am not able to reslove this by reading the other threads and looking at the other examples.

      Is this something can can be explained by showing me what the table relationship should look like?

        • 1. Re: Cascading / Conditional / Tiered Drop Down  Dropdown (8-levels)

          bdm4639, I have opted for smaller portals (each populated by the succeeding selection), rather than drop-down (or pop-up) formatting of fields when the "bread crumbs" (cascades) get more than 2-3 deep. I show the entire set of the selected "path" and allow, back-tracking of any part of it.

          Just my experience, YMMV (your mileage may vary).


          • 2. Re: Cascading / Conditional / Tiered Drop Down  Dropdown (8-levels)

            Thanks for the fast response.


            I am a novice.  I was about 80% lost with the drop-downs and about 95% lost with the portal idea.  I do not even know where to begin to set that up.  I tried to get it started but my results are worse than with the drop-downs.


            Is there a resourse that graphically shows an example of how a user can add a new record by selecting cascading portal options?

            • 3. Re: Cascading / Conditional / Tiered Drop Down  Dropdown (8-levels)



              I do think it may be a function of how your tables are set up.  I ended up with "complications" when I tried to go to three levels (not 8) to make it work.  What I was doing was Category --> Flags --> Flavors.  So based on the Category selected, a certain group of flags would be presented (as check boxes in my case) and then based on the selected Flag, different flavors would then be presented (again as check boxes but drop downs would also work).  And what was not "reflected" in what I just said was that the list of Categories and corresponding Flags and Flavors were different based on whether another field (color) was displaying one of 3 different choices.


              So my first table (List_Categories) had a list of records of each category name and corresponding color (if a category applied to more than one color, there were multiple records, such as Mineral/Red and Mineral/White).


              My second table (List_Flags) had Category, Flag and Color.  Again, if something could be multiple choices, then I had Herbal/Plant/White and Herbal/Plant/Red.  That allowed Plant to show up as a flag under the Herbal category whether the color was Red or White.


              My third table (List_Flavors) just had Flag and Flavor (the color "filter" was not applicable here).


              So here are three TOs on my graph.


              Screen Shot 2012-01-20 at 11.48.26 AM.png


              For Category, my first level, the Color was the "relevant" filter so here was my TO link.


              Screen Shot 2012-01-20 at 11.51.28 AM.png


              Then for Flags, I needed to related both on the Color AND the Category


              Screen Shot 2012-01-20 at 11.48.41 AM.png


              And then Flavors was like the first one, only b/c I no longer cared about color, only the relation to Flags.


              Screen Shot 2012-01-20 at 11.49.03 AM.png


              Then, for creating the value lists, now that I have the table relationships between the Lists and the "Wines" table, I was able to do the following for Categories.


              Screen Shot 2012-01-20 at 11.54.12 AM.png

              This pulls the Category field contents from List_Category based on the related values from Wines.  Since the relationship to Wines for List_Category is "Color", it will pull the values based on what color is listed for the record in the Wines table.


              Then for Flags, it looks like this:


              Screen Shot 2012-01-20 at 11.56.05 AM.png


              Now it is pulling the Flag field from the List_Flags table based on the related values from the Wines Table (which is now the Color AND the Category).


              For Flavors, I did the same thing for the Flavors field in the List_Flavors table (which is now only looking at the Flag connection).


              It is possible to do this without creating as many sub-tables like I did, but I found this easiest to keep track (and I did not have many records to deal with in each sub-table (and they were relatively static, with little change over time).  The key is just to keep track of what fields you are using for your relationships and then creating the value lists based on the correct fields and relationships.  You would need, however, to create separate Table Occurrences (TOs) for your table, though, to make the separate relationships to your primary table.


              If you want to send me what you did, I'm happy to take a look and see more related to your file what you are doing.


              Hope this helps.  Took me a long time to figure this out myself since I'm a pretty new to this myself.   So that is my caveat and invitation to the experts here to point out how to improve upon my post. 

              • 4. Re: Cascading / Conditional / Tiered Drop Down  Dropdown (8-levels)

                You may also want to look at this post.  There is a sample file posted that can be helpful for you to look at.



                • 5. Re: Cascading / Conditional / Tiered Drop Down  Dropdown (8-levels)

                  How do I send you my example?