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
------------------------------

markshnier__you · 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.&nbsp;
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 &nbsp;scratch &nbsp;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------------------------------

malcolmmcdonald · Answer

Thanks Mark -- that pointed me in the right direction.

Before, in pipelines, I had been doing a search on the parent table for the external key, and was then updating the child record with the parent's key ID. 5,000 records would almost always take more than an hour (fun fact: bulk upsert bypasses the 30-minute pipeline run limit). If traffic was bad, 5,000 records could take 3-4 hours or more just to update the keys. Merging from the staging tables to the production tables was never as big a deal - but we needed to do some reasonably advanced jinja to ensure we didn't stumble into race conditions.

Now I'm using a formula numeric field -- eg:
ToNumber(ToText(GetFieldValues(GetRecords("{13.EX.'"&[CPMS_ID]&"'}",[_DBID_PEOPLE]), 3))) where the CPMS ID is the foreign key returning the rid of the people table. -This part of the process to update the key is now near-instant. Huge savings of time, great performance gain!

On the action buttons -- I found a limitation on the new "dashboard" buttons that seem to limit the number of chained actions to two. I can take the same 3 or 4 action buttons and make them into a Formula - URL button on an admin report (great idea, btw thank you for that) .. but it will not work on a dashboard button .. it'll terminate on the second action everytime, showing all the subsequent RDRs in the URL. So for one of the processes, I wanted the button to purge both staging and production (2 actions) then redirect to the import screen for the staging table. Dashboard button with purge both tables, but stop at the screen that shows me the result of the second purge ... the same URL in a Formula - URL will purge both and deliver me to the import screen.

As always, thank you for your guidance!

M

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

markshnier__you · Answer

OK, thank you for the feedback and also thank you for letting me know that the new dashboard don't well support formula URL buttons with multiple redirects.

But I'm thinking about this I'm not even sure how you could have possibly created formula URL buttons on the dashboard because you need to use the formula language to do the URLEncoding. That is why I create a table with one record in it and use that one record as a place to put my various URL formula buttons.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

malcolmmcdonald · Answer

Good question Mark -- yeah, I do the URL encoding directly using a service like: URL Encode and Decode - OnlineWorks really well for up to two actions - but dies at three.&nbsp; I've taken an exact copy/paste into a formula-URL button and can go to three.Thanks againM------------------------------Malcolm McDonald------------------------------

Forum Discussion

Best way to lookup related table key fields on import?

4 Replies