Family Relationships – Design WalkthroughPosted: February 7, 2013
In “FOCUS”, the application that I spend much of my time looking after, we need to record family networks. This post explains how I’ve accomplished this. As ever, it’s included here partly for my own documentation needs, but also in the hope that a) it may be interesting to others, and that b) others may be able to tell me how to make it better.
(Note – Obviously, throughout this explanation, be careful not to confuse family relationships with Filemaker relationships. Hopefully, it’s clear which is which.)
People in FOCUS can be linked to each other to reflect family relationships. The Relationships (or REL) table is the join table which describes the relationship. For most relationships, there are 2 REL records, i.e. one for each side of the relationship.
So, for example, Homer Simpson is the father of Bart Simpson, so there are 2 REL records:
- Record 1 tells us that Homer is the father of Bart.
- Record 2 tells us that Bart is the son of Homer.
These are the main fields involved:
- REL__kf_PPL_ID_1 and REL__kf_PPL_ID_2 – these are the 2 foreign keys to the PPL table, i.e. the 2 people in the relationship.
- REL_1’sGender and REL_2’sGender – the gender of each. This is needed in order to control:
- The value list of potential relationships for this person – e.g. a female cannot be a father.
- The “antonym” for the relationship – e.g. once we know that Homer is Bart’s father, we need to know whether Bart is male or female to know whether Bart is Homer’s son or daughter.
- REL_1is2’s___ – i.e. the nature of the relationship from the perspective of REL__kf_PPL_ID_1. If ID_1 is Homer, this field holds “father”.
- REL_2is1’s___ – i.e. the nature of the relationship from the perspective of REL__kf_PPL_ID_2. If ID_2 is Homer, this field holds “son”.
In the complementary record, the IDs (and therefore the descriptive fields) are the other way around. This post doesn’t cover the writing of these REL records, but it’s fairly straightforward. (Maybe I’ll document that later.)
Showing related people
In the various portals etc., we use a combination of filtered portals and relationship-driven portals to show the various types of related people.
Showing all related people
To show all people related to a person, a portal displaying “PPL to REL” is the easiest way.
The PPL to REL relationship is thus: PPL__kp_ID = REL__kf_PPL_ID_1.
The extension of the relationship is “back” to PPL, to see details of the related person. The PPL to REL to PPL relationship is thus:
PPL__kp_ID = REL__kf_PPL_ID_1
REL__kf_PPL_ID_2 = PPL__kp_ID
The portal of all relationships when REL__kf_PPL_ID_1 is Homer will display (for example):
- PPL to REL to PPL::PPL_Name (i.e. “Bart”)
- PPL to REL::REL_2is1’s_____ (i.e. “son”)
Showing specific types of related people
Portal filtering is the best way to control what types of relationships are shown in a given portal. (This would not be the case if we were dealing with more than a few relationships per person, after which point portal filtering becomes rather slow.)
So, for example, if we need a portal to show the parents of Bart, we would use the portal described above, but filter it, thus:
Portals to show “children of…” etc. are similarly straightforward, along with perhaps a “catch-all” portal to show “other related people”, using a “not equals” filter, thus:
Siblings presents a special case. Although it’s possible to specify that “1 is 2’s bother/sister”, we shouldn’t have to, when it can be deduced (i.e. if 1 and 2 have one or both parents in common).
So for this we can drive the portal with a relationship, thus:
PPL to REL to PPL to REL to PPL, where related people at the right end of the squid leg are the siblings of the person at the left end.
I’ve used match fields – so the first PPL to REL relationship is thus:
(where PPL_MatchTypeParent holds “mother[cr]father”). So, using this relationship, PPL to REL to PPL gives the parents of the person on the left.
For the second “step” of the relationship, i.e. PPL to REL to PPL to REL, we use another match field, PPL_MatchTypeChild, which holds “son[cr]daughter”. The relationship is set up thus:
So, where the first step of the relationship gives us “parents of Bart”, the second step gives us “children of the parents of Bart”, i.e. Lisa, Maggie, plus, of course, Bart himself.
All that remains is to show the “siblings” portal, based on PPL to REL to PPL to REL, with, crucially, a filter to exclude the person at the start of the chain (Bart), thus: