Film Credits Crew Database help please!

[Shannon_Bradley]

Hello amazing users!

I have been working with AirTable Pro for several months now and I am constantly thrilled with what it can do. However there is something I simply cannot wrap my head around.

I have a list of Crew members for my show. Each crew member may fill a different specific role on a specific shooting date. They also may have other things that they do in the production that gain them a film credit.

Here is my problem:

• I want the first table that has one record per Crew Member with all their contact info etc
• I want the second table that allows to select a crew member at one of the “jobs” they are qualified for for a specific date
• I want a third that lists a record for every credit that needs to appear, in a specific order

Example:

Crew member Artsy McAwesomesauce is the Art Director. He is also a props master, droid builder, droid handler and concept artist.

On the first table I only want to see Artsy once (1 record)

One the second table I want to select him as Art Director on Jan 1st, Props Master on Feb 1st and Droid Handler on March 1st (3 records in different filtered views per shoot day)

On the third table I need to see all of his credits on separate lines, and in the specific order of Art Director, Concept Artist, Droid Builder, Droid Handler, Props Master (5 records)

Currently I have the main table with 5 lines for this crew member, each as the separate job. I then select whichever line is appropriate to work on set for specific dates (in filtered views). For the credits I have to manually review the whole database and create the list in a separate Google sheet.

I’m sure there MUST be a way to do this, but I think I have now stared at this for too long and I can’t make my brain work anymore lol

Any suggestions would be greatly welcomed!

[Dominik_Bosnjak]

It’s early in the morning here so sorry if I misread but it sounds like you have plenty of options - lookups, rollups, all revolving around linked records. It sounds like you want this system on full auto, but that should be possible even with no coding, just using the automation UI. If you can post a mockup of you base with fake data but the same hierarchy and relationships you’re describing, you’ll probably get a more concrete answer faster.

Oh, and the destructuring requirement is a parsing problem simple enough for a formula to solve. That and some markup, see here.

[Shannon_Bradley]

Markup is something I haven’t figured out. I will go searching into how that works, thanks!

[Dominik_Bosnjak]

I just realized that “some markup” killed my link in the previous post, this is what I wanted to share: Markdown syntax for Airtable rich text formatting – Airtable Support

[kuovonne]

Welcome to the Airtable community!

It sounds like you currently have all your data in one table. However, it sounds like your data is better arranged in multiple tables with linked records.

A [Crew Members] table would have each crew member listed once.

A [Work Schedule] table would have a linked record field to link to a record in the [Crew Members] table. It would also have a date field, and a field for the job. The job could be a single line text field, single select field, or another linked record field to a third [Jobs] table, depending on the needs for your data.

To see all of the credits for a crew member, you could use a rollup field in the [Crew Members] that rolls up the job from the [Work Schedule] table with the formula ARRAYJOIN(ARRAYUNIQUE(values), ", ").

The order of the jobs would depend on the order of the linked records in the linked record field. If you do not want to have to manually sort the records, you could have a [Jobs] table with a numeric job order for each table. In the [Work schedule] table, have a rollup of the job order from the [Jobs] table. Then use the Batch Update App (available for pro plans) to sort the linked records in the [Crew Members] table base on the rolled up job order number. You would need to re–run the Batch Update periodically in order to update the sort.

It would also take some work to migrate your data from its current state to the new table structure. Whether you migrate the data manually, using formulas, or using other code depends on the amount of data you have and how it is structured.

[Shannon_Bradley]

Thank you I will read this over!

[Shannon_Bradley]

That makes a ridiculous amount of sense! My database started quite simple and the utility has just outgrown what I set up originally. I think it would be well worth the effort to set things up in the way you suggest…I might be able to do it in parts.

Guessing I could indicate the jobs a crew member would qualify for and use that as a lookup on the Work Schedule table somehow.

This gives me a direction! Thank you!