My name is Paulo and I work for a production company. Here we produce Feature Films, TV Shows and also TV Ads.
I’m working on a FileMaker solution to better control the information of each job. (I'm new to FM)
I have a table called CONTACTS, where I store information about all the people that have worked for us. In each job, a person can have a different role, so I didn’t create a role field, but a join-table called CAST_N_CREW. (ex. A person can work as an assistant director in several movies and then became a diretor, so there will be some job records where the person had a role of an assistant director, and some more recent jobs where the person has a director role).
The ROLES table could be replaced by a value list, since it has around 20 role records (Director, Producer, Editor, etc).
All that works fine.
What I need to do is to connect the roles (Director, Director of Photography, Executive Producer, Editor, etc) to CONTACTS, where when committed, will create a CAST_N_CREW record for each field, with that contact, and that role.
I've two Contact Records:
Contact Record 01: "Steven Spielberg"
Contact Record 02: "Bill Butler"
I've one Job Record: "JAWS"
In the cast panel, the user clicks in the director popup menu field and it shows all contact records (in the example case, Steven Spielberg and Bill Butler).
The user then selects one of the contact name for the director role, Steven Spielberg.
Next, the user selects Bill Butler as a Director of Photography.
FileMaker then creates two new records for CAST_N_CREW, where one has Steven Spielberg as a director for JAWS and another with Bill Butler as a Director of Photography for JAWS.
Anyone has any idea on how can I do this?