Help

Best way to organise many-to-many across multiple roles

Topic Labels: Base design
Solved
Jump to Solution
1838 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Antonia_Mochan
5 - Automation Enthusiast
5 - Automation Enthusiast

I am building an archive of the activities of my amateur theatre company.

The main table is productions - for each production I have a number of fields that relate to people - Author(s)/Director(s)/Cast/Crew. These are linked to tables so that I can see all directors and what they have directed, all authors and what they have written etc.

Now, in this group, someone might write one production, direct a different one, act in another and be backstage crew in another. I would love to be able to have a People table where it shows that Joe Bloggs as an actor in XXX, crew in YYY - so you get an overview of everything Joe has done across all productions. At the moment Joe is listed once in Actors and once in Crew.

Any suggestions as to how I do that? The Base is here: Airtable - Brussels Amdram Theatre Archive

1 Solution

Accepted Solutions
49erAllie
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there Antonia!

What I would recommend doing here is getting rid of any tables that are duplicates of your “people” table to eliminate confusion

and then changing all of your linked fields (Author(s)/Director(s)/Cast/Crew) so that they are all linked to the same table, “People”

If you have 4 linked fields in your “productions” table that all link to “people”, your “people” table will have 4 equivalent linked fields that should behave exactly as you’re describing.

Airtable will NOT automatically correctly name these, so I recommend doing these one at a time.

What I mean by this:

Step 1: Edit the “Authors” field on your “productions” table so it links to the “People” table, instead of the “Authors” table
Step 2: Delete the “Authors” table since it will not stay up to date and you no longer need it
Step 3: Hop on over to the “People” tab, and identify which linked field you just created. The name of the linked field will be something like “Productions” or “Productions 2”. Rename it to “Author”. This field will now show all of the productions each person is “author”.

Remember: this works both ways, so you can add productions to people via the people table, or add people to productions via the productions table

You can also use this field as a filter, ex: if you want a list of ONLY your authors, filter your list so it only shows you People where “Author” is not empty.

See Solution in Thread

6 Replies 6
49erAllie
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there Antonia!

What I would recommend doing here is getting rid of any tables that are duplicates of your “people” table to eliminate confusion

and then changing all of your linked fields (Author(s)/Director(s)/Cast/Crew) so that they are all linked to the same table, “People”

If you have 4 linked fields in your “productions” table that all link to “people”, your “people” table will have 4 equivalent linked fields that should behave exactly as you’re describing.

Airtable will NOT automatically correctly name these, so I recommend doing these one at a time.

What I mean by this:

Step 1: Edit the “Authors” field on your “productions” table so it links to the “People” table, instead of the “Authors” table
Step 2: Delete the “Authors” table since it will not stay up to date and you no longer need it
Step 3: Hop on over to the “People” tab, and identify which linked field you just created. The name of the linked field will be something like “Productions” or “Productions 2”. Rename it to “Author”. This field will now show all of the productions each person is “author”.

Remember: this works both ways, so you can add productions to people via the people table, or add people to productions via the productions table

You can also use this field as a filter, ex: if you want a list of ONLY your authors, filter your list so it only shows you People where “Author” is not empty.

49erAllie
5 - Automation Enthusiast
5 - Automation Enthusiast

Additionally, I wrote a blog post a while that may help you out or give you some ideas

In your case, replace “Responsible”, “Accountable”, “Consulted” and “Informed” with Actor, Director, etc

Thanks, I will give this a go.

It worked perfectly!

This is apparently a conscious decision by Airtable, which I personally don’t like.

I don’t either-there are so many good use cases for multiple linked fields like this