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.