Help

Auto-entering data based on value of another table/field

Topic Labels: Base design Data
688 9
cancel
Showing results for 
Search instead for 
Did you mean: 
RedKev
4 - Data Explorer
4 - Data Explorer

New to Airtable, hoping someone can point me the right direction.

I am building a database of Aus Music Chart history. I have an Excel sheet that has columns like Song, Artist, Year, Position, etc. As a test, I've copied and pasted some 3000 rows/records into an Airtable table. (Yes, overquota on the free plan for now.)

What I want to do now is create another table for Genre that will automatically update the 'master' table (as describe above) based on an Artist's name. So, for instance, if I enter The Beatles > Pop in the Genre table, it will automatically assign the value 'Pop' into the master table wherever Artist = 'The Beatles'. 

Does that make sense? I've played around with relationship/linked Lookup fields, but the best result I've had is having to re-input the Artist name in a separate field -- i.e. it's not automatic. As I said at the start, there are already 3000-odd records as a test -- my master Excel sheet is well over 25,000, so entering each again manually is out of the question.

Thanks

9 Replies 9
dilipborad
9 - Sun
9 - Sun

Hello @RedKev,
Couple of things you need to understand first when you come to Airtable from Excel/GSheet OR another background.

⁜ Quick solution(Suggestion) ⁜ 
Here in airtable starting point is always a Structure.
You need to follow the rules like Base→Table→Columns(different types and formulas)→Rows.
Then based on it link multiple tables(which is part of the structure), design views using different filters and shorting.

⁜ More Details ⁜ 
Now comes to your point that you mention that you already have data that you need to migrate to Airtable and you're just new to Airtable.
These are my recommendations.
1) Design Workflow ↠ Before directly jumping to Airtable design your workflow with actions that you're currently doing using current tools and integrations.
2) Research ↠ If you've used similar tools and have a bit of technical background you can easily research and understand things about possibilities with airtable based on your workflow.
3) After doing 1) & 2) points you can easily able to structure your database and then import the data.
4) After importing data in your case there are need to separate some data into another table and link it. If you need one time(manual) then use an extension script to rearrange data into the proper table with linkings. Otherwise, use Airtable Automation which is doing stuff in the background when some actions are done.
Note: Use of extension script or automation needs some specific knowledge.

If you don't get about how to implement things then you should at least have workflow.
Then use consultation with some of the experts in Airtable otherwise you won't able to utilize all the features of AT.
You can message me.
👍

Hmm, if I were you I'd have "Master", "Artist" and "Genre", and the data would look like this:

Screenshot 2024-07-06 at 3.27.07 PM.png

Screenshot 2024-07-06 at 3.27.10 PM.png

Screenshot 2024-07-06 at 3.27.13 PM.png
And so when you update the Genre value of the "Artist" record, it'll show up in your "Master" table via a lookup

Link to base

Thanks Adam. Wouldn't this approach necessitate inputing again at least some of the data? Or manually assigning Genre to each artist? Which is the tedious bit I want to avoid. Or am I missing something?

Hm, so your Excel sheet also includes a "Genre" column now? If you could provide a read-only invite link to a duplicated copy of your base with some example data I could take a look at this for you!  Once I can see how your Excel data looks I could try to set something up

You can bulk-insert links by usual copy-paste, applied to the whole field. 
But you need to understand what's happening and how the linking works.
2 weeks ago, I replied about such linking, you might find it useful. 
I would recommend to make a snapshot before you start to test

Thanks Adam, here's the share https://airtable.com/app7KSQO037P1nooP/shrJrzyfSGnITq2Cd

No, the Genre column is something I've added in Airbase since I've been experimenting.

Much appreciated. I know there's a solution there somewhere, just don't seem to be making any headway. Probably missing something obvious.

Got it, thanks!  So this means you have to key in the Genre data in some form, yes?

I would suggest:
1. Converting the primary field of the "AMR" table to be the "Name" field
2. Converting the "Artist" field to a linked field to another table, resulting in one record per artist:
Screen Recording 2024-07-08 at 11.25.38 AM.gif

3. Add a "Genre" field to the "Artist" table and key in the Genre there:
Screenshot 2024-07-08 at 11.26.05 AM.png

4. Add a lookup field to the "Genre" field in the "AMR" table:

Screenshot 2024-07-08 at 11.26.37 AM.png

Link to base

Thanks Adam, I'll give it a go. 

Thanks @Alexey_Gusev , I'll review you previous post about bulk inserting links. Thanks.