Help with Linking Countries/States/Cities to records

Hello Airtablers,

In my base I have a growing list of contacts with links to other tables such as their company/organization and the state the contact is in. I’m now starting to include the cities and states linked to the company/organizations’ headquarters as well.

The issue is that so far I’ve just had a table with 50 states plus territories, but now I have companies that are headquartered in other countries

I’m thinking i’ll have to create another table of countries? Or just create a new field for countries in the current organizations table?
Also worth noting that I just want the HQ city info to be a simple text field.

I currently use Stacker to create a front end application linked to my Airtable base and I’m not sure how to structure this update to my base so that I can keep my linked table of US states from getting cluttered with foreign states.

Any ideas/thoughts are welcome!

Hi @Alec,

This is an interesting problem, especially as your contact list grows. I’ve set something up very similar to this, using the same approach, for a client with a large number of contacts (>1000) all over the world. The approach I took was this:

  • Country is an attribute that will be used for all contacts, no matter where in the world. This was its own field. It was a linked field so the list lived in a separate table. That table was actually synced in from a separate base. The only reason for the sync was to alleviate any unwanted adding of countries. This can happen when the table feeding the linked field lives in the same base and a user is typing in the country, misspells it, and doesn’t catch that. The “misspelling” will be added to the list as a separate country. Can be a headache later down the road.
  • State/Province is another field I included. This field may not always be used but its good to have. It was also a linked field done like Country.
  • City is just like the 2 fields above.

So, in short, I set up fields like you have. Its a good approach, especially as your contact list continues to grow and other people begin to look at the list. As for setting up a text field, I simply used a formula field to create the text field that other apps could look at or reference.

Hope this helps. Feel free to reach out if you want to discuss further.
Chris

1 Like

Thanks for your response @Williams_Innovations ! I think making a separate tab for the Countries and States will also be the solution for now. Funny what you said about misspelling countries creating headaches - as I am the primary user for now, all these headaches are made by myself :sweat_smile:

I suppose i can do the same with cities, it will probably make better sense further down the line as I seek to further manipulate the data. I wasn’t sure if you caught other “dilemma” though: I have tabs for “Contacts” “Orgs” “States” and a few others. I link the individual states to the Contacts, and when you look at the Orgs it shows all the different states they have Contacts in. But now I want to specifically say the country, state and city where each Org is headquartered.

I guess I’m just looking for feedback about that question of structure.

Thanks again for your reply!

Hi @Alec,

One thing, and for me its been a very important lesson to learn, its easy to make a mistake and not catch it when manually typing in data (ie: short text field). I know for myself, if I’m actively thinking about something else and I’m just trying to type the name of something, I could easily misspell it and not know it till much later.

As for the other dilemma, if I’m understanding it correctly (one tab show one record and the other tab has multiple), that is how linked records work in Airtable. I’ve included a picture that should help illustrate it, where states are linked to contacts. In the contact tab, each contact would have 1 state and in the state tab, each state will have multiple contacts. Also, if I just explained something you already understand, please disregard it.

Question: Are your Orgs a lookup from Contacts or are the States a lookup from Contacts in your Orgs table?

Another approach would be to have each HQ listed in one table (tab) and a running list of locations in another, then simply link a location to a contact, org or HQ.

Thanks,
Chris

Hi Chris,

Thanks for the visual! That’s essentially it as of now. My Contacts, Orgs and States tabs are all linked record field types - no lookups. I suppose they could be lookups in the States tab, but for the contacts and Orgs I 'd need to be able to easily manipulate the field data - if I changed the field types to lookups, that wouldn’t work out the same?

Ideally, each contact will be linked to just one state, each org will be linked to just one HQ state/city/country AND linked to all of the individual (USA) states that their contacts work in.

I’m thinking it is the “States” tab that needs reformatting, perhaps to include all the location data: country, state, city. Do you think I’ll need to separate these in order to properly link them for the contacts’ states, the orgs HQ data, and the list of states that each org has contacts in?

Thanks,
Alec

Hi @Alec,

So, this prompts a few questions from my side. I assumed your contacts and orgs have locations that wouldn’t change often (ie: physical location of the entity, like where the contact lived). If this isn’t the reason for the location, maybe its more fluid like location the contact is in for the given work they are doing, then that could change it a bit, however you would want it to be easy to update. That said, lookups will update when the record they are pointing to changes.

As for your second question, I feel this could be more easily answered by visualizing how you want the data to flow. I’ve included another image that I feel helps simplify the flow and management of data. Keep in mind, this may look more complex to set up, but I lean towards this approach for better management later on. A couple of things to note about how I’ve linked the fields here.

  1. Cities/Towns will be an attribute of a State/Province most of the time and a country all the time. In the past, I’ve used this logic to set up a Location flow, where I link a City to its respective State and Country. Then, I use the City to link to other entities like contacts or orgs. I apply lookups when I need the state or country data. Depending on how many people will be in and out of the Airtable base and how you want to set up permissions later on, it may be worth setting up a separate Locations base and then syncing the data into any other base you want. The plus here is that you can centralize your locations data which will reduce the number of updates/edits you might have to do later on.
  2. I’ve linked Orgs to their respective HQ. At this point, you have options.
    A. Set up Locations for HQs and Orgs independently.
    B. Set up a Location for one and use a lookup for the other. (Caution with this option: Locations are generally entity independent, so an Org and a HQ could have separate physical locations). Not sure of your application but I like to plan for any scenario I can come up with so I correct less later.
  3. The way I’ve visualized this information is a separate base for each of the following, that contain the following tables. If you didn’t want to have separate bases for any reason, you would just make each of the tables live in one base.
    A. Locations (City, State/Province, Country)
    B. Company (HQ, Org)
    C. Personnel (Contacts)
  4. The arrows point to the information you want to link, assuming you are in that table. (ie: if you are in the Contacts table, you would want to link Cities and Orgs).

Also, I recommend the an app called Miro to visualize the flow you want. I’ve used it a lot in the past to help me understand where I want to go in Airtable. Also, in my experience, I’ve saved a lot of time by planning ahead and then setting up the processes I want, rather than reworking them later when they expand or I bring others in.

I hope this helps.
Chris