6 Replies Latest reply on Jul 13, 2017 5:06 PM by user1069

    relating records to records in the same table and being able to view them from both records through the same portal...

    ericjlindholm

      Im sure this is more simple then I am making it in my head but I am hitting a wall.  i have been playing with multi key relationships and some other options but I keep running into problems.  I plan on created an "adder" table that i will vacate after each new contact is selected to be added and the relationship text is filled out.

       

      I have a table contactCompany and a table contactCompantRelationships

       

      My companies and contacts are in the same table on purpose and my solution needs to remain that way.  I know there are other ways to set that up but it is valuable for me.

       

      I want to be able to say 

       

      Dave is the owner of  company ABC

      Nick is an employee of company ABC

      Nick used to work for company XYZ

      Dave are enemies joe

       

      When a users views daves contactCompany record, they should see the relationship record that names him the owner of company ABC and one that says is an enemy of contactcompany with the name Joe

      when a user views nick , they should see that he works for ABC and used to work for XYZ

      when a user views XYZ they should see nick uses to work for them

      viewing ABC they should see dave is the owner and nick is an employee

      viewing joe displays that dave is his enemy

       

      Thank you in advance

        • 1. Re: relating records to records in the same table and being able to view them from both records through the same portal...
          brianb

          are all the variables manually selected or are they calculations? i.e. How is an enemy of XYZ determined?

           

          if you are just pulling information from a field you can add a text box to the layout and the import the related field.value

           

          Menu Bar > Insert > Merge Field > select related table/field      An example output would produce something to the effect of

          <<name>> Owns Company <<companyname>>

           

          It is a calculation that determines field relationship (not records) then  imagine it is more complex and depends on structure

          • 2. Re: relating records to records in the same table and being able to view them from both records through the same portal...
            ericjlindholm

            I should have been more clear on that.

             

             

            my contactCompany table obviously has a primary key and a name field

             

            my contactCompany Relationship table has a primary key and a text field where a user describes the relationship.

            It also has a id_relatedFrom and id_relatedTo field like any normal join table but if i do it that way, I cannot see the relationships in the same portal from the related to and related from records.  in this example if i join dave as the contactCompanyFrom and ABC as the contactCompanyTo in the join table, I cannot see that the relationship from the ABC record because the current tables are setup

             

            ContactCompanyFrom---<ContactCompanyRelationship------ ContactCompanyRelationship_To

             

            I am viewing the ContactCompanyRelationship portal on the ContactCompanyFrom record but what to see the relationship  bi directionally  in the same portal as described in the OP

            • 3. Re: relating records to records in the same table and being able to view them from both records through the same portal...
              philmodjunk

              Seems like you need this data model:

               

              Contacts----<Relationship>----RelatedContacts

               

              Contacts::__pkContactID = Relationship::_fkParentContactID

              RelatedContacts::__pkContactID = Relationship::_fkChildContactID

               

              Where Contacts and RelatedContacts are TO's of the same data source table, your table of individual and company contacts.

               

              To get a "two way relationship" so that you can see what companies an individual is linked to and also see what individuals a company is linked to, Create two records in Relationship that are identical except that the two _Fk field values are swapped.

               

              SO if contact #1 needs to be linked to contact #234, you create two records that would look like this in table view:

               

              _fkParentContactID     _fkChildContactID

              1                                  234

              234                              1

               

              You can use a script to "back link" a pair of records by creating the second record in the relationships table as part of linking the two contacts in a relationship.

              • 4. Re: relating records to records in the same table and being able to view them from both records through the same portal...
                user1069

                Here's (a grossly simplified version of) how I do it:

                 

                CONTACT ––––< RELATIONSHIP ––––– recip RELATIONSHIP >–––– recip CONTACT

                 

                The relationship between CONTACT and RELATIONSHIP is CONTACT::ID = RELATIONSHIP::ContactID

                The relationship between RELATIONSHIP and recip RELATIONSHIP is RELATIONSHIP::ID = recip RELATIONSHIP::reciprocalID and RELATIONSHIP::reciprocalID = recip RELATIONSHIP::ID

                The relationship between recip RELATIONSHIP and recip CONTACT is recip RELATIONSHIP::ContactID = recip CONTACT::ID

                 

                The RELATIONSHIP table contains the "Role" field which describes the role played by the CONTACT associated with it. For example:

                Malia Obama ––––< Daughter ––––– Father >–––– Barack Obama

                Rex Tillerson ––––< Employee ––––– Employer >–––– US Department of State

                Brigitte Macron ––––< Wife ––––– Husband >–––– Emnanuel Macron

                 

                This model means you can see relationships from any direction which is what it sounds like you're looking for. It also means you don't have the possibility of the relationship records being out of sync as you do with the

                CONTACT ––––< RELATIONSHIP >–––– recip CONTACT

                model since there is inherently no directionality in the relationships and the role is only stored once.

                2 of 2 people found this helpful
                • 5. Re: relating records to records in the same table and being able to view them from both records through the same portal...
                  philmodjunk

                  I like that!

                   

                  You still have to create two relationship records so I do not see that it has much advantage when it comes to keeping things "in synch", but it's definitely clearer and easier to understand when you find it in a relationships graph.

                  • 6. Re: relating records to records in the same table and being able to view them from both records through the same portal...
                    user1069

                    By "in sync" I mean if you need to adjust the relationship for whatever reason (sometimes mom fills out the form but accidentally lists herself as dad). We used to do it the other way, but didn't have the scripting controls in place to make sure both records were updated when a user changes "father" to "mother" so you'd get different results depending on which direction you looked:

                    Malia Obama ––––< Daughter/Father >–––– Barack Obama

                    Barack Obama ––––< Mother/Daughter >–––– Malia Obama

                     

                    Both relationship records are created simultaneously. If either is missing, the relationship doesn't exist for all practical purposes. We use the "magic key" method for most record creation, so we end up creating them transactionally:

                     

                    Set Field [ Relationship Creator by ID::ContactID ; Value: "1" ]

                    Set Field [ Relationship Creator by ID::roleKey ; Value: "Father" ]

                    Set Field [ recip Relationship Creator by reciprocalID::ContactID ; Value: "234" ]

                    Set Field [ recip Relationship Creator by reciprocalID::roleKey ; Value: "Daughter" ]

                    Set Field [ Relationship Creator by ID::reciprocalID ; Value: recip Relationship Creator by reciprocalID::ID ]

                    Commit Records/Requests [ With dialog: Off ]

                     

                    Note that the third step there populates both the recip Relationship Creator by reciprocalID::ID field (via auto-enter value) and the recip Relationship Creator by reciprocalID::reciprocalID field (via the magic key relationship) at the same time. We then complete the double-link in the fifth step and commit both records in a single transaction.

                    1 of 1 people found this helpful