5 Replies Latest reply on Jan 7, 2011 5:42 PM by InvectusIlumni

    Advice on Related records best practice

    InvectusIlumni

      Title

      Advice on Related records best practice

      Post

      Hi guys -- 

      This is a follow-up question to a series of previous questions I've already asked. To those who helped me in the past, I hope I'm not becoming a pain.

      I have a CONTACTS table that tracks  church members contact information. In some cases a contact record could simply be an entire family which will be made of many existing contacts. Since families and contacts have most if not all attributes in common, I was wondering it will be better to create a table occurrence of CONTACTS that could be named FAMILIES and create a relationship to it from CONTACTs or instead of a separate table.

      The reason I would want to have a FAMILIES-<CONTACTS relationship is to record many family-specific  information like household income, greeting (for mailing etc.) and to better determine the kinship between church members. Here's my graph and I know there must be a more efficient way of drawing it. I would also appreciate any feedback if anyone notice something wrong with the graph.

      ERD.jpg

        • 1. Re: Advice on Related records best practice
          philmodjunk

          I think you've got a good idea. One wrinkle you might think about though. It's possible for one contact to be a member of more than one family. I see that with my own kids. Me and my estranged wife are members of the same church. We have two different addresses so we would be two "families" in your system. My kids, however, can be treated as members of my "family" and also hers...

          Thus, you might want to use a Join table to link Families to contacts so that one contact can link to more than one family record. On the other hand, you might just put contacts with the custodial parent, not the other in all cases where this happens and then you don't need the join table, leaving you able to structure the data as you propose.

          • 2. Re: Advice on Related records best practice
            InvectusIlumni

            Great PhilModJunk.

            If you remember the discussion we had in this post Advice on self-relationship I wanted to have a kinship relationship between contacts. With this new layout in mind, how would you approach that issue.

            In other words, If Zack and Megan are both part of the Doe family and have in common John and Elizabeth as parents, would it still make sense to establish the relatioship between those family members as previously advised using a join Relationships table or is there a much better approach now that the family comes in play.  I was hoping that once a contact was assigned to family and his role in the family also established, the relationship with other members of that same family would be determined automatically . I just find it counterintuitive having to manually specify related family members when the family can already determined who's part of it.

            • 3. Re: Advice on Related records best practice
              philmodjunk

              Yes I remember the original discussion and see my suggestion in place in your screen shot of your Relationships graph.

              If you want to manage kinship, then the same contact can be related to multiple families via that join table. (Grandparents, divorced families, blended families, etc.) Given the complexities, I'm not sure I follow how that can be automatic. A script might be crafted to trace relationships so that when you link the record for John Smith, all of his listed relatives are also added, but also identifying the type of each such relationship would be tricky and you might get a circular relationship due to how convoluted kinship can be given the above issues that I've described. You'll definitely need to decide how far you want to trace kinship and how to still manage efficient

              Structurally, the change is fairly modest. Put your Family table in place of Contact in your graph and change RelatedContacts to just contacts (or leave it as is if you prefer).

              A portal to relationships on a Family layout can be used to display and link all related contacts. If you format Relationships::ContactID in this portal as a drop down list, and enable "Allow creation of records..." for Relationships in the Families to Relationships relationship, adding a contact can be done by simply selecting a contact in this drop down in the bottom blank row of the portal. A text field in Relationships can be used to document the kinship (spouse, child, grandchild, etc.). You might also want to add a field to document whether the related contact is resident with that family so that you can generate efficient mailing lists/mailing labels without getting duplicates or naming contacts that don't actually live at the address documented in that Family record. You can also add fields from RelatedContacts to this portal to display additional information for a given relative listed in the portal.

              Note that a portal to Relationships placed on a contacts layout can be used to list all linked Family records.

              • 4. Re: Advice on Related records best practice
                InvectusIlumni

                Thabks again buddy --  You're right, given the complexity of what I'm trying to achieve, I think I'll be better off keeping the solution as simple as possible.  Your advice will definately be helful.

                A last one on this, I'm creating a calculation to change the value of the Relationship field in the portal depending on the current record's attributes. I'm getting some syntax errors either due to "there are too many parameters in this function" or "...variable field was not found".

                My eyes must be exhausted and I can't seem to see what's wrong.

                Let (
                  b="Brother";
                  c="Child"; 
                  d="Daughter";
                  f="Father";
                  g="Grand child";
                  k="Cousin";
                  m="Mother";
                  n="Nephew";
                  s="Son";
                  x="Sister";
                  y="Niece";
                  cfk=contacts::family_kinship;
                  rfk=relatedcontacts::family_kinship;
                  rcg=relatedcontacts::gender;
                  Case (
                    cfk=f and rfk=m;"Wife";
                    cfk=m and rfk=f;"Husband";
                    (cfk=f  or cfk=m) and rfk=c and rcg="Male";"Son";
                    (cfk=f  or cfk=m) and rfk=c and rcg="Female";"Daughter";
                    cfk=c and rcg="Male";"Brother";
                    cfk=c and rcg="Female";"Sister";
                    cfk=c and (rfk=n or rfk=y); "Cousin";
                    (cfk=n or cfk=y) and rfk=c; "Cousin";
                    (cfk=f or cfk=m) and rfk=n; "Nephew";
                    (cfk=n or cfk=y) and rfk=f; "Uncle";
                    (cfk=n or cfk=y) and rfk=m; "Aunt";
                    (cfk=f or cfk=m) and rfk=y; "Niece";
                    (cfk=f or cfk=m) and rfk=g; g;
                    cfk=g and rfk=f; "Grand Father";
                    cfk=g and rfk=m; "Grand Motherr";
                    (cfk=n or cfk=y) and (rfk=g and rcg="Male"; n;
                    (cfk=n or cfk=y) and (rfk=g and rcg="Female"; y;
                
                   "" )
                )
                • 5. Re: Advice on Related records best practice
                  InvectusIlumni

                  Oops I fixed my own mistake by adding some square brackets and closing open brackets.

                  Let ([
                    b="Brother";
                    c="Child"; 
                    d="Daughter";
                    f="Father";
                    g="Grand child";
                    k="Cousin";
                    m="Mother";
                    n="Nephew";
                    s="Son";
                    x="Sister";
                    y="Niece";
                    cfk=contacts::family_kinship;
                    rfk=relatedcontacts::family_kinship;
                    rcg=relatedcontacts::gender];
                    Case (
                      cfk=f and rfk=m;"Wife";
                      cfk=m and rfk=f;"Husband";
                      (cfk=f  or cfk=m) and rfk=c and rcg="Male";"Son";
                      (cfk=f  or cfk=m) and rfk=c and rcg="Female";"Daughter";
                      cfk=c and rcg="Male";"Brother";
                      cfk=c and rcg="Female";"Sister";
                      cfk=c and (rfk=n or rfk=y); "Cousin";
                      (cfk=n or cfk=y) and rfk=c; "Cousin";
                      (cfk=f or cfk=m) and rfk=n; "Nephew";
                      (cfk=n or cfk=y) and rfk=f; "Uncle";
                      (cfk=n or cfk=y) and rfk=m; "Aunt";
                      (cfk=f or cfk=m) and rfk=y; "Niece";
                      (cfk=f or cfk=m) and rfk=g; g;
                      cfk=g and rfk=f; "Grand Father";
                      cfk=g and rfk=m; "Grand Motherr";
                      (cfk=n or cfk=y) and (rfk=g and rcg="Male"); n;
                      (cfk=n or cfk=y) and (rfk=g and rcg="Female"); y;
                  
                     "" )
                  )