AnsweredAssumed Answered

Connecting 2 tables without generating duplicate records

Question asked by PatrickHolzer on Jan 22, 2009
Latest reply on Jan 22, 2009 by PatrickHolzer


Connecting 2 tables without generating duplicate records




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.