11 Replies Latest reply on Aug 18, 2012 4:40 AM by JamesPickett

    mapping family relationships, automating the join table

    JamesPickett

      Title

      mapping family relationships, automating the join table

      Post

      I am currently using a Filemaker Pro database I designed to assist me with keeping track of a large number of individuals and relationships between them for hisorical research.

      Based on very helpful feedback from a previous forum posting, I currently map familial relationships using a join table. As it currently stands, it is necessary to make two entries for each relationship.  For instance, if I relate "Tom" to "Fred" and designate the relationship "father", I must also add a second entry from Fred to Tom designated "son."  

      For a while this system suited my needs, but as my database size has grown it has become extremely cumbersome to manually map these relationships.  For instance, if I have a father, a son, a mother, and a grandson, if I add a granddaughter to add five relationships for the granddaughter and then go back to the other entries and add the reverse of that relationship as well.  Is there a way to streamline / automate this process?  I want the database to work so that if I add a father-son relationship, it will automatically add the reciprocal (son-father) relationship, and - ideally - all the associated relationships as well (e.g. if I have data for a father-daughter relationship, it would add an entry to the join table to designate the son and daughter as "siblings.)

      Ideas and advice are most appreciated.  This is a long-term dissertaiton project, so I would love to find a workable solution for this dilemma.

       

        • 1. Re: mapping family relationships, automating the join table
          philmodjunk

          I can get you started on the first part: "back linking" the new entry in the join table so that linking a son to a father also creates a matching link from father to son:

          If you think about the two join table records, they are exactly the same except the two key fields in the table have their values swapped.

          So this code would create the second link:

          Freeze Window
          Go To Related Records [Show only related records ; table: JoinTable ; layout: "jointable" (JoinTable)]
          Duplicate Record
          Set Variable [$Temp ; value: JoinTable::KeyField1 ]
          Set Field [JoinTable::KeyField1 ; JoinTable::KeyField2 ]
          Set Field [JoinTable::KeyField2 ; $Temp ]
          Set Field [JoinTable::Relationship ; Case ( JoinTable::Relationship = "Son" ; "Father" ;
                                                                                 JoinTable::Relationship = "Father" ; "Son" ; // and so forth...]

          The above script could be performed by clicking a button in the join table's portal row. It must have the focus on the correct portal row for it to run correctly.

          • 2. Re: mapping family relationships, automating the join table
            JamesPickett

            PhilMod,

            Fantastic.  I do not have any programming / scripting experience, but let me see if I understand correctly how I should implement this.  My join table is named "Familial Relationships"; my first keyfield is called "Individual" and my second is called "Related To".  Did I specify the coding below correctly?

             

            Freeze Window
            Go To Related Records [Show only related records ; table: Familial Relationships ; layout: "Familial Relationships" (Familial Relationships)]
            Duplicate Record
            Set Variable [$Temp ; value: Familial Relationships::Individual ]
            Set Field [Familial Relationships::Individual ; JoinTable::Related To ]
            Set Field [Familial Relationships::Related To ; $Temp ]
            Set Field [Familial Relationships::Relationship ; Case ( Familial Relationships::Relationship = "son" ; "father")  Case Familial Relationships::Relationship = "father" ; "son" ;   Case Familial Relationships::Relationship = "grandfather" ; "grandson" ;   Case Familial Relationships::Relationship = "grandson" ; "grandfather" ;  Case Familial Relationships::Relationship = "sibling" ; "sibling" ;  Case Familial Relationships::Relationship = "uncle" ; "nephew" ;   Case Familial Relationships::Relationship = "nephew" ; "uncle" ;   Case Familial Relationships::Relationship = "ancestor" ; "descendant" ;   Case Familial Relationships::Relationship = "descendant" ; "ancestor" ; ]   

             

            Then I will go into the join table, create a new field which I will turn into a button via the layout menu, then have the button run this script when clicked, and then add that button to the portal view.  Is that right?   

             

            As always, thank you so much for your kind support.

             

                                            

            • 3. Re: mapping family relationships, automating the join table
              philmodjunk

              That's what I originally had in mind but what you have for case functions is incorrect syntax. It won't work and you'll get error messages just clicking OK to close the dialog.

              IT should read:

              Case ( Familial Relationships::Relationship = "son" ; "father" ;
                         Familial Relationships::Relationship = "father" ; "son" ; 
                         Familial Relationships::Relationship = "grandfather" ; "grandson" ;  
                         Familial Relationships::Relationship = "grandson" ; "grandfather" ; 
                         Familial Relationships::Relationship = "sibling" ; "sibling" ; 
                         Familial Relationships::Relationship = "uncle" ; "nephew" ;  
                         Familial Relationships::Relationship = "nephew" ; "uncle" ;  
                         Familial Relationships::Relationship = "ancestor" ; "descendant" ;  
                         Familial Relationships::Relationship = "descendant" ; "ancestor"

                       )

              Coming up with a script that runs through all the relationship records in the portal, generating additional relationships will need to be more sophisticated.

              Consider the instance where you add a new "son" relationship. For just the new record, you know that you need to create a "back link" for "Father", but for any siblings listed in the same portal, "son" or "daughter" would indicate that a "sibling" link is being created. Thus, you'd need to capture the Relationship name of the newly added link in a variable before starting up such a script.

              • 4. Re: mapping family relationships, automating the join table
                JamesPickett

                PhilMod,

                Thanks!  Is it possible to set up  the script such that it is triggered by designating the relationship type rather than by clicking a button?  Seems like that would eliminate a step.  The drawback, I suppose, is that if I change the relationship type it will add a duplicate record, unless there is some way to account for that.

                Your initial thoughts on the second part of my question are also appreciated, though I will make sure I get this first script working the way I want it to before I try something more sophisticated.

                • 5. Re: mapping family relationships, automating the join table
                  philmodjunk

                  A script trigger such as OnObjectSave on the relationship field can do that. OnObjectEnter can be used to capture the original value in a variable and then your script can check the original value to determine whether to check for an existing record to modify or delete before adding a new "back link" record.

                  • 6. Re: mapping family relationships, automating the join table
                    JamesPickett

                    PhilMod,

                    I saved the script copied below and set it to fire "OnObjectSave" from the portal in my Individuals layout.  However, I do not have it working quite yet for reasons I do not quite understand.  When I designate "Type" and thereby fire the script it jumps to the join table layout and filters down to only the entry just created by the script.  The original entry (e.g. "son") is not saved, but the script-fired inverse entry (e.g. "father") is saved, though erroneously.  (For example, when I designate individual X as "son" of individaul Y, what I get is an entry listing individual Y as his own son.)

                    Freeze Window
                    Go to Related Record [Show only related records; From table: "Familial Relationships"; Using layouts: "Familial Relationships" (Familial Relationships)]
                    Set Variable [$Temp; Value: Familial Relationships::Individual]
                    Set Field: [Familial Relationships::Individual; Familial Relationships::Related To]
                    Set Field: [Familial Relationships::Type; Case ( Familial Relationships::Relationship = "son" ; "father" ;
                               Familial Relationships::Type = "father" ; "son" ; 
                               Familial Relationships::Type = "grandfather" ; "grandson" ;  
                               Familial Relationships::Type = "grandson" ; "grandfather" ; 
                               Familial Relationships::Type = "sibling" ; "sibling" ; 
                               Familial Relationships::Type = "uncle" ; "nephew" ;  
                               Familial Relationships::Type = "nephew" ; "uncle" ;  
                               Familial Relationships::Type = "ancestor" ; "descendant" ;  
                               Familial Relationships::Type = "descendant" ; "ancestor"

                             )]

                    Also, how do I get ObjectEnter to check for duplicates as you suggest?

                    Thanks again so much for your continued support and patience.

                    • 7. Re: mapping family relationships, automating the join table
                      JamesPickett

                      PhilMod,

                      I have been experimenting, reading, and trouble shooting, but still haven't gotten it to work.  Perhaps I am missing a step related to "Set Variable," since the current code never actually uses $Temp after storing it?  Also, if I am not mistaken, the reason it does not save the original entry is because the code has it modifying the entry I just created instead of creating an additional one.  I.e. I manually created a new row in Familial Relationships via the portal, then the first Set Field command switches the Individual value to the Related To value - which is why he now shows up as related to himself, then the second Set Field command does what it is supposed to.

                      So perhaps I need something more like this (changes in bold):

                      Freeze Window
                      Go to Related Record [Show only related records; From table: "Familial Relationships"; Using layouts: "Familial Relationships" (Familial Relationships)]
                      Set Variable [$Temp; Value: Familial Relationships::Individual]
                      [optional / ideally - a command to make sure there isn't already an inverse record created and abort the script if there is]
                      [A command to create a new record and go to that record]
                      Set Field: [Familial Relationships::Individual; $Temp]
                      Set Field: [Familial Relationships::Type; Case ( Familial Relationships::Relationship = "son" ; "father" ;
                      Familial Relationships::Type = "father" ; "son" ;
                      Familial Relationships::Type = "grandfather" ; "grandson" ;
                      Familial Relationships::Type = "grandson" ; "grandfather" ;
                      Familial Relationships::Type = "sibling" ; "sibling" ;
                      Familial Relationships::Type = "uncle" ; "nephew" ;
                      Familial Relationships::Type = "nephew" ; "uncle" ;
                      Familial Relationships::Type = "ancestor" ; "descendant" ;
                      Familial Relationships::Type = "descendant" ; "ancestor"

                      )]
                      [A command to get me back to my original screen - perhaps "Go to Record" using $Temp]

                      Am I on the right track?  Any guidance is most appreciated.
                       
                      Many thanks in advance.
                      • 8. Re: mapping family relationships, automating the join table
                        philmodjunk

                        Sorry not to get back quicker...

                        Not the line in bold face missing from your latest efforts:

                        Freeze Window
                        Go To Related Records [Show only related records ; table: Familial Relationships ; layout: "Familial Relationships" (Familial Relationships)]
                        Duplicate Record
                        Set Variable [$Temp ; value: Familial Relationships::Individual ]
                        Set Field [Familial Relationships::Individual ; JoinTable::Related To ]
                        Set Field [Familial Relationships::Related To ; $Temp ]
                        Set Field [Familial Relationships::Relationship ; Case...

                        Note the red text where the value in $Temp is used to corrctly "back link" the new join record.

                        • 9. Re: mapping family relationships, automating the join table
                          JamesPickett

                          PhilModJunk,

                          You are a wizard - thank you so much for your help.  I have it working the way I want it to now and copied the successful code below.

                          Or at leasy almost the way I want it to work.  Currently the code fires any time FileMaker saves the contents of the "Relationship Type" field, which means if I need to change the designation from "son" to "nephew", for instance, I have a duplicate (and false) entry requiring manual deletion.  Is ther any way to account for this drawback?

                          Also, regarding cascading relationships (e.g. if I enter a relationship to make Ed John's son, and Brian is John's brother, the program automatically enter's Ed as John's nephew and John as Ed's uncle) - is your judgment that this would require more effort than it is worth?  Or is it feasible?

                          Freeze Window
                          Go to Related Record
                          Set Variable [$Temp; Value:Familial relationships::Individual]
                          Set Variable [$Tempp; Value:Familial relationships::Related To]
                          Duplicate Record
                          Set Field [Familial relationships::Individual; $Tempp]
                          Set Field [Familial relationships::Related To; $Temp]
                          Set Field [Familial relationships::Case - etc etc]
                          Go to Layout [Individual Full View (Individuals)]
                          Go to Object [Object Name: Individual ID=$Temp]
                          • 10. Re: mapping family relationships, automating the join table
                            philmodjunk

                            I can't imagine that your last script step: Go to Object actually does anything. Try removiing or disabling it and see if you get any difference in what happens when you perform this scritp.

                            (It has two problems: Incorrect Syntax and object names are set for layout objects in the inspector--they won't differ for each record.)

                            There are several ways to check for a duplicate record in such a script. Here's one method that should work:

                            First define a new text field in the Familial Relationshps table, DupCheck. Define the following auto-enter calculation for it:

                            Individual & " " & Related To

                            Clear the "do not replace existing values" check box.

                            Specify "Unique values" on the validation tab.

                            Then add this script immediately after the 2nd Set Field step:

                            Set Error Capture [on]
                            Commit Records
                            If [ Get ( LastError ) // a non zero error code will be returned if this new record duplicates an existing join table record]
                                Delete Record [no dialog]
                                Exit Script []
                            End If

                            • 11. Re: mapping family relationships, automating the join table
                              JamesPickett

                              PhilModJunk,

                              Your are correct, the Go to Object line did nothing.  I had added it originally because I don't see why jumping back to my Individuals table via "Go To Layout" would necessarily take me to the individual I started from.

                               

                              I think I misunderstood how to make this script work.  I followed your instructions (though I asusme "Commit Records" means "Commit Records / Requests []".

                              This following syntax results in the error message "the specified table could not be found" highlighting "Delete Record":

                              Get ( LastError )
                              Delete Record [no dialog]
                              Exit Script []
                               
                              This syntax results in the error message "A number, text constant, field name or "(" is expected here":
                               
                              [Get ( LastError )]
                              Delete Record [no dialog]
                              Exit Script []]
                               
                              I think I am missing the logic of what is going on here.  What am I doing wrong?
                               
                              On a related note, is there a way to cut and paste the code without having to type it out by hand for the purposes of troubleshooting on message boards like this?
                               
                              Many thanks in advance!