If you were to print out the data in the two tables on two sheets of paper and lay them side by side, how would you tell which record in your LengthWeight table matches up to which Taxonomoy record?
I was thinking it would be like this:
Species Afk_LW =11Parameter X
Species Bfk_LW =22Parameter Y
Species Cfk_LW =33Parameter Y
Looking at my Taxonomy table I would know what Length.Weight information to use. As the users will want to know for a given species, what values to use.
In case of any confusion, I only added the LengthWeight::SpeciesCode to try and figure this out. In the end I hope to get rid of that field and have it just be through Taxonomy that we lookup the Length.Weight information.
Let me try again.
Immediately after importing the data, you don't have any way to connect a record in taxonomy to a record n LengthWeight. That requires a common value in both tables, the primary key field in one and the foreign key field in the other. Once you have matched primary key to foreign key values, then you have something that works, but the key values can't be imported as they are generated in the LengthWeight table as I understand things from your original post.
You need a way to be able to match a record in LengthWeight to the correct record in Taxonomy BEFORE you can assign matching foreign key values. I'm looking for data that can be used for that purpose. Without it, there is no way to link your data even if you went down the list and manually entered key values one record at a time--you wouldn't know which value to assign to a given record.
What form does your data take before you import it? Is it a single file and you import some columns into taxonomy and some into LengthWeight or do you have two separate files of data to import?
Sorry I misinterpretted what you asked. I have two separate files of data. However, both tables have a SpeciesCode.
Taxonomy Raw Data:
FieldA FieldB FieldC SpeciesCode
ParamA ParamB SpeciesCode
Where both SpeciesCode fields are the same.
I think the logic that I want is something like when LengthWeight::SpeciesCode == Taxonomy::SpeciesCode, set Taxonomy::fk_LengthWeightID as LengthWeight::pk_LengthWeightID
I imagine that you currently have this in Manage | database | relationships:
LengthWeight::pk_LengthWeightID = Taxonomy::fk_LengthWeightID
That looks backwards to me, but that may just be my ignorance of how these two tables need to work. I would think that you have just one Record in Taxonomy that links to a one or more records in LengthWeight. In which case the fk and pk designations need to be swapped. I'll assume that they are correct for this post. In which case all newly imported records in Taxonomy need to receive the correct matching value in the fk field from the PK field in the correct record in LengthWeight.
In Manage | Database | relationships, make a new table occurrence of Taxonomy by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as TaxonomySpeciesCode.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
LengthWeight::SpeciesCode = TaxonomySpeciesCode::SpeciesCode
Add a layout based on TaxonomySpeciesCode and put the Fk_LengthWeightID from TaxonomySpeciesCode on the layout.
Pull up a found set of all records where there is no value in fk_LengthWeightID. (enter fiind mode, put an = in the field and perform the find.)
Click in the field and use Replace Field Contents with this calculation to copy over the matching PK values:
Thanks for your patience and help :)