Skip to main content
Solved

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

A

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

Best answer by 49erAllie

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.

View original
Did this topic help you find an answer to your question?

6 replies

4
Forum|alt.badge.img+4
  • 49erAllie
  • Inspiring
  • 6 replies
  • Answer
  • November 3, 2022

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.

4
Forum|alt.badge.img+4
  • 49erAllie
  • Inspiring
  • 6 replies
  • November 4, 2022

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

A
  • Antonia_Mochan
  • Author
  • New Participant
  • 2 replies
  • November 4, 2022
49erAllie wrote:

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.

A
  • Antonia_Mochan
  • Author
  • New Participant
  • 2 replies
  • November 4, 2022
Antonia_Mochan wrote:

Thanks, I will give this a go.


It worked perfectly!

4
1 person likes this
  • 4
    49erAllie
Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • November 4, 2022
49erAllie wrote:

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.


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

Jan - Databaser
4
Forum|alt.badge.img+4
  • 49erAllie
  • Inspiring
  • 6 replies
  • November 4, 2022
Databaser wrote:

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

Reply


Most helpful members this week

Terms & Conditions