One to multiple records problem
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!!
I think you need to go in and edit your post and re-post (maybe even a couple of times), to get it to appear correctly.
YOu should have a factory table. One record for each factory. A field in this table would record the email address.
If you have a relationship such as:
Factory::FactoryID = Model::FactoryID
Then you can place Factory::EmailAddress on a layout based on Model and it will display the email address for the factory linked to that Model record.
Create a separate table for Factories, and put their contact details in there. Then link the Models Table to it using a FactoryID.
The fields look like this:
Factory Model Email
A a1 a@gmail.com
A a2 a@gmail.com
A a3 a@gmail.com
B b1 b@gmail.com
B b2 b@gmail.com
B b3 b@gmail.com
Now I need to enter same email address under every model, to make Email box on Model layout demonstrate the factory email.
I want to fill email address only once under every factory. How can i achieve this?
I was thinking this way too. while I got dozens of factory by hand, and I do not have a file contains one record for each factory for me to import. Is there a method to generate this factory table from my original table? Or I need to enter factories one by one.
Now I got an add script on table Model, in furture, If I need to add new factories, how can i add to the factory table through this model table?
Thank you!!
You can define the table, Set a Unique Values, validate always falidation on your factory (name?) field. Then import from Models mapping the two factory fields. If you also define an auto-entered serial number field and enable auto-entry options during this import, you'll not only get one record for each unique factory name but generate serial ID numbers that will be a better choice for using in relationships than the factory name. (Like people, the name of a factory can change...)
Phil coule you please explain more about define the new table and set unique value? Sorry I did not get how to import or generate factories from my existing Model table.
Do you mean add auto-entry data to get serial number in new Factory table?
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.
Thank you so much for your thorough explain Phil 
!!!!
Seems I did not get the adding more factory part.
Now I add factory or model in Model table, and I have created a Factory table with factoryId number. While in the original table, there's no factoryID number recorded.
And If I want to add factory to Factory table through entering from Model table, how can I do that?
Thank you!!
You'll need to copy over the FactoryID into a factoryID field you add to Model.
Define this relationship to start:
Factories::FactoryName = Model::FactoryName
Add a FactoryID field to Model and place it on a Model layout. Then do this:
Show All records
Put the cursor in the FactoryId field and select Replace field contents
Select the calculation option and use this calculation:
factory::__factoryID
To copy over the matching FactoryId number.
Then you can change your relationship to be:
Factories::__pkFactoryID = Model::_fkFactoryID
Thank you Phil!!
I have a follow up question,
What if I want to add a new model, and the facotry is already on the list? If I use New/Request script, this will add a record for both sides, and in Facotry table, Id field will generate a new id for this record.
How can I avoid getting more than one id under one facotry?
New Record/Request only creates a new record in the current table.
If you choose new record while on your model table, you get a new model record, but no change occurs in Factories. You can then use a value list format on the _fkFactoryID field to select a factory for the new model record.
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.