How to select a two-level list

[MartySirkin]

Hello. I have just started playing with Airtable, and I have run into an issue. In SQL this would take but a second, but I cannot seem to figure out how to add this filtering. If you can help, I would be quite appreciative... TIA.

The situation:

1. I have a table called "Events". It records customer reviews of events that have happened.

2. I also have a table called "Continents" (with 7 rows) and one called "Countries" with 195 rows. 

What I am *trying* to do is to have essentially a two-level dropdown list. I have added a field to the Events table that has a lookup to continent. That works just fine.

The issue comes when I try to figure out what I'd *like* to do which is to set up a definition for the Countries table. I have added to the Countries object a field for continent which is a link to the continent for that country.

The real problem is that I'd love to add the relation such that when I choose the continent for that country that I can then show a dropdown list which is the list of countries for the selected continent. And I cannot seem to figure out how to do that.

Here, for example, is the record for Africa in the continents table:

1 Africa 3, 5, 20, 24, 28, 29, 31, 33, 34, 35, 39, 45, 47, 52, 54, 55, 57, 58, 66, 70, 71, 84, 96, 97, 98, 102, 103, 106, 109, 110, 117, 118, 120, 126, 127, 142, 145, 151, 153, 155, 156, 161, 162, 163, 166, 173, 175, 178, 182, 194, 195

(1 is the continent number, Africa is the continent name, and the numbers are the country numbers for that continent).

Hopefully this makes sense. If not, please ask questions and I will try to further explain. This *should* be easy to do as it is a pretty basic database construct.

[TheTimeSavingCo]

If you're on a paid plan, try creating a linked field and using the 'Filter record selection by condition' option.

Here's the how the "Continent" table is set up:

And in the "Event" table there's a "Country" field with a the "Filter record selection by condition" option toggled on and set up like so:


And so when linking a record, the available options are filtered:

Link to base

[MartySirkin]

Thank you SO much for writing back to me. I really appreciate it. And I do want to apologize for not getting back to you over the past couple of days - we had a small family issue that I had to take care of.

I wish that I could tell you that your answer solves/solved the problem that I have with Airtable. It does not seem to. And, with the very detailed explanation you gave, it essentially duplicated the problem that I have/had. I will try, in more detail to explain. Hopefully, it fully answers the questions.

I had previously looked into exactly what you put in the above answer. In the Events table I added a continent field that was a lookup to the continents table. That worked simply, without a hitch. Since I had already created a Country table, and added the continent for each country (please see the following small example): 

Next, I had added a country field to the Events table - it was the obvious thing to do. And I had created it exactly like what you showed. Here is a sample of that:

You can see that it is essentially the same thing. The *problem* that I was having (and still seem to have) is that, for a given row I can set the condition for the the Continent. If (in the example I am showing), I set the continent in the filter to "1" (which is Africa). It ABSOLUTELY WORKS well, and what I would expect. The dropdown for Countries shows the countries in Africa. That looks great!

BUT... the problem is that Airtable sets that condition for EVERY row in the Events table. So, if I then add a new row to the events table (see image below) for a different continent (Oceania in this case), when I go to the Country dropdown, it then only shows countries in Africa - which is not correct. It seems that the filter condition I put on the one row becomes global for that column (and every event added thereafter). Clearly, that is NOT CORRECT.

What I was hoping for was that Airtable had the capability to have the filter in that row be able to *dynamically* pick up the continent for that row and fill it in dynamically to the filter. In this case where it made the country column show countries in Oceania, since that's what I set in the Continent column.

That is what I could do trivially in some language such as C#, using a simple SQL statement. And I have now spent hours and hours, but I cannot see any way to do this simple substitution in Airtable. Can this be dome? Does this make sense to you?

I really appreciate the offer of help, and I am happy to speak with someone if this can help the product evolve to be able to help others (or if it can be done and I am just being a bit too dense)...

But again, thank you for the first pass on this!

Marty Sirkin

[TheTimeSavingCo]

Hmm, yeah, that's how it's set up in the base I linked, it filters dynamically based on what's selected in the "Continent" field?  Looking at your screenshots it seems like you set up the conditional filter statically instead of dynamically.  Could you try setting up your conditional filter to use a dynamic condition like the one in my screenshot?

This is yours:

This is what it it should look like:

Could you also try going into the base I linked to test the functionality to see if it's what you're looking for?

[MartySirkin]

Hello!

Again, I really do appreciate the help. I just wanted to make sure that you saw my response. I certainly understand that this is NOT time-critical. And if you're busy and have other people to help - I certainly do understand that. 

Again, thanks in advance for the help!