7 Replies Latest reply on May 21, 2014 8:44 AM by philmodjunk

    Bidirectional Many-to-Many Self Join



      Bidirectional Many-to-Many Self Join



           Lets say I have one table- Clients.  These clients can have relationships amongst themselves.  So one of my clients may be the employer for another client.  This creates a many-many self join in the Clients table.  I established this relationship by creating a self relationship mitigated by a join table.  Now on my layout I have  a portal from a layout based on clients into the Join table.  I create new records (which record the relationship) in this portal.  

           Works fine except it isn't bidirectional.  The join records have 2 foreign keys, and of course the portal only displays records based on the record in which I created the relationship.  That is to say if I create a relationship in the portal FROM Sarah's record relating her TO Julie (Sarah Employees Julie), the opposite relationship does not display in the portal when i switch to Julie's record (Julie is Sarah's Employee).  

           How can I achieve this?  I had planned this setup incorrectly assuming I would be able to create a relationships where the primary key = foreign key 1 OR primary key = foreign key 2 (i use validated UUID's for primary keys).  Any help is much appreciated!

        • 1. Re: Bidirectional Many-to-Many Self Join

               A script could be used to make a second join table record with the _fk field values swapped each time you create a record linking Client 1 to Client 2.

               So if a given record is created by you to document Client 1 as the employer of Client 5, the fields in your first join table records would be:

               _fkClientIDa = 1
               _fkClientIDb = 5
               Relatioship: employs  (client 1 employs client5)

               The script can produce:

               _fkClientIDa = 5
               _fkClientIDb = 1
               Relationship: employed by (Client 5 is employed by client1)

          • 2. Re: Bidirectional Many-to-Many Self Join

                 Thanks Phil.  I had hoped for a relationship answer mainly cause I wanted to make sure if one side was deleted in the portal the other would be as well.  While I could script a delete button that did such a thing, I'd be worried that they'd find another way to delete it that didn't activate the script and left the reciprocal relationship intact.  Any ideas?  If not I think I'll just have a pop up when they delete a relationship that the other clients relationship has to be deleted also or something.

            • 3. Re: Bidirectional Many-to-Many Self Join

                   You can use a third match field to link the pair of records to each other and then enable both delete options in the relationship that links them. Set up this third field as a match field and when your script creates the second join table record, it can copy the value of this field from the first record to a variable and then after creating the second record, it can update this field with the value from the variable.


              • 4. Re: Bidirectional Many-to-Many Self Join

                     Wow, so let me see if I'm understanding this right:  In addition to the three table occurrences I described and the create-new-join-record script you suggested (which flips the foreign keys), I will include a third match field.  Then, i created a new Table Occurence (let's call it join_Delete) based on original join_Clients table.  This occurrence is a self join based on the new match field with delete turned on.  Now, when a record is deleted in the join_Clients table occurence from the portal on the contact record, the delete sort of cascades down to the join_Delete table occurrence.  First the record is deleted in the join_clients- this will then delete the matching record in join_Delete?

                     Much thanks for your help Phil!

                • 5. Re: Bidirectional Many-to-Many Self Join

                       That's the idea.

                  • 6. Re: Bidirectional Many-to-Many Self Join

                         Everything is working fine now with one slight quirk.  The script that duplicates the record and switches the values- it's activate by a button press.  The button is in a pop up.  After i press the button, both records display in the portal.  However, when the popup closes, the portal displays the single correct record.  I assume this for 2 reasons:

                    1.           My script is switching the values not on the duplicated record, but on the original
                    3.           The portal is refreshed (or something) when the pop up closes


                         Therefore I'm guessing my script if faulty in that it's not operating on the duplicated record.  Just my guess.  I've attached an image of my script if anyone has any ideas.  It just set my variables, does the duplicate script, then set fields to the variables.  I've been googling for at least an hour, but have had no luck in educating myself in how one makes the duplicates record the active record. 

                         Phil- I can't thank you enough for your assistance.

                    • 7. Re: Bidirectional Many-to-Many Self Join

                           If the relationships are set up like I would expect them to be set up, one of the two records will not be linked to your current layout record as it is linked to the record from the other side of the join table. Thus, when the layout refreshes, it drops from view as it's foreign key won't match to the primary key of the current record as the key field values were switched.

                           Thus, this would appear to be functioning correctly.

                           What you should do is after linking Record A to Record B, pull up Record B and see if it is already linked to A as that is the intent of this method.