4 Replies Latest reply on May 14, 2014 6:22 PM by smith7180

    Many to Many Self Join Relationship



      Many to Many Self Join Relationship


           Hi All

           I'm new to Filemaker and have got myself very confused about how to deal with a many to many self relationship (I think that's what it is?!) so any advice would be fantastic.


           The situation is this: I have a table called concepts, and I want to be able to set up a relationship so that every concept is linked to other concepts that are a previous knowledge requirements to understand that concept. I.e. A concepts will have  many other 'previous concepts' that would need to be linked. By the same token the said concept could also be a 'previous concept' for many other concepts.

           Is this a relationship I can display in filemaker? I have attempted it with a self join relationship, and a self join relationship with a join table, and neither seems to work properly.



        • 1. Re: Many to Many Self Join Relationship

               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.

          • 2. Re: Many to Many Self Join Relationship

                 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'?

            • 3. Re: Many to Many Self Join Relationship

                   Option 1:

                   If you create a layout that refers to PreviousConcepts instead of concepts, you can run the relationships in reverse.

                   Option 2:

                   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:

                   Record 1:
                   Concept_Join::_fkConceptID = 5
                   Concept_Join::_fkPrevConceptID = 456

                   Record 2:
                   Concept_Join::_fkConceptID = 456
                   Concept_Join::_fkPrevConceptID = 5

                   A script can make creation of the "mirror" join record automatic.

              • 4. Re: Many to Many Self Join Relationship


                     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:

                1.           'Contacts.'  One primary Key
                3.           'join_Contacts_Contacts.'  Two foreign keys. 
                5.           '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:

                1.           A new record is created in join_Contacts_Contacts.
                3.           The primary key from Contacts populates its respective foreign key in join_Contacts_Contacts.
                5.           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.
                7.           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:

                1.           What does he mean in option 1?
                3.           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
                5.           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!