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!