This question seems to be asked with increasing frequency.
Assuming that you have this relationship:
People::__pkPeopleID = SelfJoin::_fkPeopleID1
People 2::__pkPeopleID = SelfJoin::_fkPeopleID2
You can make the link between two records in People work in both directions if you create two records in SelfJoin, but with the values in the two _fk fields switched. This is a process that can be automated so that adding a new record in the SelfJoin table performs a script that checks for the existance of a corresponding "mirror" record and creates one if it does not already exist.
The following relationship can make creating such a record easy to do:
SelfJoin::_fkPeopleID1 = SelfJoin 2::_fkPeopleID2 AND
SelfJoin::_fkPeopleID2 = SelfJoine 2:_fkPeopleID1
If you "allow creation..." for SelfJoin 2, this single script step will create the mirror record if one does not already exist:
Set Field [SelfJoin 2::_fkPeopleID2 ; SelfJoin::_fkPeopleID1]
The allow creation option not only enable creating the second join record but populates the _fkPeople1 field in the mirror record with the value of the _fkPeopleID2 field of the current Join table record.
Thanks for the quick reply!
First, i did setup the self join you the way you mentioned. However, i wasn't able to implement your technique.
1) Should i use 2 portals in the People table, one to choose relatives and one to list them?
2) self join 1 and self join 2 - i should create a duplicate of the self join table? (self join 2)
3) if that is the case, then where does _fkPeopleID2 come into play? Am i supposed to keep the 2nd People table ,
1) you should not need a second portal
2) You need a duplicate Tutorial: What are Table Occurrences?--not a duplicate table.
3) your existing join table shoule have two foreign key fields in order to facilitate your original self join relationship. _fkPeopleID2 is the field name I used in my example for the second foreign key field, the one that identifies the related person to which you have linked as a relative of your current people record.
I finally got it!
once we are on this topic,
I created a field in the SelfJoin table called 'relative' so that when i pick 'Jane' i can go to the 'relative' field in the portal and choose 'Spouse/wife' from the dropdown.
Is there a way to 'port' the Spouse option and paste it on 'John's' portal row to display husband under Jane's profile.
This would also help for listing children under a Parent's profile.
Thanks again for all your help!
Given that the value of that field will depend on the value you select in the same field of a different record in the same table, I think you'd need a script to put the correct value in "relative" in the mirror record.