A "many to many self join" relationship should do the trick:
Where Groups and Groups|Linked are occurrences of the same table.
Groups::__pkGroupID = Group_Link::_fkGroupID
Groups|Linked::__pkGroupID = Group_Link::_fkLinkedGroupID
But this creates a "one way" link from Groups to Groups|Linked. To make it a "two way" link, you create two records in Group_link. In the second record, the values in _fkGroupID and _fkLinkedGroupID would be swapped.
Thanks for your help.
I was with you up until the end bit (starting with 'But this creates..."). So the one-way link is ok.
You lost me when you mentioned creating records in Group_link. I have no data to put in there so how does this work? And then of course I don't understand how different records in the same table can swap pk and fk values...
Sorry - I'm not sure why I'm failing to grasp such basics having read so much about it all.
You lost me when you mentioned creating records in Group_link. I have no data to put in there so how does this work?
You DO have data to put in there and it's shown in my previous post. The only data you have to enter into a record in Group_Links are the ID numbers of the two records linked to it.
And then of course I don't understand how different records in the same table can swap pk and fk values...
Typically, you use a portal to the join table in order to link records via join table. In your case, you can put a portal to Group_Link on your groups layout, enable "allow creation..." in the relationship and format _fkLinkedGroupID with a value list of groups to select from in order to link the current record on your Group layout to another group. Fields from the Groups|Linked table occurrence can be included in the portal row to show info about that group once you have selected it from the drop down list.
If you have a record in Group_Link with these values:
_fkGroupID = 25
_fkLinkedGroupID = 55
Which links group 25 to group 55. Then a script can create a new record in Group_Link with these values:
_fkGroupID = 55
_fkLinkedGroupID = 25
Which links group 55 to group 25 to so that you now have links in "both directions".
I get it. Thank you very much.