Choices depending on another choice

Hi,

Let’s say I have a column named “Country”. Column type is “Choice” and possible values are France, USA and UK. I have another column named “City” whose type is also “Choice” ; possible values are New York City, Washington City, Paris, Lyon, London and Southampton.

I would like to condition the choices of column City according to the value of column Country: by selecting France, I can only choose between Paris and Lyon.

(In real life, I’ve three possible values in my fist column (Country in my example), and more than 40 in the second one)

What’s the best way to do this?

Thanks a lot :slight_smile:

1 Like

Hi there!

Currently there is not a way to filter values in dropdowns this way but this would be great for a future improvement!

You can use references to do something similar. I have created an example for you at the link below:

https://public.getgrist.com/gPk6tQAdn29h/Community-1229/m/fork

First, you select a Country in the Countries table. The Cities table will populate with cities linked to that Country. Then, you can select a city. You’ll notice that the Workshops table updates as you make selections. After selecting your Country and City, enter a value in the Topic column of the Workshops table.

After adding a value, hit enter to create the record. The Country and City columns will auto populate based on the country and city selected in the top two tables.

To set this up, first create a table titled ‘Countries’. This table will only have a single column and the Countries will be listed.

image

Next, create a table titled ‘Cities’. This table will have two columns. The first column will be ‘Country’. This is a reference column that pulls data from the Countries table. Our second column is ‘City’. Here, we link each city the Country it is in.

Next, open the table configuration panel on the right hand side of the page and under the Widget menu, select ‘Change Widget’. We can now choose to Select By Countries. This means that when we select a country in the Countries table, our Cities table will update to only show cities in that country.

You can add cities to the Cities table now and it will auto-assign the country based on what country is selected in the Countries table.

We can hide the Country column from our Cities table since we can see which country is selected in the Countries table. To hide, hover your mouse over the column header then click the arrow icon that appears.

image

Then select ‘Hide Column’ from the dropdown.

Last, we have our Workshops table. This table contains a Country column and City column - both are reference columns. The Country column points to the Countries table and the City column points to the Cities table. We are going to link this table via the City column so under ‘Select By’, I selected Cities.

Now, this table will update based on what city is selected in the Cities table. The City column in the Workshops table will also auto populate based on the city that is selected in the Cities table.

As you see in the screenshot above, the Country column does not populate. We can do this with a formula.

$City.Country

Here, we are using dot notation and using the reference column City to pull information from the Cities table. Specifically, we want to pull the value from the Country column of the Cities table.

Now, both City and Country auto populate.

Please let me know if you have any other questions.

Natalie

2 Likes

Thank you very much for this very complete and very clear tuto! It works as expected :slight_smile:

1 Like

This doesn’t work for me.

Instead of the formula returning the country from the Cities table, it is return Cities[2] (assuming Paris is city 2–I am not sure).

How do I get the text value?

The example is very confusing because of similar names: City actually means the City column of the workshops table, which resolves to a reference to the Cities table.

The formula column should also be a reference column. In this example, the Country column of the Workshops table is a reference column that points to the Country column of the Countries table.

Cities[2] is the record ID, meaning the record’s ID in the Cities table is 2. When using reference columns, it’s actually pointing to an entire record in the referenced table. Under ‘Show Column’, we choose the label we want to see to represent the record.

I know it’s the reference ID.

The trick was to make the reference column type AND supply a formula. And it’s more tricky than that:
the reference is to countries, but the value for the formula refers to $City to get the Country Column.

So, I did that but it still doesn’t work. Entries to the workshop table jump around. I do one row but the second row changes the first row.

The problem appears to be that a selection in the countries table flows through BOTH the cities table and the workshops table. So, creating a new row with a different country->city selection filters out the previous rows.

So there is another step you seem to have left out. The workshops table needs to have a selector, which is the Cities table. This doesn’t work for me–that is what causes Cities.City to filter the entire page.

Is there a way to see ALL of the workshop rows after several have been entered?

It looks like what I am saying is the expected behavior. It’s really an unsatisfying hack…

The solution is to change the selector for the Workshops table to “select widget” (e.g., the dropdown itself without any reference to any table as a selector).

But, now the workshops table ignores the choices of city and country from the other tables.

Is there anyway to get both behaviors?

Kind of… …add another widget (possibly on a separate page but can be on the current page) that is simply a view on the workshops table with no links anywhere–it is simply a view of the table as it currently exists.

So, one widget is for creating new workshops. The other widget shows all workshops.

I guess that has to be the way to do it.

This suggests that always relying on relational algebra is really flexible and fast running, but creates a visually awkward result. And, its a bit difficult to setup, maintain, replicate. Work for the future (in software thats never ending).

I think it finally works. It’s just not very pleasing. The workshops table only shows the current matches between country and city.

Note in my example: Countries = Boats (and country = boat), Cities = Routine Maintenance Schedule (and city = item), and Workshops = (dosched) aliased to “Schedule new maintenance items here.”

Can you look at: https://docs.getgrist.com/xeFh7PN2tQ2G/experiement/p/5