My Mind Has Been BLOWN 🤯 Linked Records Issue

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.

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.

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.

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:

Or Denver, North Carolina. :winking_face:

@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.

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

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?

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.

If you subsequently wish to remove the State name from the County Name field in the Counties table then you can do so using a formula field with some string manipulation.

If you have associated data with each City, County and State then you can get this into Aairtable by filtering in Excel and copy-pasting like explained above.

(BTW - filtering in Excel is done as explained here: https://support.office.com/en-ie/article/filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2 )

@Bill.French this is an excellent suggestion. Since your post I’ve done some homework to familiarize myself with the system. I pulled all the FIPS codes and, correct me if i’m wrong, but it is the state, county, city combination you alluded to which makes it work. E.g. 00100 Place Code references:

00100-ABERDEEN
00100-ABBYVILLE x4
00100-ABBOT
00100-AASTAD
00100-ABSECON
00100-ABERCROMBIE
00100-ACHILLE
00100-ABERDEEN x2
00100-ABBOTT
00100-ABBOTSFORD

Which puts me in the same fresh, eyeball bleeding poop stain I was in before, but the combination of 16-11-00100 references Aberdeen, Bingham County, Idaho.

Now the job is to make a state table, county table, and city table and have the county look up the state code and the city look up the county and the state code. I’ve begun this process and am happy to report back my progress to the community.

Thanks to @Nathalie_Collins (Coolest name in the world, btw :winking_face: ) and @David_Skinner for posting suggestions to help a noob move this ball forward, as it were. And thank you so much @Bill.French for this FIPS nugget. I’ll let you know how it turns out. If you feel like posting an idea or two to help me accelerate this linking process. I’m all ears since you have probably done this 58968561498 times before.

Yes, FIPS doesn’t resolve the fundamental challenge you face, but it does leverage an open standard that makes it an easier climb and likely more sustainable. Governments tend to fail at nearly everything they try. FIPS is one of the rare useful outcomes of your tax dollars at work.

That’s great to hear it’s moving forward. I am one of the worst when it comes to referential linking in Airtable but someone here will probably chime in soon.

If you now have unique FIPS codes for each city, county and state then it should make my copy and paste system even easier. Make sure the primary field of each table is the FIPS code. Then when you paste the State FIPS code into the State column in the County table it will automatically link to the correct State.

You should be able to bulk copy and paste all of this data quite easily.

Unfortunately, FIPS is only unique by county/state (first 2 digits are state, last three are county and county IDs are only unique within state) T0 build uniqueness among cities, you must add a city name (prefix or suffix). In practice this is typically done by transforming spaces in city names into underbar characters so they can be easily removed when/if parsing.

Check out this table I made with Every Zip, City, County and State all rolled up in a multi-tier hierarchy from the 2010 census. This has become a very useful table at my company as we often turn on features, ad campaigns and messaging based on location, so anyone can come in here, grab all zip codes from a city, state, county as a comma-separated list to select an area. Since everything is running as linked records, it is very easy to manipulate as well. I made this table in < 1 hour from a large CSV (It only took this long because this many rows creates a slow webpage regardless).

Let me know if you have questions about how I structured this. Since I apparently cannot post links here, please remove the spaces from the line below to access
https ://tinyurl.com/ y26kpsmu

Thank you for posting, @Nick_Guardino. While this doesn’t directly solve the problem of how to link a table to another where there are multiple choices of the same name, It’s a wonderful shortcut of sorts! You taking the time to post your creation is a large part what makes this product so great. Just wanted you to know how much it’s appreciated.

NAB