Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 27, 2020 01:41 PM
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!
Solved! Go to Solution.
Sep 27, 2020 04:09 PM
You can add all of the writers into the same field in your Songs table, or
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.
Sep 27, 2020 02:21 PM
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.
Sep 27, 2020 02:49 PM
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?
Sep 27, 2020 03:36 PM
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:
Sep 27, 2020 03:50 PM
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.
Sep 27, 2020 03:57 PM
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?
Sep 27, 2020 04:09 PM
You can add all of the writers into the same field in your Songs table, or
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.
Sep 27, 2020 04:14 PM
Thanks @ScottWorld! I think the junction table might be the way to go.
Appreciate your and @Marko.Petrovic’s input on this.
Sep 27, 2020 04:19 PM
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.