One to many became many to many, need a join table
I have a database part of which currently holds many projects. Originally, each project was assigned to only one drive (but one drive could hold many projects) We have decided to allow one project to have multiple drives assigned to it. My issues is that I can't figure out how to allow this action.
The database is fairly complex, but here are the relevant tables:
Drive InOutHistory (used to record the date a status of a drive was changed, per FileMaker's help section)
A drive is connected to a project through a foreign key, and connected to both a client and a person through foreign keys. I know that if I only had the project and drive tables I could create a join table, called Drive Assignments, but I don't know what to do in this situation.
The drives change from project to project, so ideally I would like the join table to populate automatically. Currently, everything is controlled from the project table. A use can select one drive from a pop-up menu and the in the drive table a list of the projects attached to a drive appears in a portal. Ideally, I would like the use experience to remain the same. i.e. a user can be in a layout of the project table and select one or two drives to associate with a project, and the project would appear in a portal on the individual drive pages and the relationships between clients, people, drives, and projects would remain the same. If necessary, I could delete the Drive InOut History table and just used the last modified data.
I'm very new to FileMaker, so I would really appreciate any advice you could offer. Thanks!