6 Replies Latest reply on Sep 6, 2012 8:51 AM by schamblee

    Complex Relationship

    dasloane

      Title

      Complex Relationship

      Post

      I have a table named Contacts; no problem.  Each contact (record) in Contacts can have a one-to-many relationships with other contacts within that table.  For example, Don can be Nicole's father and Don can also be Steve's brother.  I need to show all of the relationships Don has is a portal on a tab named relationships. 

      Portal rows for Don might be

      Name        Relationship

      Nicole        Daughter

      Steve        Brother

      Josephine  Wife

      I'm having trouble figuring out how to create the relationship map and what scriping i need to create the relationships Frown

        • 1. Re: Complex Relationship
          schamblee

          I don't know if this will do what you are looking for but I think you need a familykey in your Contacts and then your relation between contacts would be based on this family key. 

          • 2. Re: Complex Relationship
            schamblee

            I made a sample app to give you an idea of what I was talking about in my above post.

            https://www.sugarsync.com/pf/D9559058_790_708343235

            • 3. Re: Complex Relationship
              philmodjunk

              What you end up with is really a "many to many self join relationship". You link contacts to another occurrence of contacts, but with a "join" table in between so that many contacts can be related to many other contacts.

              Contacts----<Relationships>----RelatedContacts      (RelatedContacts is a second occurrence of Contacts, --< means "one to many" )

              Contacts::ContactID = Relationships::ContactID1
              RelatedContacts::ContactID = Relationships::ContactID2

              You enable "allow creation of records via this relationship" for Contacts to Relationships and place a portal to Relationships on your Contacts layout.

              Place ContactID2 in this portal row with name and any other contact fields that you want added to this portal row, but taken from RelatedContacts.

              Format ContactID2 as a drop down list of ID numbers and corresponsing names from the contacts table.

              THis allows you to list related contacts by selecting them from the drop down.

              A script can make a second record in Relationships with the values in ContactID1 and ContactID2 swapped so that linking Jim to John by selecting Jim's name in the drop down automatically links John to Jim.

              • 4. Re: Complex Relationship
                dasloane

                Thank you both, very helpful but I'm still struggeling.  PhilModJunk - can you take a look at the attached graphic and let me know what I'm missing.  I'm gettign a many-to-many relationship.  Arghh...

                • 5. Re: Complex Relationship
                  philmodjunk

                  It looks correct. Are RelatedContacts and Contacts occurrences of the same data source table? (If you hover the mouse over the arrow in the top left corner, do you see the same table name appear?)

                  • 6. Re: Complex Relationship
                    schamblee

                    Hello.  I don't know if you got this figured out yet but I have and here is the sample file.  I hope this helps.

                    https://www.sugarsync.com/pf/D9559058_790_700884476