Is there any data in Model other than the empty _fkFactoryID field that will correctly match to only one record in the factory table? If there is, then you can use that field in a relationship to copy over the primary key value of the correct factory record.
I think there is not, unless factoryName can be used?
The current relationship between the two tables is
Factory::_pkFactoryID = Model::_fkFactoryID
The value list for the pop up has two fields, the first is Factory::_pkFactoryID and the second is Factory::factoryName.
If the factory name is unique, it could be used in a supplementary relationship so that you can get the needed factory ID. The main requirement is that somehow, FIleMaker has to "Know" which factory record has the right ID for a given newly imported model record.
Two common options:
- Import model records only for one factory at a time, then a script or replace field contents can be used to update the found set of newly imported records to link them to the same correct Factory ID.
- Import records from multiple factories in one import, but then use a field in the imported data, such as the factory name, to match up records to the correct Factory record, then an auto-enter calculation or replace field contents can be used to copy over the correct ID into each newly imported record.
I tried making a smaller two table database with only the primary keys, foreign key, factoryName and modelName fields to try to work out a solution to this problem and found that after importing into Model::_fkFactory, Factory::_pkFactory is automatically set. I do not understand why it works correctly on this small sample database and not the original one.
Nor do I from what little you have described here about your trial database.
Note that I said that you would need a "supplementary relationship" if you were to import the factory name. By that, I meant that you'd link a new Table Occurrence of Factory to your Model table and specify that the factory name fields be used as the match fields in this relationship. Then Replace field contents can be used to copy over the ID number from this new occurrence after import or you can set the _fkFactoryID field to autoenter the __pk value from this new occurrence of Factory provided that you enable auto-enter options during the import.
I tried using Replace field contents in a script but I am not writing it correctly. When I run the script it just clears the _fkFactoryID from all of the records in the model layout.
Replace Field Contents[ model::_fkFactoryID; factory 2::_pkFactoryID]
Was I suppose to set variables or use if statements? I tried both but it did the same thing where it just clears _fkFactoryID.
Your replace field contents step and your relationship appear to be correctly set up. The _fkFactoryID field shouldn't have any data yet so it is not clear why it is "clearing" a field that should be empty in the first place, but what you report suggests that the text in Model::FactoryName does not match any record in Factory 2.
If you place the Factory 2::FactoryName field on your Model Layout, do you see a factory name? This relationship will only work if the text in Model::FactoryName and Factory 2::FactoryName matches.
Thank you Phil for clearing up my confusion. It works perfectly now :)