Problems making my own array to pass to bulk insert query

Queries and Resources
,
1

I am trying to do a bulk insert query, but I need to make my own array of records to insert and I am having trouble getting this all wired together correctly.

I have two table components that I need to pull data from to create a new array to insert into a third database table. The first of the tables is multiselect so .selectedRow.data is an array.

I fire a js query from a button:

//jsAddProductsToTemplate
jsMakeBulkInsert.trigger()
qryTemplateItemsBulkInsert.trigger()

This runs jsMakeBulkInsert first which makes the array and stores it in a temp state var and then fires the SQL query.

// jsMakeBulkInsert
   var newData = [];
   tblProducts.selectedRow.data.forEach(row => {
       newData.push({sku: row.sku, template_id: tblTemplates.selectedRow.data.template_id})
   });
   BulkInsertRows.setValue(newData);

The sql query then just looks like:

// qryTemplateItemsBulkInsert - Array of records to insert
{{BulkInsertRows.value}}

However, BulkInsertRows is getting set after qryTemplateItemsBulkInsert is triggered.

Browser console:
[onActionButtonClick] running an action jsAddProductsToTemplate
app.ab4664be047c4caa9cbf.js:2 JSCode triggering jsMakeBulkInsert {additionalScope: {…}}
app.ab4664be047c4caa9cbf.js:2 JSCode triggering qryTemplateItemsBulkInsert undefined
app.ab4664be047c4caa9cbf.js:2 Setting value of  BulkInsertRows to (2) [{…}, {…}]

I first tried using the return value in jsMakeBulkInsert like this:

//jsMakeBulkInsert
... Same as before
return newData

and

// qryTemplateItemsBulkInsert - Array of records to insert
{{jsMakeBulkInsert.data}}

But that worked sometimes and not others and when it didn't I got an error on the order of cannot use .map in undefined.

If there is a way to force the temp state var assignment to happen when you think it will, that would solve the problem I believe.

Or is there another best practice for this?

2

Hey @bradlymathews! So the quick answer here is that you should probably do:

jsMakeBulkInsert.trigger({
  onSuccess: {
    qryTemplateItemsBulkInsert.trigger()
  }
})

Temp state in Retool can be a bit wonky with timing - the most reliable way to make sure things happen in order is to use this onSuccess hook. LMK if this works!

1 Like
3

@justin

I m encountering a few more cases where I need to create my own array to pass to a bulk query. My current method, described above uses three queries to accomplish this.

The first js query I’ll call the quarterback, or QB, query which starts the process by calling the js function that actually builds the array and stores the results. The QB then waits for onSuccess and calls the SQL query to do the actual work.

Surely there is a better Retool-ish pattern for this?

4

@bradlymathews this is a pretty normal Retool pattern! Is there another type of architecture / idea you had in mind?

5

Well, If I could call the array builder directly from the SQL query that would make things easier, more understandable and therefore more maintainable.

However, in order to get the return value of a js query, you need to trigger it first so you are stuck with a two step process.

Maybe you add another method called .triggerWithReturnValue() that triggers the query and also sends back its return value? This would make it act like a traditional JS function.

Maybe that’s the feature you need to add which would have far reaching implications. Seems as if all js one writes in Retool-land is a “query” and not a real boy js function. Could you allow us to write actual JS functions and have full hooks into Retool-land?

Or maybe I am not understanding the js/Retool relationship yet?

6

Another option that is a bit easier to reuse values from inside of a single Run JS Code query is an async await function like this:

2104Ă—708 64 KB