Pipelines - Repeat Add record X times

Question

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 below:
Tables - Task, Parts, Assets
Task Table - Where job is generated. User selects The "part number" and Quantity of the partParts Table - List of available part # and Part Name
Assets Table - List of assets.  Has Related Task #, Related Part #, Related Part Name, Asset  Number, Serial NumberA user creates a new Task. They enter the Part # and the Quantity of that partI want to create new Asset records based on the quantity number entered. e.g. Quantity = 10 equals adding 10 new Asset records.
As the quantity can be many hundreds, I'm looking at an efficient way to creating the multiple records. 
I'm thinking I can use a bulk records, but being new I'm still not sure if I need to use a For loop somehow or if there is a better way? It is currently setup as a single pipeline for each record add. i.e. to handle adding 10 assets I have 10 separate pipelines. This is not practical for large quantity adds.
Any assistance would be greatly appreciated. Thanks. ------------------------------Grant K------------------------------

MarkShnierYou · Answer

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

Sign up

Login to the community