Need Help: Dropdown condition constraints with cascading Reference columns

Ask for Help
1

Hi everyone,

I’m building out an inventory and pricing dashboard in Grist and have run into a major roadblock with cascading dropdown filters when dealing with Reference columns. I am trying to keep my database structure lean without creating single-column helper tables or redundant formula columns just to manage interface dropdowns, but the restricted sandbox of the Set Dropdown Condition box is giving me some severe limitations.

Here is my exact structure and the methods I’ve already tried:

My Setup

  • Adhesives Table (Master): Contains columns for Brand (Text) and Product (Text). Because there are multiple products per brand, the brand names are naturally repeated across multiple rows.

  • Prices Table: Contains two Reference columns pointing to that master table: ADH_Brand (pointing to Adhesives) and ADH_Product (pointing to Adhesives).

The Goal

  1. In my Prices table, when I click the ADH_Brand dropdown, I want to see a clean list of unique brands (no duplicates), while keeping the column as a true data-entry Reference type.

  2. When I select a brand, the ADH_Product dropdown should filter to show only the products belonging to that specific selected brand.

What I’ve Tried (and Why They Failed)

1. Direct Lookup inside the Dropdown Condition

I attempted to de-duplicate the brand dropdown directly using table lookups to only show the first instance of a brand row:

Python

choice.id == Adhesives.lookupOne(Brand=choice.Brand).id

  • Result: Errored with unknown variable 'Adhesives'.

  • I then tried to bypass this using the universal table keyword (choice.id == table.lookupOne(...)), which resulted in unknown variable 'table'. It seems the dropdown condition box restricts table-level lookups entirely.

2. Dot-Walking Reference Objects

To get the cascading product filter working once ADH_Brand was set back to a Reference column, I tried to pull the text string attribute of the record inside the product’s condition box:

Python

choice.Brand == $ADH_Brand.Brand
  • Result: The dropdown failed to filter at all. The condition box seems unable to evaluate dot-walked attributes on a referenced record object.

3. Record ID Comparisons

I tried calculating the parent brand row ID over on the master table via a formula column (Brand_Match_ID), and then matching them strictly by IDs in the product dropdown condition:

Python

choice.Brand_Match_ID == $ADH_Brand.id
  • Result: This also failed to filter, meaning ID-to-record matching across cascading reference dropdowns is failing to resolve in the sandbox.

4. Changing Type to “Choice” (Partial Fix with a Catch)

Changing ADH_Brand from a Reference type to a Choice type completely fixed the duplicate issue and allowed the simple product filter choice.Brand == $ADH_Brand to work perfectly.

  • The Problem: This completely breaks my relational data integrity. It splits the data into two independent lists. New brands added to the master table do not appear in the prices dropdown, creating data entry silos and bypassing validation. I have a lot of other material tables to build out next, so duplicating lists like this isn’t sustainable for my workflow.

My Question

Is there a supported, native syntax within the Set Dropdown Condition box that allows a Reference column to filter out its own duplicate display values, and subsequently allows a second Reference column to look at that selection and filter its own choices accordingly—without having to create auxiliary summary tables or helper columns on the master table for every single layout field?

Any guidance or workarounds on how to achieve this cleanly within the formula sandbox would be greatly appreciated!

Thanks