Dear Airtable Hive Mind,
I have an Actors table and a Movies table. Each Movies record refers to the Actors table via multiple Link fields, i.e. Movies.Actor1, Movies.Actor2, Movies.Actor3,..., Movies.Actor10 in order to capture the full cast, each of which is a Link to the Actors.Name field.
Q1 Each Actor is a member of a particular Acting Association (Actor.ActingAssociation field). How can I pull the Acting Association for each Actor ideally into the Movies table, i.e. Movies.Actor1Assoc, Movies.Actor2Assoc etc. Or do I need a new table, and if so, how to do it there?
Q2 How can I gather a distinct (unique) list of Actors into a new Movies.AllActors field that lists each actor noted across that Movies record (10 Cast Member Actor fields as listed above), ideally as objects rather than text, that will automatically update when a Movie.ActorX field is edited. Plus a related column called Movies.AllAssociations showing the distinct list of Associations for the Actors noted in Movies.AllActors field?
Thank you in advance!