Forum Discussion

Qrew Cadet

3 years ago

Best way to lookup related table key fields on import?

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

Data import and export

MarkShnier__You

Qrew Legend

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

MalcolmMcDonald

Qrew Cadet

3 years ago

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
Qrew Legend
3 years ago
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
  Qrew Cadet
  3 years ago
  Good question Mark -- yeah, I do the URL encoding directly using a service like: URL Encode and Decode - Online
  
  Works really well for up to two actions - but dies at three. I've taken an exact copy/paste into a formula-URL button and can go to three.
  
  Thanks again
  
  M
  
  ------------------------------
  Malcolm McDonald
  ------------------------------