AnsweredAssumed Answered

Foreign key lookup across multiple tables upon import

Question asked by JillianDunic on Jul 31, 2012
Latest reply on Jul 31, 2012 by philmodjunk


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!