6 Replies Latest reply on Nov 1, 2014 9:01 PM by StevenNardi

    Help with using FM to show relationships between people

    StevenNardi

      Title

      Help with using FM to show relationships between people

      Post

      I use Filemaker to record research in the humanities. At core my database keeps track of writers and books, so I have a table of works and a table of authors and then a join table to associate them. It would also be useful, however, to be able to take notes on the relationships between people. To this end I added a second instance of the people table and a join table to show relationships. 

      However, when I add a relationship, that relationship only shows up in one record. So if I’m on my Ernest Hemingway page and I add a record that he married Martha Gelhorn, it only shows up on Hemingway’s page, not Gelhorns. Further, since many relationships are directional (father --> son for example), it would be very useful if Filemaker could understand if I added a daughter to Audrey Hepburn’s record, that would show up as a mother on Katherine Hepburn’s entry. 

      Does anyone have any ideas as to how to structure this (assuming it is possible)?

        • 1. Re: Help with using FM to show relationships between people
          philmodjunk

          You can use a script to "back link" your relationship by creating a second record with the values in the match fields swapped.

          People1::PeopleID = Relationships::PeopleID1
          People2::PeopleID = Relationsihps::PeopleID2

          When you create a record in Relationships, a script could make a second record in Relationships and swap the value in PeopleID1 with the value in PeopleID2.

          "knowing" that a daughter relationship requires a mother relationship for the "back link" is also possible, I suppose, but it won't be simple to set as there are a great many different such pairs of reciprocal relationships that are possible and some are gender specific while others are not. I've actually kicked this idea around a bit as an example in a not yet released issue of Adventures In FileMaking, but decided it was two complex as the initial form of a "many to many self join" relationship so I haven't found the time to finish working out the details needed to make this work.

          • 2. Re: Help with using FM to show relationships between people
            StevenNardi

            Phil, thank you. I hadn't realized a script could do that. It solves the first problem. 

            (Thank you also for the Adventures in FileMaking tutorials. The one on conditional value lists was just what the doctor ordered last week.)

            As for the directionality of relationships, it's true that there are many possibilities, but they all divide into either a relationship between equals or a senior/junior relationship--so three possible states. Would it be possible to write the script for the back link in such a way as to indicate which state the first record is (and so which of those three states the automatically created record should be). If that could be done I think I could figure out the rest.

            • 3. Re: Help with using FM to show relationships between people
              philmodjunk

              It's possible, but you'll need to set something up that lists all the possible pairings in a table so that by specifying "Daughter" in in the original relationship allows you to look up "Mother" from a field in the same table in order to assign it to the relationships table.

              The list would look something like this but with more records: ( | separates two fields in the same record)

              Mother  | Daughter
              Daughter | Mother
              Father | Son
              Son | Father

              But note that pairings of Father | Daughter and Mother | Son are also possible and thus you also need to consider gender as well as the original relationship in order to determine which "reciprocal relationship label" to use.

              • 4. Re: Help with using FM to show relationships between people
                StevenNardi

                Since I'm the only one using the database, I don't mind something less precise. I could simplify by using gender neutral terms--"spouse" and "sibling" instead of husband/wife or brother/sister. I think I can get by with just the following terms: 

                spouse | spouse
                lover | lover
                partner | partner
                sibling | sibling
                parent | child
                ancestor | descendent
                mentor | men-tee
                teacher | student

                That's rough, but I think it would cover everything that I need. 

                Let me recapitulate your suggestion to make sure I understand it: I would make a table of pairings, which each line above a record. And then in the script, I would have Filemaker search for the value chosen for person one (which would be Field1) and then input in person two's record the reciprocal relationship (Field2). That means for hierarchical relationships I would need actually two records each, for example, both

                mentor | men-tee
                mentee | mentor

                Right? I think I can do that.

                Phil, thank you so much. This really helps.  

                • 5. Re: Help with using FM to show relationships between people
                  philmodjunk

                  You are on the right track but you don't actually need to script a find to get to the correct record. Note that a relationship from the join table to this new table that matches by relationship name can allow you to use the relationship in order to access the reciprocal name. Match by field one and then field two returns the reciprocal name. (In my project, before getting pulled away, I was going to match by gender and relationship name fields to access one specific reciprocal relationship name.)

                  • 6. Re: Help with using FM to show relationships between people
                    StevenNardi

                    I got it working. Of course, it took a few hours of fiddling to get everything working correctly, and I realized that I needed to create another table for the notes so that they appeared on both records, but the end result is exactly what I was hoping for. 

                    Thanks a million Phil.