I'm using the term "reciprocal relationship" in the same sense as https://filemakerhacks.com/2017/10/20/reciprocal-linkage/, 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.
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.