3 Replies Latest reply on Jan 22, 2013 7:47 AM by tamcderm

    Create Relationship to Record in the Same Table

    tamcderm

      I have a table with records of individuals and businesses. How can a establish a relationship between two individuals within the same table to show they are siblings or spouses or between and individual and business to show an employer/employee relationship? Thanks.

        • 1. Re: Create Relationship to Record in the Same Table
          stephensexton

          You could set up a table of businesses and then link the individuals to a business (the business table should include some form of serial ID or UUID field to ensure uniqueness of each record, and there should be a matching BusinessID field created in your main contact's table).  If you wanted to show a portal within an individual's record (form view) that showed all other individuals linked to the same business, you could set up a self-join in the relationships graph - i.e. create another table occurrence for your contacts (e.g. call it something like Individuals_Self) and join this to the original contact's table occurrence via the BusinessID field.

           

          A portal based on the "Individuals_Self" table occurrence can then be added to your main contact's form.  Another potential link that you could use is phone number if the phone number of related contacts is likely to be the same.  If you used this option, I would suggest setting up a calculation field that automatically corrects the formatting of the phone number (e.g. removes spaces, letters and uses the correct regional code etc.).

          • 2. Re: Create Relationship to Record in the Same Table
            comment

            tamcderm wrote:


            How can a establish a relationship between two individuals within the same table to show they are siblings or spouses or between and individual and business to show an employer/employee relationship? 

            If you mean all of these, i.e. that each record can be linked to several other records in different types of relationship, you will need a join table to record the type of the link.

            • 3. Re: Create Relationship to Record in the Same Table
              tamcderm

              Thanks for the help.  However, I used a join table to establish a many to many relationship between organizations and individuals, and placing organizations in their own table and that worked fine for individual to organization relationships.  The challenge remained to relate individuals to each other such as a spouse to a spouse; parent to children; and siblings to each other.  It appears a self-join relationship is a solution, but I am unclear if it will solve this need.