Cascading Dropdowns

Question

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
------------------------------

babipanjikar · Answer

Hi Rob,&nbsp;You need to setup Parent-child-child tables and relationship. Category-Category-1 and Category-1 to Category-2 (child).&nbsp; Create the category-2 table with record ID as primary category-1 as reference field for setting up relationship.&nbsp; 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------------------------------

donlarson · Answer

Rob,To get this to work there are three relationships and then a conditional in the Inventory tableCategory 1 &lt;&lt; Category 2This will allow you to build all the descriptions needed to categorize the inventory.Category 1 &lt;&lt; InventoryThe highest level of categorization of InventoryCategory 2 &lt;&lt; InventoryThe second level of categorization if InventoryIf you were the only user you would be done.&nbsp; However we now need to prevent people from selecting a Category 2 that is not a child of the Category 1.&nbsp;&nbsp;There is two steps to this&nbsp;Go back to the relationship for&nbsp;Category 2 &lt;&lt; Inventory.You need to add Related Category 1 as a Look Up field in Inventory from the Category 2 relationshipIt will rename that field to something like&nbsp; &nbsp;Related Category 2- Related Category 1Last step is to Properties for the field Related Category 2 in the Inventory tableIn the section Reference field options enable the Conditional Values check boxThere are two drop downs in there that have to be setThe first one you need to set to Related Category 1The second on you set to the look up field Related Category 2- Related Category 1That last step prevents the user from selecting any Category 2 values that do not have have the same Category 1 parent as Inventory.&nbsp; 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.DonDon@paasporter.com------------------------------Don Larson------------------------------

robvugteveen · Answer

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
------------------------------

donlarson · Answer

Rob,Users creating bad data and being lazy....&nbsp; &nbsp;So many stories.To prevent this, change the permissions on those two tables to prevent certain User Roles from being able to Add Records.&nbsp; &nbsp;Go to settings for the table in questionThe right hand column is Workflow &amp; PermissionsThe second one down is AccessThis will show you all the roles and the permissions the have in that tableUncheck the 4th column from the right, AddDo this for both tables Category 1 and Category 2Now they can use the data in those tables but not add to it.&nbsp; I suggest you also take away their ability to Modify.&nbsp; &nbsp;Don't take away View as suddenly they will be unable categorize the Inventory items.------------------------------Don LarsonPaasporterWestlake OH------------------------------

robvugteveen · Answer

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.&nbsp;(one-time internal conversion from the old category method to the new one)------------------------------Rob Vugteveen------------------------------

Forum Discussion

Cascading Dropdowns

Related Content

Embedded Grid Edit Report Pre-filtered Conditional Dropdown

Conditional Dropdowns, Cascading Dropdowns and Cascading Permissions

Cascading Dropdown

Cascading Dropdowns Not Working

Filter dropdown selection