4 Replies Latest reply on Oct 21, 2013 4:48 PM by standardGeese

    One to many became many to many, need a join table

    standardGeese

      Title

      One to many became many to many, need a join table

      Post

           Hey there!

           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:

           Projects 

           Drives

           Clients

           People

           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!

      Screen_Shot_2013-10-21_at_3.59.51_PM.png

        • 1. Re: One to many became many to many, need a join table
          philmodjunk

               It would appear that you have your join table already in place. Drive Assignments seems to link Projects to a Table Occurrence of Drives and this is exactly what you need for a many to many relationship between Drives and Projects.

               If you place a portal to Drive Assignments on your Projects layout, You can use it to select as many drives as you need for the project by selecting from a drop down list or pop up menu formatted _fkDriveID field in that portal. More sophisticated data entry options are also possible.

               And a portal to Drives Assignments or to projects can be placed on a layout based on Drives 4 to show all projects linked to that drive. (Use a portal to the join table if you want to modify the assignments, Use a portal to Projects if you just want a "read only" list.)

          • 2. Re: One to many became many to many, need a join table
            standardGeese

                 Phil,

                 I tried what you suggested. The join table Drive Assignment was blank. I was trying to set that relationship up when I came to ask for help. I tried manually filling out two records and tried your portal. That worked properly, however, is there a way to make create a join table automatically? For instance, before I created any entries in the join table, the portal would not let me select anything. Once I added one entry, it allowed me to change the drive the something else, but i could not select anything in any other records or add or remove drives. How can I make it so that if  say I made a new project, I could select a drive for it without already having a record in the join table?

            • 3. Re: One to many became many to many, need a join table
              philmodjunk

                   Sounds like you need to enable "allow creation of records via this relationship" for Drive Assignments in the Projects to Drive Assignments relationship. Go to Manage | Database | Relationships and double click the relationship line to open a dialog where you can select that option.

                   You may find this demo file on many to many relationships helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: One to many became many to many, need a join table
                standardGeese

                     Thanks Phil! That worked perfectly! I realized I had the "allow creation of records via this relationship" box checked on only one side of this relationship.