How to update multiple rows using a multi select table

[Terry]

Hi,

I am really new to retool and having some trouble updating multiple records based on the user selecting multiple rows in a table.

I have created a table that displays data from my database and have successfully updated 1 row. I would like to be able to update multiple rows but unsure how to set this in the GUI Mode.

I've enabled multiple rows to be selected in the table but not sure how to reference this in the IN clause.

This works for 1 row:

How do i update this for multiple?

Thanks

[jocen]

Hi @Terry, welcome to the community!

You can hover on the {{}} code to view what kind of data you are passing as argument in the GUI filter. You can also open the left panel (CMD+B) to view the data structure of your components and resources so you would know how to properly refer to them. Some screenshots on your table structure would be helpful to pinpoint on how you are accessing the array of ID.

I'm assuming you are using a Google Sheet Resource here, might want to check up on how to bulk update it. With the resource you are using now, it seems it will update all the specified ID by the single value you place and not dynamically update the value for each ID. If this is your use-case, then it should be fine.

Regards

[Terry]

Hi,

Thanks for your prompt reply.

I'm using an oracle DB as a resource and populating the table based on a select statement.

When i don't have multiple rows selected for the table and I hover over the resource I get the ID.

When I enable multiple rows and I hover over the resource I get 'undefined' Is there a different way of selecting the data when multiple rows are selected?

Thanks

[jocen]

Hi @Terry,

I see. You can hover on the TransactionTable.selectedRow.data and it'll show you that it has a structure of Array<object>. What you want is to iterate over TransactionTable.selectedRow.data to provide you a list of ID. You can do either of the following:

1. {{ formatDataAsObject(TransactionTable.selectedRow.data).ID }}
2. {{ TransactionTable.selectedRow.data.map(x=>x.ID }}

Either of those should work on your filter argument. Lemme know how you go with that

[Terry]

Hi,

Thanks for your suggestions. Think i'm understanding this better now.

I have tried both of those options and get errors.

Is there a syntax error?

Thanks

[jocen]

@Terry, try putting space in between the code and the brackets.
i.e.
{{ formatDataAsObject(TransactionTable.selectedRow.data).ID }}

[Terry]

Hi,

That worked thanks for your help.

When I use this in SQL mode i get the following error:

Trying to pass in the selected ID's to populate another table.

Can this only be used in GUI mode?

Thanks

[jocen]

Hi @Terry,

That error code is an Oracle DB specific error. It's basically saying that's the wrong syntax. I'm not familiar with Oracle DB so you might want to read up on their documentation re: Working with Arrays.

Something I find similar is the Postgres syntax on this cheatsheet. You might want to try that syntax if that works for Oracle DB.

[Terry]

Thanks for the links will take a look

[mondob]

Hi,
I have the same question, but for some reason, the solution @jocen suggests does not work for me.
I have a table (new table component) and want to add a timestamp to all selected rows. I'm trying to use this code: {{ formatDataAsObject(emailDuplicatesTable.selectedRow).id }}
I do not use .data to access tables selected rows based on the documentation provided for the new table component, but I've tried to do this with .data as well.

When I add .data the error I get:

Am I missing something here? Could there be an issue with the code or my understanding of how to access the selected rows? Any guidance or suggestions would be greatly appreciated.

[ScottR]

Seems that you are trying to update multiple record so use Bulk Update via Primary Key

[mondob]

I'm not sure does that work, because I only want to add a timestamp to the column manually_deleted for the rows that are selected. Users do not edit any rows manually.

I see that the problem that I mentioned before was because I was using .selectedRow and not .selectedRows.
I fixed this, but now I get null value rows for some reason even if there are no empty rows in my table. Why these rows are being shown?

Bigquery does not allow updated data if there are any null value rows.

[ScottR]

You still need to use BULK Update if you are updating multiple rows in a table in the db

[mondob]

I'm trying this, but it still returns these 3 empty arrays.
{{ emailDuplicatesTable.selectedRows.map(row => 'manually_daleted' in row ? row : { ...row, manually_daleted: moment().format('YYYY-MM-DD HH:mm:ss') }) }}

I can't understand where these are from.
There are only 2 rows in my table in total and only one is selected

[ScottR]

I see because you have empty values in the table.... I think you would need to transform the changeSetArray to remove the empty values and then use the new value in that query

[ScottR]

I see in the table it says 4 of 2 selected
Try unchecking the box in the header to ensure all rows are not selected and then select one row