Cross-reference by date range in another table

[AoifeGrant]

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!

[yuannc12]

This is what Im looking for as well!! and I would like to have the summary of reference. like uniq count 🙂 

[Sho]

If the Actor's link field is divided into multiple fields, it will be difficult to achieve this using only the basic functionality of Airtable. It must be processed by a script.

If the Actor field is a single multiple link field (multiple selections are possible in one field), you can use the ROLLUP field to ARRAYUNIQUE(values) to extract the unique values of the Actor and ActingAssociation.

I guess there should be more functions to operate on arrays in formula fields.