None of the above.
As with just about every case with numbered fields, the best practice is to NOT have them.
Instead create a project_manufacturer join table with foreign key fields for IDf_project and IDf_manufacturer.
The Join table that Bruce refers to would look like this:
---< means "one to many"
If you enable "allow creation" for the join table, you can use a portal to the join table to build the links between projects and manufacturers.
A question, should I use the join table for the portal I want on the "Manufacturers" table?
I did this and nothing shows up. Is this because I have more than one "Manufacturer" on the "Projects" table?
You can put a portal to the Join table on your projects layout and use it to create new Join table records that link the current project to different manufacturers. You can put a portal to the Join table on your Manufacturers layout and use it to link the current Manufacturer to different projects. These two uses of a portal to a Join table are not mutually exclusive.
The portal will be empty until you create records in the portal. Starting from a Projects layout, you might do the following:
Put the Join table's match field for Manufacturers in the portal row and format it with a value list of Manufacturers. By selecting a manufacturer, you both create a new record in the join table and link it both to the current project and the selected manufacturer with a single value list selection. (The match field linking it to the current project gets the needed value automatically.) You can add any additional fields from the Manufacturers table occurrence to this portal based on the Join table as you need to show information about the selected manufacturer.
Please note that the one thing that you can't do in this example is add a manufacturer that isn't already in the manufacturer's table. You'd need to go to a layout for that table, add the new record first, then return to the projects layout and select the newly added manufacturer in the portal. There are ways to automate that process.
So existing Projects will not show up in the portal?
Not until you use the join table to link them to the current Manufacturer. If you have a lot of records and an existing relationship, a script can be used to update the Join table.
For existing projects you should create a once off script to do the following:
Loop through all project records, and on each record—
(1) take the ID from id_manufacturer_1 and create a join record
(2) repeat for id_manufacturer_2, 3 and 4
Once you have completed that script you will have upgraded your existing system to the new structure. Then you can:
delete the id_manufacturer_1, 2, 3, 4 fields
delete the script
Thanks for all of the suggestions and help. I must have done something wrong... I'll be back at it next week to try and figure out where I went wrong, I will be away from the office for a few days.
followup after reading all the posts:
yes, create the Join table
yes, put the join portal on the Projects layout detail and you can add Manufacturers
use a drop down, perhaps to select Manufacture ID to add (the foreign key is set)
you can put the related manufacturer name (view only) also in the JOIN portal
yes, put the join portal on the Manufactures layout detail and you can add Projects
use a drop down, perhaps, to select Project ID to add (the foreign key is set)
you can put the related project name (view only) also in the JOIN portal
yes, you can see the Manufacturer and Project in the JOIN table (list view layout) because you can put the related fields (view only) from the two relationships to the respective foreign keys to their parent primary keys
it's called "tunneling data through", even though you ENTER the foreign keys in the join table, the relationship allows the other fields to be shown in the portal or list view from the perspective of that JOIN table.