Pipelines - Repeat Add record X times
Hi All,
I inherited a database and still wrapping my head around pipelines. I am trying to find an efficient way of repeating a task x times (based on a qty field number). The basics are listed ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendHere is my two cents worth. Well you could go through the gymnastics to coax a pipeline into looping X number of times, it will be slow to add the records particularly if you were talking about creating "many 100's of records". Looping pipelines also ran the inherent risk of a programming error leading to an infinite loop.
So one solution is to create a helper table where are you upload from excel with say 1000 records in it or for that matter 10,000 records in it. Whatever the most number of assets you would ever need it to be created at one time. That table would have a single field in it which would be the number from one to 1000.
The pipeline could initiate and create a Bulk Upsert and search that table for any records less than or equal to X where X is the number of records that need to be created. Then you do your For Each loop to add a record to the Bulk Upsert and commit the upsert.
That will work but in my experience records are only added to the bulk upsert at the rate of somewhere between five and 10 per second. So if it was even 10 per second which I doubt it is, then that would take 25 seconds to create 250 records. 25 seconds is a very long time for a user to wait around to see if the records got created.
So another possible plan is to once again have that same helper table but have an additional table with only one record in it. It will have record ID equals one. The pipeline would populate that single record with any information needed to create the asset. Then that information would be locked up into the thousand record helper table.
Then rather than building a bulk upsert which adds records relatively slowly, you would have the pipeline call the API to run a table to table import, which would import the records all at once.
Lastly, there is a new feature in pipelines in the last few months under Bulk Record Sets called Copy Records and I hear it is super efficient and quick. So rather than running a saved table to table import you could use Copy Records and not have to worry about the syntax to call an API. I suspect that under the covers copy records and a saved table to table copy are probably the same thing.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Hi Mark,
Thanks for your reply. I've been trying to implement what you mentioned. I could probably get away with option 1 as the vast majority of the time it will be only adding 1-20 records at a time. It's only a rare occasion it will need to be able to handle in the hundreds. I tried the copy records too which seems to copy but having issues with the for loop condition. When doing other coding and For loops, there is normally a variable with the count iteration for the loop - doesn't something similar exist with pipelines so you could do a For loop iteration number <= quantity DO copy record"?
I set up a helper table as suggested but having troubles working out the syntax to use it.
The current steps I have been testing are (which adds 1 record):
A - Record Created (Triggers on the source table action)
B - Prepare Bulk Record Upsert
C - Search Records (Searches on source table to capture data). Query of Record ID = {{a.id}}
For Each loop contains
D - Add Bulk Upsert Row
End of For Loop
E - Commit upsert
I wasn't sure about implementing the helper table (or iteration count if possible). With a helper table my thinking is I need to insert an extra Search Record step between Steps B and C with an advanced query? How would I read the counter value to compare in the advanced query? {{b.Counter}}LTE{{a.Qty}}?
I did also try to create the second temp table with one record which is fine. The copy record function seems to be able to copy this record to the final destination table but again stuck on the loop query syntax to replicate x times.
Thanks.
------------------------------
Grant K
------------------------------
- MarkShnier__You
I think these would be the steps
I think your search step is on the wrong table.
A - Record Created (Triggers on the source table action) Correct
B - Prepare Bulk Record Upsert Correct
C - Search Records (Searches on source table to capture data). Query of Record ID = {{a.id}} Incorrect - you want to search the helper table with a filter that the helper record counter number is less than or equal to the # of records required from step A.
For Each loop contains
D - Add Bulk Upsert Row correct
End of For Loop
E - Commit upsert correct
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------Thanks for your assistance. Finally got it working. There is a noticeable delay when adding hundreds of records but since that is a rare situation it should be acceptable.
The advance query ended up being a simple:
{6.LTE.{{a.job_qty}}}
------------------------------
Grant K
------------------------------
