7 Replies Latest reply on Jan 3, 2011 12:23 PM by philmodjunk

    Advice on self-relationship



      Advice on self-relationship


      Hi all,

      My solution has a CONTACTS table that contains information about members of a church.

      Ideally, each contact can belong to a family and in that family, the contact would have a role (kinship) i.e: father, mother, son etc.

      Let's say John Doe and Elizabeth Doe are married and have 2 children: Zack Doe and Megan Doe.

      When viewing John Doe's profile page, I'd also like to see who he's related to and the type of relationship that binds him to that other contact. The same would be true for every member of the Doe family

      I'm just a little lost in the relationship graph. I've already created a table occurrence that I called family that is based on the CONTACT table.

      I'm not sure how to move on from hear and I'd appreciate any help.

        • 1. Re: Advice on self-relationship

          I think you'll want another table for this to set it up as a join table between two table occurrences of your contact table:

          Contact---<Relationships>----RelatedContacts    (RelatedContacts is a table occurrence of Contact)

          Contact::ContactID = Relationships::ContactID
          RelatedContacts::ContactID = Relationships::RelatedContactID

          A field defined in Relationships, can document the type of relationship, (spouse, child, grandchild, etc.)

          Place a portal to Relationships on your Contact layout and include fields as needed to show contact info about each relative.

          • 2. Re: Advice on self-relationship

            As always thanks PhilModJunk.

            When I add the portal to the Contact layout, what do you think is the best way to determine who's related to who.

            Should I have a dropdown list of contacts to choose from on the Contact layout or do you have a better suggestion?

            • 3. Re: Advice on self-relationship

              Depends on how many contacts will be entered in your database. For moderate numbers of contacts, a drop down list works fairly well.

              For larger numbers of contacts, you may want to set up a search tool that let's you search for individuals by name with a script that adds the link to the relationships portal.

              • 4. Re: Advice on self-relationship

                One last question Phil,

                The fields I'm placing on my portal, should they be be populated from the related Contacts occurrence or the Contact Occurence. I already have records on my Contacts table. If I place a dropdown field from the relatedContacts occurrence on my Contacts layout, there are no records displaying. All contact records should only be saved on the same table, shouldn't they?

                • 5. Re: Advice on self-relationship

                  Neither. The drop down should be Relationships::RelatedContactID. That links the record via RelatedContacts to a different record in the Contacts table.

                  • 6. Re: Advice on self-relationship

                    Hey Phil, sorry to be such a pain, but I'm not sure Ièm following you just yet.

                    Here's my current setup:

                    1 portal with 5 fields: Relationships::ContactID, Relationships::relation, relatedContacts::gender, relatedContacts::status and relatedContacts::department.

                    Relationships::RelatedContactID is the dropdown field in question and currently I have it pulling data from relatedContacts::full_name through a value list to display the full name of the member.

                    This is where I get stuck. the Dropdown is inactive.

                    • 7. Re: Advice on self-relationship

                      ContactID should be an auto-entered serial number. You don't want to match by name as names are not unique and names change when people marry, get adopted or just because they want a different name. (And sometimes a name gets entered incorrectly and you have to fix it at some unknown time in the future when the mistake is discovered/reported.)

                      Your value lists should list the ID number in column 1 and list a name field in column 2 so that you can select a name, but the value list enters the ID number.