Generally, a many to many relationship such as yours is set up like this:
Products::_pkProductID = Product_Manufacturers::_fkProductID
Manufacturers::_pkManufacturerID = Product_Manufacturers::_fkManufacturers.
"Allow creation of records via this relationship" is usually enabled for the join table side of both of these relationships. There is no need to have a Manufacturer name field such as ManufacturerList in the join table as this is redundant. Any time you need that name, you can refer to the name field that already exists in the Manufacturers table.
So make sure your basic structure is set up in this fashion.
Now to answer specifically: "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?"
You've got that a bit backward. What you need to do is select the manufacturer's ID and then the matching name can appear in your layout. On your Products layout, place a portal to Product_Manufacturers and place the _fkManufacturerID field and the Manufacturer name field from the manufacturer table inside this portal's portal row. Format fkManufacturerID with a two column value list. Column one should list values from pkManufacturerID. Column 2 should list values from the corresponding name field in Manufacturers. Now, when you select a Manufacturer from this value list, the ID for that manufacturer is entered and the matching name appears in the field next to it when you exit the field.
Holy hell, you guys are all GENIUSES. I thought I had to set up that 'ManufacturerList' field because it was going to help me pull up the Manufacturer primary keys... but the portal worked perfectly!
Any chance you can help me figure out how to pull up Product data from the Manufacturers layout now that I have the primary keys from both Product and Manufacturers in the join table (as _fkProductID and _fkManufacturersID)? This is the sort of thing that will take me forever to figure out on my own.
Do the same basic process, but with everything reversed. Place a portal to the join table on your manufacturer's layout and place the _fkProductID in it plus any fields from Products that you want to see and edit.
Thank you so much!
That worked perfectly on the Manufacturers layout, but when I tried to copy the chart onto the Products layout the portal only lists the current product being viewed. Is there a reason why the portal wouldn't work on the Products layout?
Why do you list the product in the portal? Since you are on the Products layout, only that product will be listed in the portal, but now you can list multiple manufacturers of that product in the portal.
You should replace that field with a field for the Manufacturer name.
I'm listing the product in the portal as part of a list, products available by the manufacturer. I have two "Other Products by..." portals on the Product layout: one for other products by the designer, which I constructed via a self-join relationship in Products, and one for other products by the manufacturer. That's the one from the Product_Manufacturers join table that I'm having trouble with-- though as I said, this same portal works fine in the Manufacturers layout.
Let's say there are three products in our database by Vitra. Based on the relationship you helped me build, I can access a list of those three products from the Manufacturers layout. But when I go to the Product layout for one of those three products and try to set up the same portal displaying all products in our database by Vitra, it only brings up the current product. I understand why this is happening, but not how to fix it.
First, I understood from your original post, that a given product might have multiple manufacturers. Please confirm that this is the case or we have built more complexity into your system than is necessary (Don't need a join table for then).
Oh dear... no, other way around, a given manufacturer might have multiple products. Your solution works fine (maybe until now) though... I just set up the original portal per your instructions on the Products layout with only one row and no arrows so users would only select one manufacturer.
Yes, I knew that one manufacturer can produce multiple products, but I gather that any given product has only one manufacturer. that means we can change your relationship graph from
Manufacturers::::_pkManufacturerID = Products::_fkManufacturerID
Then we can add a new table occurrence of products, ProductsSameMfg to get:
Products::_fkManufacturerID = ProductsSameMfg::_fkManufacturerID
A portal to ProductsSameMfg will list all products with the same manufacturer.
To create ProductsSameMfg, open Manage | Database | Relationships, select Products, then click the button with two green plus signs. This creates a new "box" on your relationship graph that refers to the same data source table as does Products.
I don't think that's going to work... the reason for the join table in the first place was so that I can select a manufacturer from the Products layout without creating a new record in Manufacturers. When I had the relationship set up like you recommend, I had to turn on the 'create new record' option for Manufacturers to get the drop-down menu for Manufacturers to show up in the Products layout... and then FM would create a new record in Manufacturers each time I selected a manufacturer.
Not if the field is defined in Products. That would only happen if you've added the manufacturer ID field from Manufacturer to your products layout.
Not if what field is defined in Products?
The field you set up with the drop down menu of manufacturers. If this field is a field defined in Products, there is nothing going on that could possibly create a new manufacturer record.