Check rows in a Retool table, update a database tablerft

[Michael_Stabosz]

I searched, and there are similar questions, but nothing is quite right for my situation. I have a feeling I am going about this in a needlessly complicated way, but it was all I could think of.

• Goal: User clicks multiple rows from this table, selects a value from a select element within a modal, and that updates multiple rows in a database table called plate. Database is within the Retool resources.

The "Status Name" is supposed to update.

• Steps:

1. Select a status from the drop down.
2. Run this query:

update plate
set plate_status = {{ statusSelect.selectedItem.id }},
last_updating_user = 'UNKNOWN',
last_updated_time = now()
where serial_code in (selectedPlates.value)
;

and "selectedPlates" is from this Transformer

var selectedNumbersArr = [];

selectedNumbersArr = selectedNumbersArr.concat({{ plateInfoTable.selectedRows }}.map(  it => {return {serialNo : it.serial_code}}));

var selected = "";
for(var i = 0; i <  selectedNumbersArr.length; i++){
  selected += " '" +selectedNumbersArr[i].serialNo +"' ,";
}

return selected.substring(0,selected.length - 1);041

It produces working SQL, e.g. update plate set plate_status = 4, last_updating_user = 'UNKNOWN', last_updated_time = now() where serial_code in ( '0000011112222' , '123456789' , '222222' ) ;

This query runs without errors, but it fails to update anything.

I tried to do this through the GUI tab and the "Bulk Update Records through Primary Key", but was even more clueless on how to do that.

[Paulo]

Hi @Michael_Stabosz, the bulk update should be straight forward. Here is an implementation:

A table with multiple row selection just like yours, and a Select component to set the desired role for the selected rows:

I added a button to submit the update in order to prevent running multiple queries if there's an error on selection, but feel free to skip it. When users click that button (or on dropdown selection if you prefer), we run this JS:

We are returning an array of objects with the id of each row, and the new role for the update. On success, we run the bulk update query (more on this query on the next step).

The bulk update query, uses the id of each object in the array to know which row to update, and updates the role column with the new value from the Select component:

Because this query runs on success, updateRoles.data will always be the updated objects. Finally, we attach two Success event handlers to this last query, one to refresh the table, and one to clear the selection.

Let us know if you have any questions!