Bulk update from single value

App Building
, ,
1

Hi

We're now to Retool and have just started playing in readiness for building a few solutions for our internal business units. We've been testing the new Table component and have got a snag which we can't seem to find a direct solution for in the docs, forum or video tutorials.

Our use case is that we have data which comes from third party platforms into our staging database. Some records need manual review to ensure the transactions coding is correct. We have got this working fine for a single record.

What we're struggling with is how to update a number of selected records with a single value aka, update these x number of records with this source code. We're trying to use the "Bulk update via a primary key" and are using the "table.selectedrows" array to get the selected row but we can'r figure out how to update x field within the selected rows for the target database table with the entered value.

Could anyone point us the right direction please?.. thanks..

image
image1128×620 91.4 KB

2

Hello!

We encountered a similar issue for filling in multiple entries with a single data point. There are definitely MUCH better ways to implement the solution here but what we landed on was a javascript query to loop through the row selections and trigger an update/create to our table as needed. This was mostly because there was necessary logic for determining how to handle new vs update entries.

Here's the query without modifications:

let updates = fillInfoXfrm.value;
for(let i=0; i<updates.length; i++){
  if(jobCostModuleMappingQuery.data.PrimaryKeyValue.findIndex(x => x == updates[i].PrimaryKeyValue) >= 0)
  {
    jobCostModuleMappingUpdate.trigger({
      additionalScope : {
        update: updates[i]}})
  }
  else
  {
    jobCostModuleMappingInsert.trigger({
      additionalScope : {
        update: updates[i]}})
  }
}

Here, fillInfoXfrm is a transformer which is handling the mapping of data for two different tables (which could both have the single data point updates addressed). Here's the basic breakdown with my custom mappings redacted:

image
image483×703 27.3 KB

At the end we return the final array, filtered by unique primary key.

The loop processes all of the selected rows from both tables one at a time which isn't great for overhead but allowed us to insert some branching logic along the way.

The button triggers the loops, which is reading the actively transformed data that is updated after the row selections are made. The loop branches to the proper single entry query to update or create the new mapping data.

4

Thanks @pyrrho

Before checking the reply we'd almost got it working with SQL as follows (our DB is Snowflake for info)...

UPDATE
"PROD"."RETOOL"."TABLE_TO_UPDATE"
SET
SOURCE_CODE = {{ txtBulkNum.value }}

WHERE ID IN
({{ table.selectedRowKeys }})

We're getting a "SQL compilation error: Unsupported data type VARIANT" error when this runs however, when we hard code the SOURCE_CODE...

SET
SOURCE_CODE = 123

it works perfectly... any idea why it's not liking the{{ txtBulkNum.value }} which is a Number Input component?!..

5

Have you tried to force the number type in the SQL query with AS_INTEGER?

https://docs.snowflake.com/en/sql-reference/functions/as_integer

6

Thanks again @pyrrho

We've checked the table and the SOURCE_CODE data type is actually "VARCHAR" and have tried with a Text Input Component also and with AS_VARCHAR but still the same issue?!.. v.odd

7

Hrm. Does adding in a declaration step help at all?

Something like:

DECLARE @BulkValue = AS_VARCHAR({{txtBulkNum.value}})
UPDATE
"PROD"."RETOOL"."TABLE_TO_UPDATE"
SET
SOURCE_CODE = @BulkValue
8

Nope, same error..

10

Okay, I've gone back to the beginning and ran..

UPDATE
"PROD"."RETOOL"."TABLE_TO_UPDATE"
SET
SOURCE_CODE = {{txtBulk.value}}

WHERE ID IN
({{ table.selectedRowKeys }})

which I get the error, within the console state it's showing this SQL as the query output..

"UPDATE "PROD"."RETOOL"."TABLE_TO_UPDATE" SET SOURCE_CODE = 445566 WHERE ID IN (1,2,3)"

which runs okay when I run it directly within the SQL console directly within Snowflake?!.. even with AS_VARCHAR added..

Seems to be something odd with retool?!..

11

I wonder if this has more to do with the IN clause.

  1. You can try to use
    WHERE ID = ANY({{ table.selectedRowKeys }})

  2. This might be related to the prepared statements option on the resource. Disabling prepared statements might get it working, but at the cost of higher risk of queries borking the whole DB.

1 Like
12

Using ANY() didn't work either but Disabling prepared statements did.. Thanks for your assistance...

2 Likes