3 Replies Latest reply on Mar 19, 2009 11:53 AM by laneystewart

    many to many relationships



      many to many relationships


      ok so I am back... just when i thought that I knew enough.....  I have a many to many relationship with students and colleges.  I have a student table and a college table with a ZID field that auto-enters a serial number when a record is created.  I have created a join table with a studentID_FK and CollegeID_Fk.  How do I get this table to make a new record when a new record for the student is created and get the studentID_fk to populate with the same number as the ZID????  The colleges in the college table will remain the same. I am looking to set up a portal with a list of the colleges in a layout drawn off of student.  I eventually want to be able to filter the list of schools in the portal as described in the special edition Using Filemaker 9 book, chapter 17.  I can get the entire list of colleges to appear in the portal by choosing the [X] in the relationship, but I do not want to have this long of a list to scroll thru.  Any help would be appreciated.  Thanks in advance!




        • 1. Re: many to many relationships
             You'll almost certainly need to write a script. I don't have the book, so must ask: By what criteria to you plan to filter the list so users don't have to scroll through the entire list?
          • 2. Re: many to many relationships

            The book describes using a global field to have text entered to filter by.  I plan on using the college name.  It describes how to set up a high and low field and use the greater than and less than options in the relationship.  I get this part I believe but just could not make the fields populate thru a look-up or calculation.  After sending the post i realized that I can have a new record created in the join table thru the relationship when a new record is created in student.  Now I have to figure out how to get the field to populate the same as the student table.  I have also set up a constant JoinID of 1.5 so that the portal populates, but it does have all 5000+ records.  Getting there I guess........:womanmad:


            • 3. Re: many to many relationships
                 OK so I got it......... the filter works for one word at a time....... YEAH!!!!  I set a constant JoinID for the college table of 1.5 and now with the MatchHigh and MatchLow, I can filter with a range in between to get a shortened list!!!!! YEAH!!!!!I have added a select button to my portal that will let my people choose a college and have it added to the record for the student.:smileyvery-happy: