Expand all | Collapse all

Cascading Dropdowns

  • 1.  Cascading Dropdowns

    Posted 10-22-2019 00:57
    Greetings from the beautiful Sierra Nevadas and High Desert of northwest Nevada.

    I'm getting stuck creating two cascading dropdown menus for classifying new inventory items.
    There are separate tables for Inventory, Category 1, and Category 2.
    The Category 1 Table is simple, and contains a dozen options.
    The Category 2 Table creates a relationship between the choice of Category 1 and the options available for Category 2. (This is like having Category 1 be US State Names, and Category 2 being specific cities in each state.)

    The Inventory Table includes fields for Category 1, Category 2, and additional fields whose values are 1:1 dependent on Category 2. There may be from one to dozens of Category 2 values dependent on Category 1.

    Category 1 has a many-to-one relationship with Inventory (each Inventory item can only have one Category 1 value).

    Category 2
     also has a many-to-one relationship with Inventory (each Inventory item can only have one Category 2 value).

    Category 2 has a many-to-one relationship with Category 1 (each Category 1 has a unique subset of Category 2 values associated with it).

    Also, when a Category 2 value is selected it also specifies the value that will be assigned to two (possibly more) other fields associated with that item in the Inventory Table. This could be like the names of the mayor, police chief, and fire chief for that city.

    When a new Inventory item is entered,
              the user must first choose a Category 1 value from a dropdown,
              then select a Category 2 value from a dropdown,
              and the Inventory record is saved with the Category 1 value, Category 2 value, and the other values 1:1 dependent on Category 2.

    Okay, I can create the relationships between the tables, and I get the right value for Category 1, but I'm not getting the data structure for Category 2 set up right. I've read the online documentation for cascading dropdowns, but I'm missing something in setting up the fields. I have an Excel file with the relationships defined, but it's not working when I import it.

    Can anyone help me with this?

    Rob Vugteveen
    Northwest Nevada

  • 2.  RE: Cascading Dropdowns

    Posted 10-22-2019 03:50
    Hi Rob, 

    You need to setup Parent-child-child tables and relationship. Category-Category-1 and Category-1 to Category-2 (child).  Create the category-2 table with record ID as primary category-1 as reference field for setting up relationship.  Then setup a proxy field on your relationship field to show category-2 instead of record id and use form rule to update the category. This way it preserves the values instead of only keeping child table record id(s).

    Hope this helps.

    Babi Panjikar

  • 3.  RE: Cascading Dropdowns

    Posted 10-22-2019 08:37

    To get this to work there are three relationships and then a conditional in the Inventory table

    Category 1 << Category 2
    This will allow you to build all the descriptions needed to categorize the inventory.

    Category 1 << Inventory
    The highest level of categorization of Inventory

    Category 2 << Inventory
    The second level of categorization if Inventory

    If you were the only user you would be done.  However we now need to prevent people from selecting a Category 2 that is not a child of the Category 1.  

    There is two steps to this 

    Go back to the relationship for Category 2 << Inventory.
    You need to add Related Category 1 as a Look Up field in Inventory from the Category 2 relationship
    It will rename that field to something like   Related Category 2- Related Category 1

    Last step is to Properties for the field Related Category 2 in the Inventory table
    In the section Reference field options enable the Conditional Values check box
    There are two drop downs in there that have to be set
    The first one you need to set to Related Category 1
    The second on you set to the look up field Related Category 2- Related Category 1

    That last step prevents the user from selecting any Category 2 values that do not have have the same Category 1 parent as Inventory.  In fact it will not let you pick a Category 2 until a Category 1 has been selected.

    Send me a note if you have any problems and we can get this fixed quickly for you.


    Don Larson

  • 4.  RE: Cascading Dropdowns

    Posted 10-23-2019 02:51
    Edited by Rob Vugteveen 10-23-2019 02:55
    Thank you, Don. Your "Last Steps" were the pieces I was missing, and I have made it work just as I had hoped...

    ...with one exception. The dropdown menus contain just what I specified EXCEPT for the item at the bottom which allows the user to add a category. I don't want the general user to be able to add a Category 1 or Category 2. We had users being lazy and adding a new category when they had something unfamiliar to them, rather than think through how it could fit into an existing category.

    I have hidden the Category 1 and Category 2 tables from the Table Bar so users can't see them, but those dropdown menus are setting me up for trouble with that "add" option. Can I turn that option off?

    And, thank you again for getting me over the obstacle.

    Rob Vugteveen

  • 5.  RE: Cascading Dropdowns

    Posted 10-23-2019 08:06

    Users creating bad data and being lazy....   So many stories.

    To prevent this, change the permissions on those two tables to prevent certain User Roles from being able to Add Records.   

    Go to settings for the table in question
    The right hand column is Workflow & Permissions
    The second one down is Access
    This will show you all the roles and the permissions the have in that table
    Uncheck the 4th column from the right, Add

    Do this for both tables Category 1 and Category 2

    Now they can use the data in those tables but not add to it.  I suggest you also take away their ability to Modify.   
    Don't take away View as suddenly they will be unable categorize the Inventory items.

    Don Larson
    Westlake OH

  • 6.  RE: Cascading Dropdowns

    Posted 10-24-2019 23:11
    Many thanks to Don and Babi for guiding me to success with the cascading dropdowns. My boss is ecstatic to finally have this capability in our inventory system.

    I now face the consequences of that success with another problem, but I'll put that in a separate thread. 
    (one-time internal conversion from the old category method to the new one)

    Rob Vugteveen

  • 7.  RE: Cascading Dropdowns

    Posted 10-24-2019 23:34
    Happy to hear that, Rob.

    Feel free to connect on your further queries.

    Babi Panjikar