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!