3 Replies Latest reply on Jan 31, 2012 7:14 AM by comment

    Many to Many Relationship


      Hi, Newbie here. I can't get my head around many to many relationship. Using FMP 11. I have a list of contacts that I visit and give training to. Sometimes one contact is present sometimes several. The lesson material is tracked to be sure all lessons have been received by all contacts. I think I need a join table to allow for multiple lessons to be taught on one visit to multiple contacts. I think my entities are Contacts, Visits and Lessons. What should my relationships look like in there simplest form?

      I have tables for Contacts, Visits & Lessons. Trying to be able to layout a visit screen where I can choose contacts who attended, and what lessons were taught. Then be able to generate individual records of instruction.

      Any help is appreciated. Thanks

        • 1. Re: Many to Many Relationship

          You are describing two many-to-many relationships, one between  Contacts and Visits and another between Visits and Lessons. Properly, this should be resolved by two join tables, e.g.:


          Contacts -< Attendance >- Visits -< LessonsGiven >- Lessons


          In some circumstances you could get away with using a checkbox field in the Visits table instead of a join table.

          1 of 1 people found this helpful
          • 2. Re: Many to Many Relationship

            Thank you for pointing me in the right direction.  I will endeavor to construct the two join table solution you outline above. 

            The Layout I would like to have, where a visit could be described and attendees and lessons selected, is more difficult than I imagined.  Which table would provide the context for such a layout?  Visits? Would there be two portals?

            • 3. Re: Many to Many Relationship

              Yes, a layout of Visits with two portals to the two join tables seems like the most suitable choice for the user interface.

              1 of 1 people found this helpful