5 Replies Latest reply on Apr 6, 2009 11:51 AM by philmodjunk

    Relationship

    okten

      Title

      Relationship

      Post

      Hello,

       

      I am a new user of Filemaker. I am the director of a graduate program and I am trying to use Filemaker to keep track of our graduate students.

       

      I have a table of students - each student record contains several fields some of which are devoted for the student's graduate committee members. The committee members are entered as different fields as: Member 1, Member 2,..,Member 4.  These members are faculty.

       

      I have a second table that has the faculty data. Each record has the faculty name, department, etc.

       

      How can I set up a relationship and build a portal so that for each faculty I can see a list of students for whom the faculty served as a graduate committee member.

       

      For example, if Faculty John Doe was Member 1 in the committee of student X, and Member 3 in the committee of student Y, then I would like to be able to create a layout where I will have Faculty John Doe, and the students X and Y listed as students John Doe served as a member.

       

      I will very much appreciate any help!

       

      Thanks.

      G. Okten 

        • 1. Re: Relationship
          comment_1
            

          Your current structure does not enable producing such report (at least not easily). You need a third table joining students to faculty, where each comittee membership is a record:

           

          Students -< Memberships >- Faculty

           

          A record in Memberships needs fields for StudentID (to link  to the Students table), FacultyID (to link to the Faculty table) and fields that describe the membership itself (such as role, date, etc.).

           

          Once you have that in place, you can produce your report from the Memberships table, sub-summarized by faculty.

          • 2. Re: Relationship
            comment_1
               Upon reread, I see that you asked for a portal, not a report. Nevertheless, the answer is the same. Although technically you could combine all the fields for faculty in a student  record into one, and use that to establish a relationship to the Faculty table, it wouldn't solve the basic issue.
            • 3. Re: Relationship
              okten
                

              Thanks for your help. I could not get it done by a third table, but I ended up finding a solution using another tip: I created a new committee field, and added all committee members in this field separated by carriage return. Then I created a relationship between this new field (which belongs to the Students table) and faculty name (from the Faculty table). Then I created a portal from Faculty table for the new field. I worked like a charm!

               

              Thanks again for the advice. 

              • 4. Re: Relationship
                comment_1
                  

                Well, as I said, that will work in a limited fashion. But the proper implementation of a many-to-many relationship is through a join table.

                You can find a basic demo of the method here.

                 

                Note also that using names as matchfields in relationships is not good practice: you should assign a serial ID to each record and use only that as the matchfield.

                • 5. Re: Relationship
                  philmodjunk
                    

                  comment wrote:

                  Well, as I said, that will work in a limited fashion. But the proper implementation of a many-to-many relationship is through a join table.

                  You can find a basic demo of the method here.

                   

                  Note also that using names as matchfields in relationships is not good practice: you should assign a serial ID to each record and use only that as the matchfield.


                   

                  I'll politely disagree that the "proper implementation of a many-to-many relationship is through a join table." I find that a multi-value key often eliminates the need for a join table in Filemaker Pro solutions. It's not the best choice in every situation and properly designed join tables do work quite well.

                   

                  However, I completely agree with his advice to use serial ID numbers instead of names. Otherwise, if Jane Doe gets married and changes her name to Jane Smith, you'll have find all your fields that contain Jane Doe and change them to Jane Smith.