11 Replies Latest reply on Aug 1, 2012 9:13 AM by philmodjunk

    Relationship on same table? For a school - linking parents to students

    AmyKraunz

      Title

      Relationship on same table? For a school - linking parents to students

      Post

      I'm fairly new to FMP and am developing a database for my child's school. Thanks to one user I've learned a LOT and compeleted started from scratch and made my whole database relational. It's working great, HOWEVER I'm having one small hiccup that is driving me crazy. So..... among a few tables I have I have a student info table (this is my "parent" file in database terms), I have a parent info table. the tables are related, so if you re on a student page you can see who their parent(s) are. On the parent page you can see who their student(s) are along with connecting via a relationship page to their volunteer hours and to a separate donation table. What I would REALLY like though is to be able to connect to parents that are married/partners, but they are on the same table. For example, if John Smith #0001 is married to Mary Smith #00002 I'd like to be able to put a field/button/portal on John's page that says partner/spouse ID, I can input that ID and it connects to Mary's page. Is there anyway to do this. I'm driving myself crazy trying to make it work. Thanks for your help!

        • 1. Re: Relationship on same table? For a school - linking parents to students
          philmodjunk

          You can, but given divorce/separation/remarriage issues it may not be the best option. You may find that you need to link a parent to more than on studetn, more than one parent and a student record may need to link to any number of "parents".

          First, to do the specific thing you requested.

          In Manage | Database | relationships, make a new table occurrence of Parent Info table by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Now you can link this new reference to the Parent Info table to the original table Parent Info table occurrence by the fields that you describe.

          Now for the issue that I raised:

          What I'm describing is really a many to many relationship. Each family can have many members and a given individual can be a member of more than one family.

          The solution is to use a Join table:

          Contacts-----<Contact_Family>------Families

          Contacts::ContactID = Contact_Family::ContactID
          Families::FamilyID = Contact_Family::FamilyID

          In place of "contacts", you can use your two tables for student and parent info or you can actually combine the records from both of these tables into the contacts table.

          • 2. Re: Relationship on same table? For a school - linking parents to students
            AmyKraunz

            Thank you for your response, but I am having trouble with this (the first answer). I was able to duplicate my table (create a self joining table) but it doesn't connect correctly. For simplicity sake let's say in my table I have: Parent ID (serieal #), Student ID, Parent First Name, Parent Last Name. So on my layout was where I was planning on having a field "If have spouse, here is spouse Parent ID#" making that Parent ID # a button and would bring to 2nd parents page.

            When I make the self joining table I don't know what to connect. I tried adding a field called SpouseID, but when I connected that to the parent ID and put that field on Parent #1's page it just listed parent #1's ID /name.

            What am I doing wrong?

            • 3. Re: Relationship on same table? For a school - linking parents to students
              philmodjunk

              Amy, I hesitate to respond in this thread as you already have several knowledgeable folks helping you out with the same issue in a different thread. What I suggest could differ from theirs and add to your confusion.

              You would need this relationship:

              ParentTable::SpouseID = ParentTable 2::ParentID

              If your layout is based on parentTable and you enter a ParentID into the ParentTable::SpouseID field, any fields that you add to your layout from ParentTable 2, will show data from the ParentTable Record with that ID number. The typical method for selecting your SpouseID field here is to set up a value list of ParenID's for field 1 and parent names from field 2.

              • 4. Re: Relationship on same table? For a school - linking parents to students
                AmyKraunz

                thanks phil

                is there anyway you can send me a screen grab of how correctly to do the join table for the family? keep trying it, but not working

                • 5. Re: Relationship on same table? For a school - linking parents to students
                  philmodjunk

                  Parents-----<Contact_Family>------Families

                  Parents::ParentID = Contact_Family::ParentID


                  Families::FamilyID = Contact_Family::FamilyID

                  • 6. Re: Relationship on same table? For a school - linking parents to students
                    AmyKraunz

                    where do the student IDs fit in = basis of the whole file? sorry - so new at this, and better when i see a picture of the relationships graph, witht his am not sure how to show multiple siblings/kids, how that connects

                    • 7. Re: Relationship on same table? For a school - linking parents to students
                      philmodjunk

                      You have two options. One is to use the same table for parents and students.In which case, the above relationship works. That was my original suggestion--that you merge parents and students into a single table of contacts.

                      The other is to add this relationship:

                      Students::StudentID = Contact_Family::StudentID

                      • 8. Re: Relationship on same table? For a school - linking parents to students
                        AmyKraunz

                        can't merge the students and parents into same table as too many different variables that for each, need to keep separate. 

                        So, when you say add: Students::StudentID = Contact_Family::StudentID

                        the do i also need both of these?:

                        Parents::ParentID = Contact_Family::ParentID

                        and
                        Families::FamilyID = Contact_Family::FamilyID

                        and am i just using the main serial number ID i created for each Student and Parent and linking them to this new contact family serial number

                        any other fields/new table?

                        • 9. Re: Relationship on same table? For a school - linking parents to students
                          philmodjunk

                          Yes, but you might find that merging the two tables is actually quite possible. Sometimes this structure is used:

                          Contacts::ContactID = StudentDetails::ContactID

                          Contacts::ContactID = ParentDetails::ContactID

                          This provides a single unified table of names and basic contact info such as a cell phone number, but with related tables that record additional details specific to students and parents.

                          • 10. Re: Relationship on same table? For a school - linking parents to students
                            AmyKraunz

                            I'm not sure if I did what you had above, but I managed to create a Family Join Table. It works when I go to the table and put in the family serial number for the correct family, BUT I just created a new student record and a new parent record, which generated new Student IDs and Parents IDs respectively, but it didn't generate a Family ID and the only way to do that looks like to step into the Family Table when I really don't want to leave my main page. What am I doing wrong?

                            • 11. Re: Relationship on same table? For a school - linking parents to students
                              philmodjunk

                              Use a portal to the join table from either a "student" or "parent" based layout.

                              Take a look at this demo file for some ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                              If you are using FileMaker 12, it will convert without issue into .fmp12 format if you use Open from the File Menu to open it.

                              It shows a number of ways for working with many to many relationships.