Best way to lookup related table key fields on import?

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