5 Replies Latest reply on Apr 20, 2010 10:14 AM by jhed

    School records- siblings linked through family?

    jhed

      Title

      School records- siblings linked through family?

      Post

      Hello, again!

       

      I feel a little bad for relying so heavily on this forum, but as no one's complained yet, I will continue to ask my questions as the help I have been getting here is invaluable (and greatly appreciated).  Hopefully after I'm done with this project, my skills will have advanced to the point where I can pass on the goodness...

       

      Using FileMaker 10.0v3

      MacBook Pro, OS X 10.4.11

       

      Now to the (latest) point.

       

      I've been building an alumni/development database for a post-secondary educational program.

       

      What I'm trying to do is set it up in such a way that when I link a new student's record to an existing family the new student's record becomes linked to the existing one and shows up in their record (via a portal) as a sibling.

       

      Here is a specific example:

       

      New record (Alumni Layout/Alumni Table):

      AlumniID (auto-enter serial)

      AlumniFirst: A.

      AlumniLast: Smith

       

      (No family record exists for this alumnus yet, so I go to the tab control that I have set up in the Alumni layout with fields from the related Families table and enter the parent info.)

       

      Alumni Layout/Families Table:

      Family1ID (linked by script upon entry of family data)

      P1aFirst: J.

      P1aLast: Smith

      etc.

       

      As I said, this data is automatically linked to the alumnus record via a script I wrote that fills in the Alumni::Family1ID field with the auto-created serial from Families::FamID, thus linking the two records.

       

      Now, when I create yet another Alumni record:

       

      AlumniFirst: P.

      AlumniLast: Smith

       

      I have, with the help of this forum, created a script linked to a button that will search the family records for matching families.  Once I've linked this alumni record to J. Smith's family, I want that to automatically link the two students.

       

      I've already created another join table in which I have the following fields:

       

      (Siblings)

      RecordID

      AlumniID

      SiblingID

      SiblingFirstLast (I used a calculation field to concatenate the AlumniFirst and AlumniLast fields into this one.)

       

      Am I even on the right track here?

        • 1. Re: School records- siblings linked through family?
          fitch

          I don't think you want to store "SiblingID" as this will be inflexible and cumbersome with large families.

           

          Add another table occurrence (TO) of student, and link it to the existing student TO with two criteria:

          FamilyID = FamilyID

          StudentID <> StudentID

           

          You can then show related students from the same family in a portal on a student layout.

          • 2. Re: School records- siblings linked through family?
            jhed

            I'm not sure if I've executed this precisely the right way...  I've tried this a number of ways and I'm not sure if I'm duplicating your suggestion just right.

             

            When I link the two TO's with the following relationship:

             

            Fam1ID = Fam1ID

            AlumniID = AlumniID

             

            The portal will show the student's own record and no others (I created a test record linked to the same family, but it only shows itself as well).

             

            Using this relationship:

             

            Fam1ID = Fam1ID

            AlumniID < AlumniID

            AlumniID > AlumniID

             

            I need to add not one, but two TO's to the graph, and this displays no records in the portal.

             

            I've also tried:

             

            Fam1ID = Fam1ID

            AlumniID x AlumniID

             

            And that showed no records either.

             

            I'm actually using a join table to link the Alumni and Family records, so the relationship between Alumni and Families works like this currently:

             

            Alumni::AlumniID = join.Families::fk.AlumID

                                           join.Families::fk.FamilyID = Family1::FamID

             

            Am I still doing something wrong here?

             

             

            • 3. Re: School records- siblings linked through family?
              fitch

              The second predicate of the relationship should read "not equal" which may look like an equal sign with a slash through it (≠) or as a less-than and greater-than sign together. Sorry for the confusion.

              • 4. Re: School records- siblings linked through family?
                jhed

                Once again, the forums come to my rescue!

                 

                Thanks for clarifiying... it works perfectly now.

                • 5. Re: School records- siblings linked through family?
                  jhed

                  I've just discovered one more little hiccup.

                   

                  The sibling relationship that I had defined as:

                   

                           Alumni::Family1ID = sibling.Alumni::Family1ID

                  AND  Alumni::AlumniID ≠ sibling.Alumni::AlumniID

                   

                  Works great...  As long as Alumni #1's Family 1 is Alumni #2's Family 1 as well.

                   

                  But, what if Alumni #1's Family 1 is Alumni #2's Family 2?

                   

                  Ideally, I would be able to create the following relationship:

                   

                            Alumni::Family1ID = sibling.Alumni::Family1ID

                  OR    Alumni::Family1ID = sibling.Alumni::Family2ID

                  OR    Alumni::Family1ID = sibling.Alumni::Family3ID

                  OR    Alumni::Family1ID = sibling.Alumni::Family4ID

                  OR    Alumni::Family2ID = sibling.Alumni::Family1ID

                  etc.

                   

                  What is the best way to achieve this relationship?

                   

                  EDIT TO ADD:

                   

                  Nevermind!  I answered my own question, but here it is in case anyone else has a similar problem:

                   

                  Instead of matching Family1 = Family1, I created a new calcultation field in the siblings.Alumni TO of the Alumni table:

                   

                  SiblingFamilyIDMatch =

                  Fam1ID & ¶ & Fam2ID & ¶ & Fam3ID & ¶ & Fam4ID

                   

                  And matched it to fk.AlumID in my join table.

                   

                  Now it works!  Sorry for jumping in without thinking it through!