Hi:

I have a database tracking information about products our architecture firm uses in projects. Right now I have a 'Products' table and a 'Manufacturers' table. I would like users to be able to select a manufacturer via a drop-down list from the products layout, with a button to 'Add a New Manufacturer' that would take users to the manufacturer layout. I created a join table (Product_Manufacturers) to avoid creation of a new records in the Manufacturers table every time someone selected a manufacturer from the products layout.

This works fine, except that on the manufacturer layout I can't pull up a list of products carried by each manufacturer. In the join table, I have the name of the manufacturer (from the drop-down selection on the products layout), the primary key from the 'Products' table (which is a foreign key in the join table), and the primary key from the 'Product-Maufacturers' table. Can someone help me figure out how to get the foreign key from the Manufacturers table to auto-fill based on the fact that I have the name of a manufacturer in this table? My best guess was that once I can the primary keys from related records in the Manufacturers table to show up in the join table, I can figure out a way to pull up related product records from the manufacturers layout.

Or if there's an easier way to pull up related products from the manufacturers layout, could someone let me know? Thanks so much-- I've been pretty good at McGuyvering FileMaker solutions up until now but this one has me stumped!

I created a join table (Product_Manufacturers) to avoid creation of a new records in the Manufacturers table

I am not sure I understand this part. Do you have products that are manufactured by more than one manufacturer?

Rather than a layout screen shot, it would be better to post a relationship graph screen shot; or the entire file (or clone).

But perhaps you're trying to do something like this?

Products.fp7.zip