Best way to lookup related table key fields on import?

Question

I'm regularly importing 5k rows of data into various child tables. The data comes from outside my organization and uses their keys. My parent table uses the default rid as the key field and has all the foreign keys as just plain fields. The foreign keys are a wide mix of numbers or alpha strings or guids etc.

Right now the process I put the data through is to use a pipeline on record create in the child table to search the parent table for a match on the foreign key, return the rid, and update the related field on the child record

As an Example -- my employee, treated like a sales agent by the vendor, has an vendor provided agentid, and an employee ID I provided.

Sale.AgentID Sale.RelatedEmployee -> Employee.RID Employee.VendorsAgentID
ABCD 123 123 ABCD

Pipelines, at that volume, seem to have performance issues - somedays it's several hundred per minute, other days it's 10s a minute.

Is there a better way?

Thanks!

M

------------------------------
Malcolm McDonald
------------------------------

MarkShnierYou · Answer

How about this for an idea.Create a temporary scratch file table to use as an intermediate table when importing. You will need one of these for every different child table that you are ultimately going to populate, so these may be easily created by copying (duplicating) your existing child table. That will create the required fields for you.Import your 5000 rows of data into the temporary table and use a formula query to determine the correct value for Related Parent. Then use a saved table to table import to copy from the scratch table into the real child table.Once you get this working you can then have an admin record which is just a table with one record ID in it where you can hang formula URL buttons.Then you will have a button to clear the  scratch  table.Then you will have a button to import your data into the scratch table (call up the import from file menu).Then you'll have a button to run the save table to table copy to copy the data from the scratch table to your real child table.If you have several different kinds of child table records to populate you organize the buttons on the single admin record so that the process can be really dumbed down and easy to do.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Sign up

Login to the community