It would be a good idea to add a Company ID field that's an auto-entered serial number in Companies and then replace ProductCompany with a matching number field that you link in a relationship like this:
Companies::CompanyID = Products::CompanyID
There are several reasons for this:
1) Company names aren't really unique in every case
2) Companies change their names
3) If you enter a company name incorrectly and don't discover the error immediately, attempting to correct the error can "break" the connection to any related records in Products as they still have the original, incorrect name.
The ID field avoids those issues. The ID field can be formatted with a drop down list that shows the ID number in column 1 and the company name in column 2 so that you can still select companies by name.
Given the ID field, You'd need a "new company" button that takes you to the Companies layout for you to create the new record.
There are ways to have a text field that's used as a search field (and lists company names in an auto-complete enabled drop down) and a script can then be performed via script trigger on the field that finds the record by name and then enters the matching ID number back into your original products record. This script could include code that creates a new record in Products and enters the company name you typed in if no matching company name record was found.
Hm, ok, well If I have a list of 100 companies (as I do or more), how can I assign a serial number to all of them without having to manually type it, because if I create a auto serial number field, it only starts doing that when I create new records, not when I already have them in there
For existing records, after adding the serial number field option for new records, you can go to a layout (or make one) where you have the ID field visible. Put the cursor in the field and use Replace Field Contents with the serial number option to both add serial numbers and update the next serial value setting so that the next new record will get the next value in the series.
Ah nice, thanks again PhilModJunk!