9 Replies Latest reply on Feb 28, 2012 1:54 PM by lkeyes

    Many to Many Portal - Stumped on this classic problem

    lkeyes

      Hi.... I'm stumped on the classic many to many problem. I have two tables --- committes and members, and I want ot have a portal on the Committee layout which shows a list of members.

      The portal only needs to display last name and first name (both from the contacts table), as well as the committee office that that the person is fulfilling. Something like:

       

      President John Doe

      Vice President Mary Smith

      Treasurer Joe Dokes

       

      etc.

       

      Since the contacts can serve on multiple committees, I've created a join table that has the follwing fields:

       

      fkCommitteeID

      fkContactID

      Office

       

      When adding a new record in the portal, I want to be able to use a drop-down list to choose the contact, which will then insert the ContactID into the join table's fkContactID.

       

      So, my question is, when defining the portal fields, which fields do I include? the ones from the Join table? (I don't want the key fields to actually be displayed, even if they are required...)

      And how can I do a pick list of LastName + FirstName which will correctly a.) populate the join table, and b.) display the contents of the Last Name and First Name fields?

       

      Thanks!

        • 1. Re: Many to Many Portal - Stumped on this classic problem
          usbc

          A quick file to explain. On the Contacts page, just enter a committe for the person to join.

          For simplicity, I just used the committee name as a key.

          For simplicity I used plain lookups in the Membership table.

           

          Chuck

          • 2. Re: Many to Many Portal - Stumped on this classic problem
            LyndsayHowarth

            On the portal to the join table from the perspective of the Committee...

            ...place the fkContactID with a valuelist of the Contact ID is assigned to the field as a popup menu. This valuelist would be where the first value is the pkContactID and the second value is "Full Name" made by a calculation of First & " " Last.. choosing to only show the second value.

             

            On top of that field place the Contact Full name field from the related contacts table. In the Behaviour section of the Inspector... make the field not enterable in Browse mode.

             

            The effect of this will be that when you click on the top field... the popup from the bottom one displays allowing you to choose by name but have the actual id entered. When you commit that record... what displays is the full name.

             

            HTH

            - Lyndsay

            • 3. Re: Many to Many Portal - Stumped on this classic problem
              LyndsayHowarth

              The problem with using the committee name as a Key is that the name of that committee might change...

              - Lyndsay

              • 4. Re: Many to Many Portal - Stumped on this classic problem
                usbc

                "The problem with using the committee name as a Key is that the name of that committee might change...

                - Lyndsay"

                If you re-read my post, I qualified (or at least  tried to) that this was a simple example.

                Sometimes folks ask a question because they are momentarily "stumped" on a concept.

                They are asking for a nudge and eliminating all but the basics of the question can be more helpful than a disertation.

                 

                But you may be right and I should include a list of disclaimers regarding design theory / best practices... Thanks.

                1 of 1 people found this helpful
                • 5. Re: Many to Many Portal - Stumped on this classic problem
                  Stephen Huston

                  The trick to picking the name to populate the fKey ID is to format the selection field as a popup-MENU using a value list which has been set to use 2 fields, the name and the ID, with only the name showing.

                   

                  Picking the name IN THE FKEY FIELD (not the name field) will populate the ID/fKey but will display the name.

                   

                  Not particularly intuitive, and you MUST use the popup-MENU option to enable thsi functionality, which is often less than optimal for viewing the list.

                   

                  A work around many of us have used is to use to relationships and have the ID/fKey as a lookup when the name is selected from any other kind of list. Unfortunately, that necesitates entering redundant data to trigger the lookup.

                   

                  - Stephen Huston

                  1 of 1 people found this helpful
                  • 6. Re: Many to Many Portal - Stumped on this classic problem
                    lkeyes

                    Dear folks...

                     

                    Many thanks for the suggestions.  I've tested a version of Lyndsay's solution, which appears to work; and indeed I wouldn't have originally thought about stacking fields (a technique that I dimly remember from Visual FoxPro when doing reports... yikes!)

                     

                    Anyway, the stacked fields appear to work, and I was able to use a dropdown list.

                     

                    I'm wondering why you both mention the constraint of requiring a pop-up menu? 

                     

                    The dropdown list seems to work better; definitely from the user's perspective in that they are able to type in a letter or two and rapidly search the list (which in my case is extensive). 

                     

                    I'm using FMP Advanced  V.11 for Windows if that makes any difference between the pop-up menus and the drop down list.   

                     

                    The whole business seems a bit of a kludge... especially thinking that it takes just a few clicks to have the same effect in Access. 

                     

                    Again, many thanks to all.   I'll go with this for the moment and see how it works out with th users.

                    • 7. Re: Many to Many Portal - Stumped on this classic problem
                      LyndsayHowarth

                      LOL... this has just been topical for me as I just fixed up a system someone else built where all the keys were based on data that could change. I did take on board your "For simplicity" intro... but wasn't sure if ikeyes had..

                      • 8. Re: Many to Many Portal - Stumped on this classic problem
                        LyndsayHowarth

                        Yes... pop-up vs drop-down....

                        My preference is drop-down too... but with a pop-up they can't accidentally enter something else...

                        - Lyndsay

                        • 9. Re: Many to Many Portal - Stumped on this classic problem
                          lkeyes

                          Just wanted to say that I wrote up this discussion with screen shots... located in a .PDF on this page.

                           

                          http://www.mxdesign.net/styled-3/downloads-2/files/FMPM2Many.pdf

                           

                          I'd appreciate any feedback, on the writeup.    --- L