AnsweredAssumed Answered

Bidirectional Many-to-Many Self Join

Question asked by nihmbrisby on May 19, 2014
Latest reply on May 21, 2014 by philmodjunk


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!