6 Replies Latest reply on Sep 9, 2013 10:24 AM by philmodjunk

    Many to Many problems

    CekariYH

      Title

      Many to Many problems

      Post

           Problems with setting up (or to make it work properly) a Many to Many setup.

           **IDx = index fields

      Two Main Tables:

      Group with two portals, one for a list of people in the group and the other showing all persons not in the group.

      A second instance of the Table in the Relation tab Group 2 for second portal

           GIDx = numeric auto-incr uniq

           IDx = numeric

           GDumIDx = Dummy for second portal

             

      Personnel with two portals, one for a list of groups the person has and the other showing all groups not belonging to the person.

      A second instance of the Table in the Relation tab Personal 2 for second portal

      PIDx = numeric auto-incr uniq

      IDx = numeric

           PDumIDx = Dummy for second portal

       

      One Join-Table

      JGIDx and JPIDx

      Relation-Setup:

      Group     Join       Personal

      GIDx----=JGIDx

                     JPIDx=---PIDx

      (Setup from Knowledge Base "http://help.filemaker.com/app/answers/detail/a_id/9922")

      Something I'm missing or set wrong when defining links in the relation tab?

      I cant get it to work as when I create a new Group I get a new record in the Join table and when I create a new Person it fills out the linked field in the existing record in the join creating a One 2 One relationship only? (Or when I start with creating Personal it makes no new Join-records)

      More Groups creates new Join-records but more Persons than Groups doesn't create new records in the Join-table

      In my word there should be at least as many records in the join as the sum of records in Group and Personal and plus one for every more joins on either side?

      Then, where do I join the Dummy's to make it work without interfere with the main-joins?

      Hope you understand my rambling.

      Please help!

        • 1. Re: Many to Many problems
          CekariYH
               

          In my word there should be at least as many records in the join as the sum of records in Group and Personal and plus one for every more joins on either side?

          Rethinking this: Not sure that's the truth... probably 1 join-record for every join perhaps, so if one Group has 3 Persons there is 3 Join-records and if one other Person belongs to 3 other Groups thats 3 more Join-records ?

          • 2. Re: Many to Many problems
            CekariYH

                 This is how it looks:

                 So, where do I link the 2:nd instances of the of the 2 "tables" under the others?

                  

            • 3. Re: Many to Many problems
              CekariYH

                   ...and this is how I think the Join-table should look as, as an example.

                    

              • 4. Re: Many to Many problems
                philmodjunk

                     Your screen shot of your relationships looks correct. But your sample table appears to contain errors. The third Person record shows a PIDx of 3 and this would not link to a Join record with a PIDx of 5. Perhaps that was a typographical error?

                     A join table record will link exactly one person record to one group record. So for any one person, there will be as many join table records as their are groups to which they are linked and for any one group record there will be as many join table records as there are person records that belong to that group.

                     I suspect that the problems you are having are due to mistakes in your layout design. The simplest layout design for working with a many to many relationship is to use a portal to the Join table that includes additional fields from the 3rd table. As an example, you can place a portal to your Join table on Your Person Layout and use it to select Groups for the current Person record displayed on your layout. A group name field from the Groups table can be included in the row of the portal. And a simple way to select a group is to format the JGPIDx field as a drop down list of GIDX and Group Name values.

                     Here's a demo file where this approach is referred to as the "basic setup". It matches contacts to events, but if you were to rename them Persons and Groups, you'd have the same setup you are trying to create here: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                • 5. Re: Many to Many problems
                  CekariYH

                        

                       

                            The third Person record shows a PIDx of 3 and this would not link to a Join record with a PIDx of 5. Perhaps that was a typographical error?

                       Yup, as following Joins are as well after reading your following statement.

                        

                       

                            A join table record will link exactly one person record to one group record. So for any one person, there will be as many join table records as their are groups to which they are linked and for any one group record there will be as many join table records as there are person records that belong to that group.

                       Yes of course , should have known that... just brain-dead sometimes :-/

                       Now i at least know what i was doing wrong, thanks.

                        

                       

                            I suspect that the problems you are having are due to mistakes in your layout design.

                       Agree as I'm still new to FMP :-)

                       

                             

                       

                            The simplest layout design for working with a many to many relationship is to use a portal to the Join table that includes additional fields from the 3rd table. As an example, you can place a portal to your Join table on Your Person Layout and use it to select Groups for the current Person record displayed on your layout. A group name field from the Groups table can be included in the row of the portal. And a simple way to select a group is to format the JGPIDx field as a drop down list of GIDX and Group Name values.

                       Yes, that's what I'm trying to do, one portal to show linked records and one portal to show non linked records that with a check box to link them, on "both sides", so I need to get where I should link the 2:nd instances of the Grupper and the Personal tables. 

                  The strike through text... well, doesn't look nice to me, a check box is more aesthetic to me :-)

                        

                       Thanks a lot for all the help.

                        

                        

                        

                        

                        

                  • 6. Re: Many to Many problems
                    philmodjunk

                         I have only describing a single portal for adding people as a way to make sure that your layout design works.

                         For a "checkbox" approach, there's a version of that in the demo file.