Auto populate foreign key
I have a database that is tracking an item through a process. Item fields are imported from a spreadsheet from a flat database format. On that import is the item number, the different places it will visit in the process and a current location code. I am trying to link the places the item will visit to the list of locations it could visit with a join table, itemID to Location ID through the following:
fkItem to fkLocation
I am able to auto populate the item foreign key using a set field script step but not sure how to go about populating the other foreign key automatically. Do do this manually will be very tedious and so would like to make this automatic.
Now to make this more complicated, there is a current location code that will change as the item moves through the process. I need to tie that current location code (from the item table) to the location it is visiting in the Locations table.
I am probably missing something very simple here, and I can sort of make this all work by manually populating the foreign keys through pop up menus but that will take all day with over 1000 items.
Any help is most appreciated.