10 Replies Latest reply on Feb 26, 2015 8:46 PM by mikeg998

    linking family members

    mikeg998

      I'm helping set up a db that involves family members. I have created the relationships as follows:

       

      Contacts---------<FamilyContacts>-----------Families

       

      The Contacts table has the individuals (kids included) with individual level data and a pkID.

      Families has an address and pkID.

      FamilyContacts has fk for contacts and families as well as a field for role (father,mother,child) and a field for holding the family name so it can be looked up on a layout.

       

      What I am struggling with is:

       

      1)  I want to create a "look up" field that takes the last name and first names of the mother and father from the contacts table as they are created.   i.e. I add Bob Smith and his address, then I add Jane Smith and look up (Smith, Bob) in the FamilyLookUp value list. Finally when I add the kids I look up Smith and the value list is now (Smith, Bob/Jane). This way there is no confusion when linking families with the same last name.

       

      2)  I want to display all the names of the linked family members in the contacts layout. It seems like this should be a calculation list field in the FamilyContacts table but I am sort of at a loss. My preference is to have the list be Father: (name), Mother: (name) and so on but left blank if one does not exist.

       

      I'm assuming the fields for both of these will need to go in my join table but any insight or clarification would be much appreciated.

       

      Thanks.

        • 1. Re: linking family members
          bigtom

          Why do you need the lookup field? To copy the address? What happens when two families have the same last name?

           

          How deep does this go? What if the family members have different addresses or last names? For instance a married daughter. Do you move that record out to its own "family" or delete that contact all together?

           

          For simplicity I would assign a FamilyID and link the family members together with that. You could also have a table that just has Family ID, Contact ID, and possibly Priotity ID (role as you called it for the list of Father>Mother>First child>Second child. You could pull the list with ExecuteSQL and parse the result how you want in a text field or a merge field.

          • 2. Re: linking family members
            mikeg998

            Main purpose is to look up the address and be able to link families within the same household. When someone leaves the household the record will be updated with a new address (and a new family ID) It's not meant to be go much further than that. The families-contacts join table has exactly what you're describing, ContactID, FamilyID and Role. What I need to figure out is how to crate a calculation that combines the names of the mother and father into one lookup value, making it easier to find and which table this should go in.

            I'm quite new to this. Am I way off in my thinking?

            Thanks for the suggestions.

            • 3. Re: linking family members
              bigtom

              The address seems to be relating the families together. But since you contacts table is maybe not built around the address it is not too straight forward.

               

              I am not sure if you can consider an address table with family member names and roles as related records. If you think of it I terms of a "household" table instead of "address" this may make more sense.

               

              I am really trying to figure how you are assigning the proper family ID to start with but I will assume you have a process for that.

               

              If everything is in place and you just need the calculation for the names I will help with that when I get back to a computer unless someone gets to you by then. On the phone right now.

              • 4. Re: linking family members
                NickLightbody

                Here are some general comments which I hope you find useful in starting by avoiding any of the common pitfalls that can overtake a new developer.

                 

                Take a look at these pieces for some helpful starters on how to get the best out of FileMaker

                 

                Simplicity delivers performance:

                https://community.filemaker.com/thread/78220

                 

                Design principles for performance

                https://community.filemaker.com/thread/78361

                 

                SingleTableArchitecture:

                https://community.filemaker.com/thread/78458

                 

                Designing for language:

                https://community.filemaker.com/thread/78347

                 

                Designing a good Portal Filter:

                https://community.filemaker.com/thread/78666

                 

                What is happening when FileMaker Server becomes overloaded (and how to avoid it):

                https://community.filemaker.com/thread/79075

                 

                Other benefits of using a language table for localisation

                https://community.filemaker.com/message/175299

                 

                There are several means of getting information from another record to appear in the current record:

                 

                (1) Unstored calc

                (2) Stored calc

                (3) Auto enter with don't replace

                (4) Auto enter without don't replace (i.e replace) (better described as "Auto enter & update")

                (5) Lookup

                 

                Many folk use (3) to set data which will not change on record creation.

                 

                If you just uncheck the "don't replace box" option (4) - you have a rather magical ability to have the data change automatically as necessary - when the source changes - but be stored and thus indexable and automatic. In my experience this is preferable to using a lookup which seems to take longer. But there are limitations to be aware of - see the following comments.

                 

                The question then is what is your source?

                 

                There are two ways of doing this. The source can just be a record in another table but there you run into whether and when your field will in fact update. It depends and you need to create a test file to work out what works and what doesn't - there are several techniques. As a general guide my preferred approach is the simplest and most reliable which - on balance - is to use an unstored calc in the current record as the source for the type 4 auto enter - this is foolproof and always works as expected. So if for example the father's data is amended the children's records will all update just like that but the children's records can still be indexed on their auto entered + replaced father's data.

                 

                Cheers, Nick

                • 5. Re: linking family members
                  bigtom

                  Nick has plenty of experience to draw from. Please take the time to read the resources he has mentioned. To sum up what nick has said, the design matters! A bad design decision will haunt you for a long time.

                   

                  #2 is easy with a simple relationship and a filtered and sorted portal on the layout.

                   

                  #1 is a little more difficult.

                   

                  For the calc I would suggest GetNthRecord(Contacts::FirstName, 1) & "-" & GetNthRecord(Contacts::FirstName, 2) if the relationship is correct and you have a sort order on the relationship by role numerically ascending. That should get you going in the right direction.

                   

                  If you are feeling brave you may consider ExecuteSQL if you are familiar with it. It will allow you to grab data without regard to the relationship. Done properly is can be fast. Some people do this with only one table and 3 fields, an ID or UUID, data type, and data value. You might need a couple more fields for the family relation data. I know people use this for very easily doing value lists and that sounds like what you are looking for. Take a look around about it.

                  • 6. Re: linking family members
                    mikeg998

                    Thanks for the resources. I'll have a good read through them and see if I can get a better handle on where I'm going.

                    • 7. Re: linking family members
                      NickLightbody

                      My pleasure Mike, please click the like button if you find this useful!

                      Cheers, Nick

                      • 8. Re: linking family members
                        davehob

                        Hello Mike,

                         

                        I also have an application where I need to keep track of families, and early on I realised that using surnames or addresses (or even Family IDs)  to keep track of families was increasingly flawed, certainly in this area of the UK anyway, so I gave up the notion of "Family" as an entity in the database.  I just use a "Relationships" table to link people, and a person's "Family" is everybody to whom he or she is linked in this way. 

                         

                        I wrote a blog post a while ago about this - Family Relationships – Design Walkthrough | A Filemaker Miscellany, which may (or may not!) be interesting.  One of the advantages of this approach is that you can define non-family relationships - e.g. "Childminder<>Minded child", "Neighbour<>neighbour" etc., which makes the whole thing more flexible.  And it's much less prone to errors through reliance on non-unique data, etc.

                         

                        Dave.

                        • 9. Re: linking family members
                          Benjamin Fehr

                          First thing I thought when reading this is how to narrow this down to less foreign-keys.

                          - _fk_spouse

                          - fk_children (repeating field?!)

                           

                          All it needs could be built on relationships. That's where you end in something like a babushka principe about how to "fold down" relationships. You'll need to get relationship of relationships to get grandchildren, for example.

                          Actually, all relations are there but they wouldn't resolve themselves like a recursion.

                          At this point I get the same spooky feeling like when setting 2 mirrors in opposition and set my had in-between

                          • 10. Re: linking family members
                            mikeg998

                            Thanks for all the suggestions. I'll try to dig through the reading and do some thinking on the design over the weekend.