Help with Linking Countries/States/Cities to records

[Alec]

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!

[Williams_Innova]

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

[Alec]

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 :grinning_face_with_sweat:

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!

[Williams_Innova]

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

[Alec]

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

[Williams_Innova]

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

[Alec]

Hi Chris,

So I’ve changed the structure a bit more from your last recommendations and I think I’m ALMOST there! I have four tabs in my base now:

1. Contacts: i have many fields here, but only the states (USA only because my contacts are not international) are linked to my States tab. The Contacts are linked to their Organizations, but all the info about the HQ of the Orgs doesn’t need to be displayed/linked/looked up here.

2. Organizations: Again, i have quite a few fields in here - field linked to the Contacts, field linked to States tab (the idea here is to show all the locations linked to each contact at each Org), and fields linked to HQ Data (this is where I’m still a bit confused).

3. States: just a list of states here with two linked fields to Contacts and Organizations where it shows all the contacts and orgs linked to each state.

4. HQ Data: the primary field is the City, and next I have State and Country as single select field types. I also have the fields linked to the Organizations tab but I’m missing something (see attachments).

^ I don’t need all these fields showing the linked Orgs in the HQ Data tab, but if i delete them then it will break the links to the Organizations tab and I won’t be able to do the lookup fields, correct? Better if i just hide the fields?

I believe I should be using some lookup fields in this situation no? I really only want the HQ information to be linked to each Org. While leaving the State tab alone so it can serve it’s purpose with the Contacts tab and showing all the locations/offices of each Org.

Am I as close as I think I am? :grinning_face_with_sweat:

Thanks,
Alec

P.S. I just started using Miro for another project actually, and I have a lot to learn there for sure - the flow charts you’ve been making are quite helpful!

[Williams_Innova · Accepted solution]

Hi @Alec,

Yes, I think you are very close!

As for your questions, here are my responses…
When you link a table to another table by creating a “Link to another record” field, you will simultaneously create a linked field in the other table. This is how Airtable shows a linked field, with a link in both tables. The only way this doesn’t happen is by creating a linked field to the table you are in, say if you wanted a field in the Contacts table to have other contacts, maybe for supervisor or project partner or whatever. I recommend hiding these if you don’t need to see them. If you delete them, it won’t delete the field in the other table, it will convert it to a Single Line Text field because you’ve deleted the link, so Airtable doesn’t know what to look at.
As for look-ups, I find these very handy. So for this instance, you can do exactly what you are thinking. If you link HQ to Org, then Org to Contacts, and then Contacts to States, you could pull States into HQ eventually. You would have to create a look-up for States in Org (via Contacts), then create a look-up for States in HQ (via Org). I’ve included a link to a base that I created that mirrored the Miro (HA, no pun intended there) board. You can see in this base how links are working and the use of look-ups. I did link through Cities, as I did in Miro. Its obviously just an example.

Airtable

People, Orgs, & Locations - Airtable

Explore the "People, Orgs, & Locations" base on Airtable.

And yes, Miro is great!

Thanks,
Chris