My Mind Has Been BLOWN 🤯 Linked Records Issue

Nathan_Beckstea · ‎Sep 10, 2019

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

David_Skinner · ‎Sep 11, 2019

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-ccf664b... )

Nathan_Beckstea · ‎Sep 12, 2019

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

Bill_French · ‎Sep 12, 2019

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.

David_Skinner · ‎Sep 13, 2019

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.

Bill_French · ‎Sep 13, 2019

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.

Nick_Guardino · ‎Sep 20, 2019

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

Nathan_Beckstea · ‎Sep 28, 2019

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