A self join relationship with a join table would seem to be the way to go here:
Concepts::__pkConceptID = Concept_Join::_fkConceptID
PreviousConcepts::__pkConceptID = Concept_Join::_fkPrevConceptID
PreviousConcepts is a second Tutorial: What are Table Occurrences? of concepts.
See the first post of this thread for an explanation of the notation that I used: Common Forum Relationship and Field Notations Explained
Note that this relationship is "one way" you can go from concepts to previous concepts to pull up a list of previous concepts but the reverse cannot be done without additional changes made in how you manage your records in the join table.
Thanks so much, that has completely cleared it up for me.
Out of curiosity how would I go about making the the relationship 'two way'?
If you create a layout that refers to PreviousConcepts instead of concepts, you can run the relationships in reverse.
You can create two records in the join table that "mirror" each other. The link to the same two concepts but the ID numbers are in opposite match fields.
Thus, to link concept # 5 to concept #456 in both directions, you'd create two records in Concept_Join:
Concept_Join::_fkConceptID = 5
Concept_Join::_fkPrevConceptID = 456
Concept_Join::_fkConceptID = 456
Concept_Join::_fkPrevConceptID = 5
A script can make creation of the "mirror" join record automatic.
I'm working on my first filemaker database, and I have a similar issue. I have a 'contacts' table and a 'join_Contacts_Contacts' table. I have three table occurrences:
- 'Contacts.' One primary Key
- 'join_Contacts_Contacts.' Two foreign keys.
- 'relations_Contacts.' One primary key
The attached image shows the relationships between these three tables.
The user is working in a layout with context- 'Contacts.' A portal on this layout looks into 'join_Contacts_Contacts'. Each portal role has a 'create' button. When clicked:
- A new record is created in join_Contacts_Contacts.
- The primary key from Contacts populates its respective foreign key in join_Contacts_Contacts.
- A pop up appears were the user selects from a drop down showing all contacts (The value list is based of the primary key from 'relations_Contacts' and only displays names. When the name is selected the other foreign key in 'join_Contacts_Contacs' is now populated with the parent key from relations_contacts.
- The user also selects the type of relationships (Father, friend, spouse, etc...)
What I want to happen next:
If I'm on Joe's record and I follow the above steps to select Bob and select type: Father, I would like the portal in Bob's record to show Joe with type: son.
While I think I understand Phil's suggestion in option 2, I have these three questions:
- What does he mean in option 1?
- Presuming that doesn't work for me as well as option 2, I'd like to ask: Is there no other way to accomplish this than through scripting? I'm trying to avoid any complicated scripting as I'm am a total noob and very concerned about writing scripts I don't fully, 100%, get. I'm concerned they'll have unintended consequences
- If there's no way around scripting, could anyone offer me some tips or point out pitfalls commonly encountered with this sort of scripting? (my training thus far: All the Lynda filemaker 12 and 13 courses, browsing the 'missing manual' and 'Developers Reference,' a bunch of browsing on filemaker sites, and of course much perusing of the forums (three cheers for philmodjunk!)
Thanks a lot!