6 Replies Latest reply on Nov 21, 2012 5:37 PM by ziceg

    Help bogged down on silly relationship

    ziceg

      I'm getting myself bogged down on a silly relationship... I'm stuck and need someone to help me see what I'm doing wrong. Please....

       

      I've got this database where I have Kits which are made up of various Trays. I can select in a Portal the trays I need and also go to the correct Tray Form to see more info.

       

      My problem is that when I'm in the Tray Form I'm supposed to able to see a list of which Kits use that particular Tray and go back to that Kit by clicking on portal row. I'm trying combination after combination of relationships but can't make the list of Kits appear in the Tray portal.

       

      I'm attaching the file as I bet it's dead easy and it'll be one of those Doh moments but I'm stuck.

        • 1. Re: Help bogged down on silly relationship
          Mike_Mitchell

          Hello, ziceg.

           

          What you're describing is a many-to-many relationship. These are normally implemented using a join table, an additional table that sits between the two related tables and contains the two key fields (in this case, the key for Kit and Tray). What this will do is convert your many-to-many relationship into a pair of one-to-many relationships.

           

          To manage your join records (the records in the join table), it's often beneficial to use a script that captures the key field of the current parent record (either Kit or Tray) and then present the user with a list of potential records from the other parent table to select from. Then, insert both keys into a new join record.

           

          Doing it this way allows you to see all related records from either parent. You also get a lot of flexibility in adding data elements to particular combinations of parent records.

           

          HTH

           

          Mike

          • 2. Re: Help bogged down on silly relationship
            ziceg

            Thanks Michael, I now understand the principle behind it but still can't get it to work, it's frustrating the hell out of me, I keep trying many different combinations of keys, fields, tables, relationship, anything.... and still not working for me

            • 3. Re: Help bogged down on silly relationship
              Mike_Mitchell

              I've attached a modified version of your form with a very basic demonstration. Check out your forms for each of the parent tables.

               

              If your users are savvy enough, you can get away with a drop-down list of options in the join table, as I've done. I usually don't; I prefer to present a list of options that doesn't show them the key field. But this will allow you to see how it works a little easier.

               

              HTH

               

              Mike

              • 4. Re: Help bogged down on silly relationship
                ziceg

                Hi Michael Thanks so much for helping me.  I tried to apply it on a brand new simplified version to ensure I understood it correctly and it's like one step forward one step backward . I thought I was there but no go and not sure why.

                 

                Attached is new version

                • 5. Re: Help bogged down on silly relationship
                  Mike_Mitchell

                  ziceg -

                   

                  Your structure is correct. Your value list was wrong. You were inserting the tray name into the key field rather than the tray ID. Hence, there were no matching records. I corrected the Tray ID value list and created a couple of records so you can see the difference.

                   

                  Mike

                  • 6. Re: Help bogged down on silly relationship
                    ziceg

                    Thanks you soooo very much, it's now all working (even though I would prefer not show the Record ID, but I can live with that).  Sorry been without internet for a few days.