6 Replies Latest reply on Aug 8, 2012 4:14 PM by JillianDunic

    Lookup foreign key after data import

    JillianDunic

      Title

      Lookup foreign key after data import

      Post

      I have two tables. One contains the names of species (Taxonomy Table), the other contains a list of information about length and weight calculations (Length.Weight Table).

      In my Taxonomy table I have imported all of the information, so species names and codes. In my Length.Weight table I have also imported all of the information. Now I am trying to fill in my Taxonomy::fk_LengthWeightID with the LengthWeight::pk_LengthWeightID values that correspond to each species.

      I have tried adding a second column of Taxonomy::SpeciesCode2 and then do a calculated replace to fill it in with the fk_LengthWeightID but that just gave me blanks in that field for all of the records.

      I have also tried to set up lookups and then ‘relookup’ but for the fk_LengthWeightID but I just get blanks.

      My current relationships are

      Length.Weight::pk_LengthWeightID = Taxonomy::fk_LengthWeightID

      Length.Weight2::SpeciesCode = Taxonomy::SpeciesCode

      So far I have been able to do these lookups upon import but now that I am trying to match up these two tables that already have data in them I’m a bit confused.

       

      Using: FileMakerPro 12

        • 1. Re: Lookup foreign key after data import
          philmodjunk

          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?

          • 2. Re: Lookup foreign key after data import
            JillianDunic

            I was thinking it would be like this:

            Taxonomy TableLength.Weight

            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.

            • 3. Re: Lookup foreign key after data import
              philmodjunk

              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?

              • 4. Re: Lookup foreign key after data import
                JillianDunic

                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

                Length.Weight

                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

                • 5. Re: Lookup foreign key after data import
                  philmodjunk

                  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.

                  If so....

                  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:

                  LengthWeight::Pk_LengthWeightID

                  • 6. Re: Lookup foreign key after data import
                    JillianDunic

                    Thanks for your patience and help :)