My Mind Has Been BLOWN 🤯 Linked Records Issue

[Nathan_Beckstea]

Not the best at Airtable (AT)…yet :smirk: but what i’m doing is feeling WAY too difficult so i’m wondering if there is a better way.

I’ve made a base with four main tables: Cities, Counties, and States with the fourth being items that pertain to them.

Of course, all this was built out in Excel originally so I import everything I can to keep the servers from tearing apart then I sit down to a hot supper of copy-pasta under Linked Fields.

Here’s the rub: Did you know there was a Phoenix, Oregon? How about Phoenix, New York??

The city/county/state tables are exhaustive. They must be for our purposes. This means that each and every city or county must be verified to be in the correct county or state or the wheels will come off.

Hopefully you can see the issue: I cannot paste a column of data into Airtable and have it reliably link to the correct “parent?”. I’m thinking I have to concatenate a string of city, county, state in the Primary field, but don’t know if this is correct. Currently the primary field in each table is the name of the city, county or state while the fourth table is the name of the item. It feels like i’m mowing a golf course with a push mower here…

FYI it’s a little south of 40k records so i’m looking for any help I can get.

NAB

[Nathalie_Collin]

Hi @Nathan_Beckstead

While I love pasta, I do not envy your supper.

What about having just two tables. One for location (ie City, County, State) and with the other for your items. Items would then be linked to location.

[Nathalie_Collin]

Also, you mentioned that it was all in excel before. Why not just import everything and then convert the field. Must easier than copying and pasting.

[Nathan_Beckstea]

Such a good question! I actually built a table with cities, counties and states integrated but it seems to fall apart when I try to factor in the heirarchy. In other words, I don’t know how to make the cities, counties and states
hierarchical and still maintain a single instance of the data. I’ll post an example in a second.

[Nathan_Beckstea]

When you upload a CSV, all fields default to single-line text. I’m not aware of a way to import relationships. When you then convert to a linked field, AT chooses what seems to be the top choice among however many choices there are in the table you’re linking to. Care to guess how many "Fairfield"s there are in the US??? What the h311 have I done!? :slightly_smiling_face:

[Bill_French]

Nathan_Beckstead:

Did you know there was a Phoenix, Oregon? How about Phoenix, New York??

Or Denver, North Carolina. :winking_face:

[Bill_French]

@Nathan_Beckstead,

Yeah, I feel your pain. Importing any sizeable collection where target data types are critical is never going to work well because CSV files are really dumb; they’re just numbers and letters.

I haven’t tested this, but I’ve often wondered if there was a way to set up the fields and types and then import a CSV file hoping to get the data to transform in-flight into the proper data type values. Maybe someone here knows the answer to this question.

For my clients (where data types are really important), I set up the target tables and field types and formulas in advance. Then I thoroughly test the links, lookups, etc. with a few test data rows. Then – AND ONLY THEN – do I use the API to perform the import process. This leaves me with a fully functional solution the instant the data arrives. It is ready to test and prove it works.

Occasionally there are hiccups, but making adjustments in the API, clearing all the data from the tables, and running again is very easy as opposed to starting over from a CSV import that had to be modified to meet some requirements.

My success in importing has always come at the expense of detailed and methodical planning and fully testing the Airtable model before introducing the data. Lastly, with a known functional Airtable solution, the next step is to model the data to fit inside it during the API upload process. Only server-side code using the API is able to model the data to fit the desired target solution.

I was reading your post a second time and it occurred to me…

This means that each and every city or county must be verified to be in the correct county or state or the wheels will come off.

You should consider introducing US FIPS codes into your data architecture.

A city name plus the FIPS code for a county is a unique identifier since no county in the US has two cities of the same name. I have used FIPS for many geo-location projects and this is the best way to craft a unique identifier for cities.

Ideally, your “FIPS keys” should be of the format:

01001-Plattville (which would be Plattville in Autauga County, Alabama)

There are many advantages to using FIPS including (but not limited) to these -

• The code is really a concatenation of a 2-digit state code plus a three-digit county code which means you can know state and county separately which also means you can reverse-encode.
• These codes are managed by Uncle Sam, and it’s an open standard which means you can automate the inclusion and awareness of future towns and cities.
• County is yet another metric that tends to become valuable in any project or solution where location science is at its core.
• There are open and free FIPS web services that can be used for instant lookups, integration, etc. Or, the FIPS database can be freely integrated into your Airtable solution.

Nathan_Beckstead:

It feels like i’m mowing a golf course with a push mower here…

I think a better metaphor is mowing a sod farm with Barbie’s Cut’N’Curl beauty scissors. You need planning, architecture, and automation to mow 25,000 acres.

[Bill_French]

(post withdrawn by author, will be automatically deleted in 1 hour unless flagged)

[Nathalie_Collin]

Nathan_Beckstead:

I try to factor in the heirarchy

You can’t think hierarchy so much as perhaps just think of it as a flat file (in this case).

Sometimes when we’re too vested in something, we tend to overlook things. I think this is one of those times. Why not have one sheet called locations and in that sheet, have the multiple fields, one each for; city, county, state? Why are you trying to create separate sheets for each component?

You mention all this was in Excel. How was it laid out before?

[David_Skinner]

Hmmmm. I think you need to do some work in Excel first and then enter the data in the right order into Airtable afterwards.

Your main problem is that you need to identify different Counties with the same name in different States. To do this I think you will need to concatenate the County and State in Excel before import. Something like this:

=CONCATENATE(A1, " (", B1,")")

Where A1 contains the County and B1 contains the State. Which will give you a column with the County names in this format: Phoenix (Oregon)

You’ll then need to filter the spreadsheet by the this new combined County (State) column to get a list of all unique Counties.

Now, back in Airtable, assuming the Counties table has just two columns:

County Name, State

Where the County Name field is just a text field and the State field is a linked record field to the States table. If you copy and paste your filtered excel data (just the new combined County State column and the State column) into the Counties table it will create a new record for each County and fill the State field too. In filling those State fields, it also creates a unique record for each State in the States table.

So we have all of the Counties in the Counties table linked to the correct States in the States table. Now for the Cities.

Go back to Excel, remove the filter that you applied to filter out duplicate Counties. Now, assuming the Cities table has 2 columns:

City Name, County

Where the City Name is a text field and the County is a linked record to the Counties table. Copy and paste the City Name and Combined County columns from Excel into Airtable. This will create a new record for every City and link it to the correct County record in the Counties table. Job done.