Expand all | Collapse all

Lookup fields won't update

  • 1.  Lookup fields won't update

    Posted 10-28-2019 01:30
    I 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

  • 2.  RE: Lookup fields won't update

    Posted 10-28-2019 07:49
    i'm not sure that I totally understand your relationships, but let me guess at an answer.
    When you select a main category to then drive the filters for the next level of selection of Sub category, you should rename that like [Main category filter], as opposed to [Main Category].    Then use a lookup to get the [Main Category] as a lookup from the Inventory record.  Same with the Sub Category.

    The point is that if you Change the Main category from the Master Inventory record, you want tho flow down though the lookups.  So differentiate between the data entry filter you are using and the live lookup values which may change if the master changes.

    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach