Basic question about linking records between tables

[Patrick_Hughes]

Hi all, I’ve been using Airtable as an employee for the last few months but only recently have started building some tables on my own, and I’m running into a hurdle that I think either has an incredibly simple answer or just requires a different approach. After going through forums for hours, I figured posting might be the quickest way to find a solution.

This will be a musician related database, and I have two tables - ‘Owners/Writers’ to keep records of artists:

And ‘Songs’ to house the information for each song they might be a part of:

As you can see, each song may have multiple writers. I want to create a column in the ‘Owners/Writers’ table that shows every song that each writer may be a part of; for instance, Ben Allen is a writer on both of the example songs, Song 1 and Song 2, and I’d like those records to be displayed in the ‘Owners/Writers’ table.

The problem is when I create a linked record in the ‘Owners/Writers’ table to the ‘Songs’ table, it doesn’t automatically populate - which is my ultimate goal here. It creates the ability to select the songs manually, but I need that info to autopopulate if I enter a writer on any song. So in that scenario, as I add new songs to my table (often with the same writer involved), it will show up, linked, in one column in the ‘Owners/Writers’ table.

Am I missing something incredibly basic here? I’ve played around with rollup/lookup fields and have been able to get the names of the songs to all display in one text-only field, but I really need those to be linked to actual records.

Any advice would be very appreciated!

[Marko_Petrovic]

I think you might be doing something wrong, or I totally misunderstood your question :grinning_face_with_big_eyes:

When you link two tables Airtable automatically makes new field in “other” table that does auto populate. In our example we have a Table with all our clients and separate Table where our projects are. When we made a linked field in project Table and pointed out to our Client Table Airtable automatically made a field in Client table where it shows all of projects that client is added to. I am not sure where did you made a misstep, as this is default behavior.

On a side note, if you want to make your base a little bit cleaner, you don’t need 2 fields to add 2 writers to same song (unless you want it to be that way). You can use one link and just select allow linking to multiple records.

[Patrick_Hughes]

Thanks Marko - the reason we have a field for each writer, is that each song has different ownership percentages; those percentages are attached to a formula to determine royalty payments (those columns were hidden in the previous screenshot):

Yeah, I’m not sure where I’m going wrong; here, you can see I’ve created a linked column that points to the Song table:

But you can see, it doesn’t autopopulate:

I can definitely click and manually link each writer to a song, but my question is - why don’t they link automatically upon the creation of that column, if they’ve already been selected as a writer in the Songs table?

[Marko_Petrovic]

Ok, let me show you quickly what should happen, and maybe somewhere along the way we find error :grinning_face_with_big_eyes:

Lets say we have 2 bases - Writers and Songs:

Then I go to Songs Table and create link:

At that point in Writers Table Airtable automaticly makes new field (the one in red) that autopopulate.

I didnt create that field manualy, Airtable did it buy default.

So when I go to Songs Table and pick a writer:

Than Writers Table is auto updated:

This is how all of my Tables work by default. I hope this can help you figure out where the problem is :slightly_smiling_face:

[ScottWorld]

Patrick_Hughes:

I can definitely click and manually link each writer to a song, but my question is - why don’t they link automatically upon the creation of that column, if they’ve already been selected as a writer in the Songs table?

That’s not your “reverse link” field. Another “reverse link” field was automatically created for you by Airtable — if you don’t see it, check underneath the “hidden fields” button.

[Patrick_Hughes]

Hey Marko, thanks for this - I just tried this and the only issue seems to be that now, each song that the writer is a part of might be in different columns depending on whether they’re in the Writer One or Writer Two column:

Looks like this in the Songs table:

Is there a way to display all of the linked fields in one column?

[ScottWorld · Accepted solution]

Patrick_Hughes:

Is there a way to display all of the linked fields in one column?

1. You can add all of the writers into the same field in your Songs table, or

2. You can create a many-to-many relationship by creating a junction table, which is a 3rd table that acts as the “intersection” between writers & songs.

[Patrick_Hughes]

Thanks @ScottWorld! I think the junction table might be the way to go.

Appreciate your and @Marko.Petrovic’s input on this.

[ScottWorld]

You’re welcome! That is typically the best approach to take, because it gives you ultimate flexibility to sort/filter/search/group writers from within the junction table.