Appending new fields to Linked Records without affecting original record?

Greetings! Newbie here, I think the best way to ask this question is to just explain what I’m trying to do.

We produce comedy shows, and are trying to create a table that will contain all the pertinent info for each show. Here’s how it’s set up:

  • One table, called TALENT, contains records for each performer, with the typical vitals (name, phone, headshot, social media, etc.)
  • One table, called VENUES, contains all the same kind of typical vitals, but for the physical locations.
  • One table, called SHOWS, aggregates one linked record from VENUES, and multiple linked records for every performer we’re using from TALENT, along with other show specific fields such as date and time, etc.

Here’s the problem:
For each performer’s linked record, we want to append a few fields, like CUT RATE (currency), FLAT RATE (currency), and POSITION (example: opener, featured, headliner). These vary from show to show, and we may have the same performer on two show records simultaneously getting paid different rates, so these fields can’t live within the TALENT table, they have to live within SHOWS, but they need to reference an individual record from what is a list of linked records.

Any idea how to do this? Thanks in advance!

Another table!

You have a table that joins Talent to Venues via Shows. What you need is an additional table that joins Talent to Shows via … “Bookings” perhaps.

So the “Bookings” (or whatever you name it) table will link out to Talent and to Shows, joining one Performer to one Show. You’ll have one record in this table for each instance of a Performer participating in a Show.

These records will contain “Show specific” info like the pay rate and position in the lineup. And any data from these “Bookings” records that you want to see in your “Show” table you can pull through with Lookups and Conditional Lookups.

So in the end:
A Venue will have many Shows
A Show will belong to one Venue
A Show will have many Bookings
A Booking will belong to one Show
A Booking will have one Performer
A Performer will belong to many Bookings

Brilliant! And I can have the BOOKINGS table contain a linked record back to the relevant SHOW (as you said, a booking belongs to one show) which in turn SHOWS can reference to autopopulate the list of show bookings per show.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.