AnsweredAssumed Answered

How to build a reciprocal relationship with join table attributes?

Question asked by chivalry on May 31, 2018
Latest reply on May 31, 2018 by philmodjunk

I'm using the term "reciprocal relationship" in the same sense as, but I think of it as a "many-to-many self-join".


What I've tried: `PRSN` table stores people, `CNCTN` table stores connections between people, has fields `prsn_1_id`, `prsn_2_id`, `relationship_1`, `relationship_2`. The `relationship_*` fields store descriptions of the relationship from person to person, "Supervisor," "Team Memeber," "Coworker," etc.


12SupervisorTeam Member


What I want is to have a portal from `PRSN` to `prsn_CNCTN` (with `prsn_cnctn_PRSN` on the other side) where I can see the other person's information and the relationship to the other person.


What I tried to do, that works but doesn't work:


  • create a multi-key field prsn_ids in `CNCTN` that's set to SortValues ( List ( prsn_1_id ; prsn_2_id ) ; 2 ).
  • create a global field, PRSN_ID
  • attach an OnRecordLoad script to the layout that sets the PRSN_ID current record's id
  • create a OTHER_PRSN_ID match field set to Substitute ( prsn_ids ; PRSN_ID ; "" ).
  • connect the `prsn_CNCTN` and `prsn_cnctn_PRSN` TOs matching prsn_CNCTN::OTHER_PRSN_ID to prsn_cnctn_PRSN::id.


I say it "works but doesn't" because the portal shows the right value when I'm viewing person 1's record, but person 2's own record when viewing that record, even though the Data Viewer shows person 1's record. Refreshing the portal and window don't help, and neither does going to layout mode and returning to browse mode.


I know others have solved this. I've seen mention of 1 and 2 record solutions in the join file, but haven't been able to find a sample file or sufficient directions to build it.