7 Replies Latest reply on Oct 2, 2014 6:37 AM by gjbdjc

    Many to Many Relationship



      Many to Many Relationship


      I found an old tutorial on Many to Many relationships and thought I'd give it a try.

      As expected, I can't get it to work.  So I'm looking for help.

      The database has three tables 9that are relevant)

      Students - Relationships - Seminars

      Students and Relationships are related by Student# and Students is allowed to add and delete records in Relationships

      Relationships and Seminars are related by Seminar# and Seminars is allowed to add and delete records in Relationships... Standard Many to Many setup

      I have a layout associated to Seminars, with a portal showing records from Students

      The portal has three fields, Student# from Relationships, First and Last Name from Students.

      Problem: I cannot see or select any of the fields in the portal????? 


        • 1. Re: Many to Many Relationship

          Another diagram

          • 2. Re: Many to Many Relationship

            Make the portal a portal to Relationships instead of students. You can add fields from students to the portal row to show data on each student signed up for that Seminar. Make the Relationships::Student# field a value list equipped field for selecting a student in order to sign them up for the seminar.

            A button and script could be used to create a new record in students and then a new record in Relationships that links that new record to the current seminar.

            Caulkins Consulting, Home of Adventures In FileMaking

            Note: Adventures in FileMaking #3 will cover many to many relationships, but it's not ready for release yet...

            • 3. Re: Many to Many Relationship

              Your almost there... The idea behind the many to many relationship is that you would create records in your Relationships Table.  This is how you get the many to many...  

              In other words..  If you want many students to many seminars and many seminars to many students... I would have all this in your middle TO called relationships...  You would create all your records here...  They would just reference your students and seminars TO's...

              • 4. Re: Many to Many Relationship

                Sorry... for the late response.. Phil.. I must have been typing while you were responding too.. :)

                • 5. Re: Many to Many Relationship

                  I made the recommended change.  The portal now shows records from Relationships ( the table is actually called Registrations, sorry).

                  Anyway, I still cannot see the fields.  If I enter the information manually it shows up and works for that ONE portal record.  However I cannot add another record???

                  • 6. Re: Many to Many Relationship

                    Your latest image does not match your original post and I cannot tell which table you are using as the "join" table that we are discussing here.

                    If you have enabled "allow creation of records via this relationships" for Registrations, then you can create new records in Registrations by entering/selecting data in a field from the Registrations table. You will not be able to see or modify data from Students until you a) create a new portal record by entering data into a field from Reservations and b) link that record to a specific record in Students.

                    If you set up the Registrations::StudentID field as a drop down list for selecting a student, selecting a value in this field in the empty "add row" of the portal does both a) and b) in a single operation and data from Students should then appear in any field from students that you may have put in the portal row.

                    • 7. Re: Many to Many Relationship

                      Sorry about the diagram mix-up. Anyway, Originally I downloaded an old tutorial for FM7, which my FM13 converted.  I rebuilt the entire databas3e in FM13 and it works flawlessly.  Thanks for the help.