4 Replies Latest reply on Jan 22, 2009 8:39 AM by PatrickHolzer

    Connecting 2 tables without generating duplicate records

    PatrickHolzer

      Title

      Connecting 2 tables without generating duplicate records

      Post

      Hi,

       

      I have a table called "Products" and a table called "Suppliers". (Each product has one supplier, but one supplier can have several products.)

       

      I created a "=" relationship between the two tables, where the match field is "Supplier_Name".

       

      Next, I've created a value list called "Supplier_Name" (this list gets the values directly from the "Supplier" table so it stays up to date) so that I can have a drop-down menu in the "Product" layout where I can assign a Supplier to the Product record easily.

       

      The problem now is this:

       

      Every time I choose a Supplier from the drop-down in the Product-layout, there's a new record being created in the Supplier-table. But I don't want that. I merely want to connect one record at a time with a record from the other table without changing anything about the record.

       

      And if I turn off "Allow creation of records in this table via this relationship" for the Supplier-table, the drop-down menu in the Product-view becomes empty and I cannot assign any suppliers.

       

      Shouldn't it be simple to connect two tables via a unique field? Thank you for your help.

       

        • 1. Re: Connecting 2 tables without generating duplicate records
          ninja
            

          Howdy Patrick,

           

          The field you have on the "Product" layout wouldn't happen to be from the "Suppliers" table would it?  It should be from the "Product" Table.

           

          If you put the field in from the "Suppliers" table into the "Product" layout it would result in the bad stuff you are describing.

           

          The Product::Supplier field should have your dropdown value list based on values in Suppliers::Supplier and should be on the "Product" layout.

           

          Or am I misunderstanding something?

          • 2. Re: Connecting 2 tables without generating duplicate records
            PatrickHolzer
              

            Thank you - it worked!

             

            But I'm not quite happy yet - what I've done is followed your instructions:

             

            1. I created a new value list; in the "Specify Fields for Value List"-box I've chosen the "Product"-table and the "Supplier Name"-field (where before I had Supplier::Supplier Name)
            2. With that value list, I've created a drop-down menu in my "Product"-layout
            That way, assigning a Supplier to a Product no longer produces a record in the "Supplier"-table. So far so good.

            The trouble now is this:

             

            1. In the drop-down menu, only Supplier names appear which have already been assigned to a product at least once; meaning: If I add a Supplier in my "Supplier"-table, it won't automatically appear in the drop-down menu in the "Product"-view
            2. Therefore, in the "Product"-layout, when assigning a Supplier to a Product for the first time, I have to manually enter the exact value (Supplier Name) as it is stored in the "Supplier"-table and the "Supplier Name"-field, or else the "matching field"-trick won't work, and Supplier and Product won't be connected

             

            Desired solution:

             

            It'd be great to have the drop-down updated dynamically all the time so that I can choose from a complete list of Suppliers, not just the ones that have been assigned at least once already.


            • 3. Re: Connecting 2 tables without generating duplicate records
              ninja
                

              howdy Patrick,

               

              Please read again above...

               

              The Product::Supplier field should have your dropdown value list based on values in Suppliers::Supplier and should be on the "Product" layout.

               

              Changing it to the words you used it would be:

              create a new value list; in the "Specify Fields for Value List"-box choose the "SUPPLIER"-table and the "Supplier Name"-field (where before I had Supplier::Supplier Name) 

               

              {In other words, it sounds like you had the value list correct already...I described what you already had and you changed it thinking I meant something else}  Change it back...

              • 4. Re: Connecting 2 tables without generating duplicate records
                PatrickHolzer
                   Thank you, Ninja - now it all works perfectly!