ContributionsMost RecentMost LikesSolutionsLookup fields won't updateI have two cascading dropdowns that select Main Category (13 possibilities) and Sub Category (one to a dozen possibilities depending on the Main Category Selection). There are two (actually four, but let's stick with two for this discussion) other fields that are dependent on the Sub Category choice that go along for the ride, call them SubCat3 and SubCat4. The Inventory Table has separate fields for Main Category, Sub Category, SubCat3, and SubCat4 (as well as fields for Related Main Category and Related Sub Category). The Main Category Table contains only the names of the 13 Main Categories, one name per record. The Sub Category Table contains the "children" of the Main Category table, and each "child" has three values: Sub Category, SubCat3, and SubCat4. These are linked in the relationship between the Tables. (This table also includes a numeric Related Main Category field which points to the appropriate record in the Main Category Table for each Sub Category). SubCat3 and SubCat4 are defined as Lookup fields in the Inventory Table and they are supposed to point to the corresponding fields in the Sub Category Table. However, I'm discovering that if I edit an Inventory Item and change the Main and Sub Categories, I can see the displayed values of SubCat3 and SubCat4 appear to update on the entry form, but SubCat3 and SubCat4 don't actually show that change when I call up an Inventory report after saving that record. I'm vaguely aware of the idea of a "snapshot," but locking a snapshot is not the kind of behavior that I desire for any field. How do I get the lookup fields SubCat3 and SubCat4 to update when the Main and Sub Category values are changed whenever an Inventory record is edited? ------------------------------ Rob Vugteveen ------------------------------ One-Time Internal Data Conversion in a Huge TableI'm pleased to have succeeded with implementing cascading dropdown menus (Thanks to Don and Babi for their input!), and now I have to convert the legacy categories (22 main categories and 463 subcategories - yikes!) to the new category system (14 main and 163 subcategories). If I was in Excel, I would do this with a lookup table to fill in blank fields in the newly created fields of New-Main and New-Sub based on values from the Old-Main and Old-Sub of the legacy data. But I have tens of thousands of records to convert, and this is too large for QB to export as one file. I suppose I can filter and export data for each Old-Main category, do the lookup, and import the updated datafile back into QB. But that seems inelegant. I have to think that QB can do this. I need to identify blank records in the New-Main and New-Sub Categories, read the Old-Main and Old-Sub Categories, lookup the correct combination of new Main and Sub Categories (in a new temporary table perhaps?) and copy those into the blank fields. This process would only be done ONCE, and at a time when no one else was using the App. Once complete, entering data into the old category system will be taken off the entry form, and the new method will take over. For Example, Old-Main + Old-Sub --> New-Main + New-Sub Bottles + Soda --> General_Americana + Bottles (a reduction in overall detail that we don't need) Mining + Stocks --> Stocks & Bonds + Mining (Main Category rename and a reversal of category priority) Aviation + [no Old-Sub] --> Transportation + Aviation (reverse priority and fulfill new requirement for both non-blank values for New-Main and New-Sub) Those are a few possible combinations that will occur interpreting New from Old. I don't even know where to begin in QuickBase to do something like this. Is QB even the right tool? ------------------------------ Rob Vugteveen ------------------------------ Re: Cascading DropdownsMany 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 ------------------------------ Re: Cascading DropdownsThank 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 ------------------------------ Cascading DropdownsGreetings 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 2also 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 aCategory 2value 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 1value 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 2set 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 ------------------------------