I have 3 tables: Mission (mission_name); City (city_name); district (district_name)
The relationship are:
Mission table is created first: this include the name of the mission. One mission will cover several City.
City table is created after mission table. It includes City name (primary key) and a 2nd column named Mission that is a “link to another record” field. In this Mission column I will manually add related record from mission table. In database term the mission column here is a foreign key that are linked to primary key of Mission table. One city belong to one mission.
District table is created last. It includes district name (primary key), city name (link record to City table). The city name will be entered manually by selecting it from the linked record. One district belong to one city.
Now it is the tricky part, in the District table I also want to have Mission column so I could make some filter or aggregation. In the traditional database environment, I could write an SQL to populate it based on the relationship between City and Mission.
However in AirTable I did not find a method to implement this concept. In the district table If I add Mission column as a linked record to Mission table, I still have to manually add the value for all the record, even though the district vs mission relationship is defined in the city table already.
Is this just a limitation or is there a way I can work around this?