Title
One to multiple records problem
Post
I have a table Model, with fields Factory, Model and Email
Because One factory produces more than one model in most cases, I got multiple records with same factory code while differenct model number. And every factory has one email contact, my question is:
How can i enter email address only one time for each factory, and I can demonsrate this email address associated with factory on table Model?
Thank you!!
Define a new Factory table. To do all that I have suggested requires at least three fields in this table: __pkFactoryID, FactoryName, Email
Define __pkFactoryID as an auto-entered serial number field.
Set factoryName to be Unique values, validate always. these are options on the validation tab in field options.
Go to a layout based on your new table.
Select Import Records | File from the file menu
Select the same file you are already working with as the file from which to import data.
Select the model table as the source table.
In the field mapping dialog, match up FactoryName in the Factory Table to the field in Model that stores the factory name.
A small dialog box pops up just before you start the import that asks if you want to enable auto-entry options. Select this check box.
When you import the data, the Unique values, validate always settings will prevent the import of duplicate factory names and this will thus load your table with one record for each factory named in your model table.
The auto-entry option will ensure that __pkFactoryID will enter a serial number for each new factory record.
At this point you can then start adding fields and building relationships to link the Factory table to other tables by __pkFactoryID. This will typically require linking a table to Factory first by Factory Name, then you add an _fkFactoryId field to the linked table and use Replace Field Contents to copy the ID from the matching factory record. Then you redefine the relationship to match by factory ID and you can then replace each use of the factory name in that table with a reference to the name field in the Factory table and then you can remove the factory name field from the related table.