Link/Lookup States/Provinces and Countries

[adanks]

I’m on a free account on SeaTable Cloud.

I have 4 tables.

Countries with Country Code and Country Name

States-Provinces with State/Province Code and State/Province Name

Locations with a Country_Link field linked to the Countries table and States-Provinces with a State-Province_Link field linked to the Countries table.

A Battery Capacity Data table with a Country_Link to the Countries table and a State-Province_Link to the States-Provinces Table.

Currently Battery Capacity data entry requires selection of both the Country and the State-Province, which would be OK if the State-Province choices were limited to the Country entered. A potential solution is the just select the State-Province and have a column to link/lookup the Country from the Locations table. I’ve poked and prodded. I see some posts here that are similar, but not close enough to find a solution.

[bennhatton]

Hi @adanks, and welcome to the SeaTable forum!
I’m sorry, I’m a bit confused with your database structure , here are few thoughts/questions:

• I don’t see how you’ll use your locations table: don’t you want the battery capacity data table to be linked directly to this locations table ?
• I imagine that a state-province is necessarily linked to one single country… Can’t you add a country column in your states-provinces table linking to your countries table ? Doing so, you’ll be able to access directly the linked country once you chose the state-province

Your potential solutions sound good, but as presented, i’m affraid you won’t manage to achieve what you need as it requires either a dynamic setting

@adanks

if the State-Province choices were limited to the Country entered

which is currently not supported, or an automation rule for your second idea

@adanks

A potential solution is the just select the State-Province and have a column to link/lookup the Country from the Locations table.

as there is currently no link between the fields you choose in the battery capacity data table (country and state-province) and the location table you want to use for the lookup.

Bests,
Benjamin

[adanks]

@bennhatton

• I don’t see how you’ll use your locations table: don’t you want the battery capacity data table to be linked directly to this locations table ?
• I imagine that a state-province is necessarily linked to one single country… Can’t you add a country column in your states-provinces table linking to your countries table ? Doing so, you’ll be able to access directly the linked country once you chose the state-province

Thanks for your help. I have it working now.

My configuration was not as I described. I intended to Battery Capacity State to Location State, but I couldn’t because apparently SeaTable doesn’t allow linked records from one table (Battery Capacity) to linked records in another table (States in the Location table were linked to the State-Provinces table.)

I ended up abandoning the Countries and State-Provinces tables and changed the Locations table to include Country, State-Province, Country Code, and State-Province Code.

From there I was able to link the State-Province in the Battery Capacity table to the State-Province in the Locations table as State-Province_Link. Then I used a link formula in the Battery Capacity table to link the State-Province_Link and look up the Country from the Locations table.

This works great. I select the State-Province on the form and the Country automatically fills from the link formula.