Linked fields to junction table

[Bruce_Donnelly]

I’d like an option for “Junction table from linked field with multiple records.”

I think that lots of people who start out by linking to another table with multiple value may later find out that they need to have a junction table.

Suppose I have a list of books and authors. One author can write many books, and each book can have more than one author. So I might set up a list of authors and another list of books, and simply link them with a linked field and then allow multiple values. But what if I decide later that I need to track who’s the primary author, who’s a ghost writer, who’s a proper co-author, and who’s just a contributor in each book? There’s no easy way to derive a junction table on which I could put that. For some purposes, it’s easy to anticipate needing a junction table, but for some it may come as a surprise, and I’d like to be able to derive one easily.

I also I think it would be great to have an option for “Junction table from linked field with multiple records” because it would make it easier to create a junction table. I “get” how they work because I’ve done them before, but many people will find them counterintuitive at first. Airtable is so good at enticing people through ease-of-use that it would be a great way to make something hard easy!

[Tim_Pituch]

@Bruce_Donnelly Did you figure out how to do this yet? I’m looking to do this as well.

[Bruce_Donnelly]

I’m afraid not, sorry.

[Matt_Gerasolo]

I think I have had a similar need. I suspect it is more of a View item than an actual table.

The problem that you describe there are two ways to approach, in my experience. Option 1 may be a work around for you.

1. Have a table called “Books” and another called “People”. Then on Books you would have a field for each possible “Role” such as Primary Author, Co-Author, Ghost Writer, etc. And have each of those fields be a linked field to People. Then just setup a view that just shows the info you want. However as you have more Roles, this gets messy. If there are a 1,000 possible roles, this method gets to be very unruly and inefficient, but it can work if there are limited “roles”.
2. The proper way to do this in a relational database method, from what I have been told, is to have 4 Tables. “Books”, “People”, “RoleNames”, and “Assignments”. RoleNames would be the possible Roles, such as Primary Author, Co-Author, Forward By, etc. And then Assignments would have 3 fields which links The Book, Person, & Role. Since AirTable requires What AirTable by default call “Name” which is really used as the rough equivalent of Primary Key, You can rename the Name Field to AssignmentID and set it to a type of outnumber. Then add in your 3 linked fields and any lookups for any additional fields you want. Where this will get weird is how this will display back in the main table, as it will make a field with the AssignmentID showing up. You can hide that field though. And you might be able to do a lookup table if you want the properties to show up properly on the main book table.

[Bruce_Donnelly]

Very clever, good idea. But Airtable needs a better way.