1 Reply Latest reply on Jul 31, 2012 12:48 PM by philmodjunk

    Foreign key lookup across multiple tables upon import



      Foreign key lookup across multiple tables upon import



      I have set up a database for a very large set of data currently housed in spreadsheets. I am now beginning my data import.

      I have two tables. Taxonomy and Guild. I have a spreadsheet that contains a list of ScientificNames and a list of GuildCodes. In my Guild table, I have input all of the values for GuildCodes and pk_GuildID as there are only 10 possibilities. I need to import my spreadsheet into Taxonomy and I need the appropriate fk_GuildID to match up with eacn ScientificName imported. However I cannot for the life of me figure out how to do this lookup. 

      I have set the Taxonomy::fk_GuildID as a lookup to the table, but I do not how to tell fmp to get this fk_GuildID by checking the GuildCodes in the spreadsheet against the Guild::GuildCodes field. 

      I could go through and manually change the GuildCodes in the spreadsheet into the corresponding fk_GuildID values but my concern is that this is supposed to be my most simple import and so I need a solution that will for 15 more spreadsheets of data that belongs in multiple tables. I am also keeping in mind how we may possibly add to this dataset outside of filemaker pro forms.

      Thanks to anyone who can help!

        • 1. Re: Foreign key lookup across multiple tables upon import

          When the project is to import large blocks of data and then link them by internally generated primary keys such as your GuildID number, the trick is to create a second relationship that matches by some other value, then the Taxonomy::fk_GuildID can use this second relationship to lookup a value from GuildCodes::pk_GuildID so that you have the values in place to make the GuildCodes::pk_GuildID = Taxonomy::fk_GuildID relationship functional.

          In Manage | Database | relationships, make a new table occurrence of GuildCodes 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 GuildCodesByName.

          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:

          GuildCodesByName::TaxonomicName = Taxonomy::TaxonomicName

          You can now pull the ID code from GuildCodes across this relationship into fk_GuildID in one of two ways:

          1) Set up Taxonomy::fk_GuildID with a looked up value or auto-entered calculation field option to copy GuildCodesByName::pk_GuildID into the field.

          2) Immediately after import, use Replace field contents to popuplate the GuildCodes::fk_GuildID with values from GuildCodesByName::pk_GuildID.