Creating Dynamic Reference Drop-Downs in Grist

[Rogerio_Penna]

Filtered reference dropdowns are an advanced feature in database and spreadsheet applications that enhance user experience by dynamically refining the options presented in a dropdown menu based on another field’s selection or criteria. This functionality is especially useful in forms or data entry interfaces where the relevance and accuracy of the data input are crucial.

The concept revolves around the idea of “context sensitivity” – meaning, the options available in one dropdown menu depend on the selections made elsewhere in the form. For instance, if you have a form that requires users to select a company and then an employee from that company, a filtered reference dropdown would only display employees who are part of the selected company, rather than all employees in the database.

The example I will provide here will be based on selecting a country in a drop-down and then a second reference dropdown displaying only cities within the previously selected country

Without this, you would have two independent dropdowns and you might have a list of hundreds of cities in the second dropdown, increasing the chance of selecting a city that doesn´t belong to the country selected in the first. Obviously, the error probability increases even more if you have for example a first drop down with companies and a second drop-down with employees. You would need a widget showing companies and employees, filter it by the company you selected on the first form, then in the first form, employee dropdown, select one of those employees.

Possible, but less elegant than having a single form already doing the filtering.

other possible user cases:
-First Reference DropDown is for classes, second for Students
-First Reference DropDown for Type of Product, second for product
-First Reference DropDown for companies, second for employees

• etc

So, for this example I have 3 main tables, one for countries, other for cities (each city referencing a country) and another for the form

Notice that there is an EMPTY column (it will be used by the Action Button

And a formula column

if $Company.Name == $CountryAction:
    return $Employee
else:
    return " "

What the formula does is simply checking if Country column is equal to CountryAction, if it is, it returns the value at the column.

And finally we have the Form Page.

In the country reference, we select a country

As soon as a country is selected, the city dropdown is red and the reference is actually empty. I also created an error message, telling the user to click to update the button

So do as the messages tell you, click the button!

And voilá

What if the user changes the country after selecting a city?

It shows in red the city, because it doesn´t match the country, and tells the user to click the button to update the city again

After clicking the button to update the city…

The card list on the right updates accordingly.

[Rogerio_Penna]

So, what exactly is happening

The “Action Button” copies the selected country in the forms table to EVERY ROW of the Cities Table, in that “CountryAction” column I mentioned before.

Remember I told the “City if Country” column was a simple formula that checked if column country was EQUAL to column CountryAction? And it it was, it repeated the city name?

So… this is what happens to that table when I selected USA in the Forms table and clicked the button

In the City if Country column, ONLY US cities are being shown in the last column

and when I select Argentina and click the button

It takes advantage of a Grist feature that doesn´t work in Excel: reference dropdown lists DO NOT SHOW empty rows.

So you only get the rows where the city name is repeating, because it’s country is the same as the country selected and copie to all rows after the Action Button was clicked.

Speaking of Action Button, he is what it does

{
  "description": "Assign selected Country to all Cities",
  "button": "Update Cities",
  "actions": [
    ["UpdateRecord", "Cities", e.id, {"CountryAction": $Country.Name}]
    for e in Cities.all
  ]
}

I asked Chat GPT to explain the code

actions: This array defines the specific operations to be carried out when the button is clicked. Each element within the array is itself an array detailing a single action.

[“UpdateRecord”, “Cities”, e.id, {“CountryAction”: $Country.Name}]: This is an instruction to update records in the “Cities” table.
“UpdateRecord”: Specifies the type of action, which in this case is to update existing records.
“Cities”: Indicates the table where the action will be applied.
e.id: Represents the unique identifier (ID) of each city record that will be updated. The e variable seems to iterate over all entries in the “Cities” table (for e in Cities.all), applying the action to each.
{“CountryAction”: $Country.Name}: Defines the specific update to be made. It sets the value of the “CountryAction” field for each city record to the name of the selected country ($Country.Name). The $Country.Name placeholder suggests that there is a way for the user to select a country, and its name is used here.
The loop for e in Cities.all iterates over all records in the “Cities” table, applying the update action to each. This loop structure is not standard JSON syntax and appears to be a conceptual representation of how the actions are dynamically generated for each city record.

This code effectively means that upon clicking the “Update Cities” button, every city record in the “Cities” table will have its “CountryAction” field updated to match the name of the country selected by the user at the time of the button click.

What else?

Well, the Forms.City field has a conditional formatting where it gets red background with white font IF
"$Check_Country_vs_City_Comp!=“OK”

It’s just checking the other field called Check Country vs City.
That field has the formula

“IF($Country.Name==$CityxCountry_Check,“OK”,“ERROR! Click Update Button and Reselect Employee!”)”

This button checks if the selected country is the same one from that city. (it actually references yet another field, CityxCountry Check, which is just “$City.Country.Name”. Obviously that’s a redundant field that is only that so I could visually see if it was getting the correct country.

In reality, the formula
“IF($Country.Name==$CityxCountry_Check,“OK”,“ERROR! Click Update Button and Reselect Employee!”)”

could be replaced by
“IF($Country.Name==$City.Country.Name"OK”,“ERROR! Click Update Button and Reselect Employee!”)"

[Rogerio_Penna]

One last important thing.

The CITY field that is a reference, shows the City If Country column (that one that only shows city names if the City.country is the same as the CountryAction column.

BUT, that column is DYNAMIC. It changes all the time.

That means that if I created 4 records with Brazilian Cities, and then I change the country and click the UPDATE CITIES button for Germany cities, the brazilian cities will be empty in that column.

Look at the form table below, the City Column. The currently country used is Brazil.
Therefore the records with Italy and Argentina are showing BLANK for the city

How to solve that?

Simple… whenever you reference a record in Grist, you are actually referencing that record ID.

So let’s say you selected Milano for Italy. And it’s ID is #20

You updated the cities list and the formula column now is empty for Milano, showing Blank, because Milano’s country (Italy) is no longer the selected country.

BUT the reference is STILL towards #20.
It’s just the dynamic column that became blank.

See? the FORM widget with a SINGLE CARD is showing the “city if country” column, that right now is empty for Milano. But the city column never disappears. And the ID #20 is still Milano.

So in the SINGLE CARD widget, I show the cities I can select (reference to cities.cityifcountry)

but in the Form Card List, which shows all records created, SELECTED CITY column is showing $city.city

[nick]

finally got a chance to check this out! this can definitely have a more elegant solution within Grist, but hats off to you @Rogerio_Penna for this clever workaround (with extensive documentation, no less).

reading your process really highlights Grist’s flexibility, as well as some of its shortcomings . thanks again!

[Rogerio_Penna]

Before Grist really implements it in the engine, someone still might find a solution for all the update button clicking.

When showing countries and cities this solution might seem stupid.

Gee, too much work to select cities everyone knows the country.

But even if it’s some work, it starts getting more important when you have lists of products or codes, whatever, that are not so “intuitive”.

List of Employees by Store.

List of clients by state

List of components by product.

Anyway… someone might come with a better solution using the same logic and improving it. In fact I am thinking of a better solution right now.

[Rogerio_Penna]

what if the CountryAction column, instead of being populated with the currenctly selected country BY the action button, simply was equal to the current (or last) record of the Form table Country selection field?

It would automatically update, right?

Of course, there is a problem, the other table doesn´know WHAT record of the Form table you are working at any given moment.

It might get the last, but what if I decide to update a previous record? Thus, I guess we would need to have a data of update of the Form table, so any record you are editing becomes the last and populates the CountryAction column

---

Another possible solution still better than the UPDATE button I used, would be a SUBMIT button.
The submit button would not be located in the FORM table.

But in the FORMEDIT table, which is a duplicate of the Form table. The FormEdit would have a single record always And the ActionButton column would equal that specific record of the FormEdit table, while the FormEdit table would then show the city dropdown.

What the ActionButton does? It’s a submit button. It COPIES the content from FORMEDIT to FORM table.

So… you wouldn´need to click it everytime you changed country. You change country, list of selected cities update automatically and then you use Submit button to copy contents to the real table with many records.

I suppose the table with all records shouldn´t be edited (the filtered references wouldn´work). You would have an EDIT button on it, where you select a record, click edit and it sends that record to the FORMEDIT table.

[jperon]

@Rogerio_Penna Would you mind sharing a template with this, please? I’d have an idea to avoid clicking the button.

[Rogerio_Penna]

like the above I posted? I think one of them might work, but I haven´t tested yet.

Can you remind me how to share it? This test is on the pavicon.grist.com host, so I guess I can share it if I remember how.

[Rogerio_Penna]

@jperon
@nick

OH MY GOD, IT WORKS!!!

EUREKA

No need for action button, SO SIMPLE!

In the FORMS table, I created a new column with an DateTime Update Stamp.

In the Cities table, the Country Action column was replaced by this formula

latest_country = list(Form.lookupRecords(sort_by="dataatualiz").Country.Name)
return latest_country[-1] if latest_country else $DefaultCountry

• 1: Fetch records from the Form table, sort by the ‘dataatualiz’ column to get them by update date
• 2: Return the most recent ‘Country’ value if available, or a default value if not

[Rogerio_Penna]

Grist Automatic Reference Dropdown Filter

[nick]

That does look much simpler, congrats! Would love to take a look at it if you’re able to share the document (or a copy without any sensitive data).

[Rogerio_Penna]

there is no sensitive data in this document. Only country and city names.

the only question is what constitutes a document at Grist

can I share ONLY this?

[nick]

Yes exactly – you can see here how to share a public link.

[Rogerio_Penna]

oops, I guess I had not saved after changing to public
https://pavicon.getgrist.com/nuUzaZgPVeFB/Filtered-Reference-DropDown?utm_id=share-doc

[Rogerio_Penna]

this solution is so simple that in my ignorance, it seems it would be entirely possible for the Grist team to adopt this exact same strategy to create filtered dependend dropdowns natively at Grist… just create virtual columns working like what I did, and columns with the formulas ready, already referencing the correct fields.

[Rogerio_Penna]

Nick made a very pertinent question at the Discord post

So it is filtering based on the latest country entered, i.e. only really helpful for new records?

I answered

If you created a record, created another record, then go back to the previous record, the country being used is from the last created record, so the city field will appear as blank, until you do a modification in this record (no need to be at country field), thus, it being the latest record again and updating all the country fields of the Cities table.

A simple boolean field can solve that. Click on it the current one becomes the last updated field, with it’s country thrown in the cities table.

I exemplified it below. The ideal solution here would be for Grist to know WHICH record I am seeing in the Forms table, and the formula at Cities table to check record I am seeing instead of last updated record. I don´t think that’s possible, so there is this caveat.

Grist Filtered Dropdown 2

[Rogerio_Penna]

STEP BY STEP TUTORIAL

EDIT: I have edited this post so as to have extra information, tips etc, below the step by step tutorial

1 - Create the two tables with the Main and Secondary categories (1). Let’s consider for this example the generic names MAINLIST and CHILDLIST. They should have at least one column… let’s call the first column in MAINLIST as Main_A and the first column in CHILDLIST as Child_A

2 - In the CHILDLIST, create a reference column to MAINLIST. (2)

3 - Create the table for the place where you need the two dropdown lists, the dropdown for CHILDLIST being filtered by the dropdown to MAINLIST.
This table obviously needs at least two referenced columns, one for MAINLIST and one for CHILDLIST. Right now we will only create the reference to MAINLIST
Let’s call it by the generic names mainlistref
Let’s call this table FORMTABLE, for the purposes of the tutorial

4 - at FORMTABLE, create a new column for TimeData stamp.(3) Name it formtableupdt (4)

5 - back at CHILDLIST table, create a new formula column. Let’s name it MAINLISTSELECT
the formula should be
FORMTABLE.lookupOne(sort_by=“-formtableupdt”).mainlistref.Main_A

=FORMTABLE.lookupOne(sort_by="-formtableupdt").mainlistref.Main_A

*Basically, it is is looking for a single record at formtable. It will look for that record based on the newest formtableupdt (date time of update) and return the reference from mainlistref (MAINTABLE) and column A of that reference. *
(replace column A by whatever column you created for MAINTABLE that gives you an idea of what that record is)

6 - at CHILDLIST, create a new formula column. Let’s call it CHILD_IF_MAIN.

if $MAINLISTREF.Main_A == $MainListSelect:
    return $Child_A
else:
    return " "

what this column does is checking each record of CHILDTABLE for it’s reference to MAINTABLE
to see if it’s equal to the selected MAINTABLE reference on FORMTABLE in the last updated record of FORMTABLE.

If it is, it returns the Child_A value.

This video shows what happens with CHILDTABLE (in the video, it is the CITIES table on the very right)
Notice that every time I create or update a record at FORMS table, the whole country action column of CITIES change to the same country at FORM. And on the City if Country column of CITIES, only the ones where COUNTRY and COUNTRYACTION match, return the name of the city

Gravar #3

7 - back at FORMTABLE, now let’s create the reference to CHILDLIST. It will point to column Child_if_Main. (5)

8 - I strongly suggest creating a new column at FORMTABLE based on the ChildListRef but pointing to another column of CHILDTABLE.

Examples, tips and commentaries from the tutorial

1. Example: countries x cities by countries, companies x employees by company, etc.

2. How you populate both lists is up to you. (you can have a page with MAINLIST and a widget for CHILDLIST selected by MAINLIST, so you can create MAINLIST items and CHILDLIST items already referenced by MAINLIST items

3. If using the newer versions of GRIST, after clicking the + sign and the new column menu appearing, go down to Shortcuts section and DateTime Stamp, and select to apply to RECORD UPDATES.

4. Thus, every time a new record is created or you alter ANYTHING in a record, you get the date and time of the change.

5. That’s the secret sauce. Child_if_Main column only gets populated on the records where the record reference to MAINLIST matches the currently selected MAINLIST reference of FORMTABLE
   And Grist reference dropdowns ONLY show non-blank records.