9 Replies Latest reply on Feb 24, 2010 11:03 PM by comment_1

    Layouts and Many-to-Many Relationships

    pooba53

      Title

      Layouts and Many-to-Many Relationships

      Post

      I have three tables:

       

      people

      groups

      join_people_groups (join table)

       

      I'm using a join table because people can be associated with more than one group and a group can have many people.

       

      I have the relationship between people and the join table such that new records can be created (and deleted) in the join table when I choose one or more displayed groups from a checkbox list in a layout.

       

      My checkbox value list in the layout is showing the correct text values from the groups table and is recording the actual groups_id key.

       

      What I thought would happen is that the join table would create new entries within its table for each group_id and people_id combination. Instead it puts multiple entries in the group_id field rather than making another record.

       

      So if person with id of 1 has memberships to group_ids of 5 and 2, the join table plops both numbers in the group_id field under a SINGLE entry rather than two.

       

      This does not seem like the desired behavior. How can I get a record created in the join table for each person-group relatinship????

       

      Thanks!

      -Dan 

        • 1. Re: Layouts and Many-to-Many Relationships
          comment_1
            

          I don't understand where the checkbox comes in. I suggest you look at the demo posted here:

          http://www.fmforums.com/forum/showpost.php?post/246136/

          • 2. Re: Layouts and Many-to-Many Relationships
            pooba53
              

            I looked at your demo, thanks. The tables are set up exactly like mine and are behaving as I'd LIKE mine to when entering group choices.

             

            I'm using checkboxes to display group selections in my people layout so I can make the group associations. When I check or uncheck the group selections, the only thing that changes is the group_id field in the 1 record in the join table. It should be adding new records in my join table, not adding additional group_ids in the group_id field within my join table. Conversely, if i remove a group association, that record shoud delete from the join table.

            • 3. Re: Layouts and Many-to-Many Relationships
              comment_1
                

              You can easily DISPLAY existing joins by means of a checkbox field (a calculated one!) - but you cannot MAKE associations just by checking a selection in the field - for this, you need to create a new record in the join table. Similarly, to remove a join, you need to delete that record.

               

              If you want, you could run a script on modification of the checkbox field to add/delete the appropriate record/s in the join table, or (preferably IMHO) use a portal showing all records from the Groups table, with a button to toggle them on/off.

              • 4. Re: Layouts and Many-to-Many Relationships
                pooba53
                  

                So I guess my real question now is: What's the best way to collect data in a layout that establishes these relationships? I thought my method was pretty sound. Now it seems it is not.

                 

                I figured if I have a new person that needs entering into my database, this would be a swell time to note the group associations.

                • 5. Re: Layouts and Many-to-Many Relationships
                  comment_1
                     I am not really sure what you are asking.
                  • 6. Re: Layouts and Many-to-Many Relationships
                    pooba53
                      

                    Please read from the beginning of the original post so you are aware of my tables and relationships. Let's assume these are all perfect.

                     

                    Now. Let's say I'm working from a people layout. I am entering data associated with a person.

                     

                    Within this layout, I WAS using a checkbox list of group text (I hid group_id number) using a value list. I mean, I have to have SOME way of selcting none or many groups this person is affiliated with.

                     

                    Now, I am trying to figure out how I can achieve what I've described above, whether a checkbox is needed or not, so that the associations are clear in the layout, but I also have the proper entries for each relationship in the join table.

                     

                    I don't know how to ask this any other way, I'm afraid. I don't know how to make this happen.

                    • 7. Re: Layouts and Many-to-Many Relationships
                      comment_1
                        

                      I think I have already answered this: people are associated with groups by means of records in the join table. Selecting (or deselecting) an item in a checkbox field will NOT cause a record in the join table to be created (or deleted). All it does is add an an item to the field (or remove it).

                       

                      The simple way to add a person to a group is to select the group in the last row of the portal to the join table.

                       

                       

                      NOTE: you could use a checkboxfield to assign a person to one or more groups directly; but then you wouldn't be using a join table and - as a result - you would lose quite a lot of finding/reporting abilities. Also, the join wouldn't be able to have any attributes of its own (for example the date of joining the group).

                       

                      • 8. Re: Layouts and Many-to-Many Relationships
                        pooba53
                          

                        Okay. I set up a portal like you showed in your demo. This DOES work, but from a user interface perspective and knowing that many people will be using this from a Web bbrowser, this does not seem ideal. People can go to the last column and delete the group name and it actually deletes it from the group table.

                         

                        Also, it looks a bit silly having an ID number present instead of the text on the far left. You can hide the first field and use a pop-up menu, but it does not look very good or perform well when using a Web browser.

                         

                        Also, if one enters a duplicate, it's not readily apparent how to delete what you've entered. Really gets weird via a Web browser.

                         

                        Just seems like there's a better way. I will keep searching and reading my missing manual book :-)

                         

                        -Dan

                        • 9. Re: Layouts and Many-to-Many Relationships
                          comment_1
                             Well, one has to learn to walk before they can run. I called that demo "basic" for a good reason: it purposefully lacks anything that is not essential to the fundamental principle. However, even in this rudimentary implementation you cannot "go to the last column and delete the group name" - because the field is not enterable.


                          Once you understand the basic principles you can add more features to it (e.g. prevent duplicate joins) and build the user interface in whatever way you wish. I have already mentioned one such alternative in message #4 above.