Goal:
Hello retool community jewis here, I am noticing when I have a table with single row selection and I have one column editable using the save action, when I edit a record and click enter, I have a SQL query that updates the column for the selected row but the enter button moves the selected row to the next record, so the proposed edit goes to the next record and not the intended record. My goal is to prevent the enter button from moving to the next row upon clicking. If someone has experienced this issue, is this a matter of changing the sql query to not use the selectedRow or to change the configuration of the table component.
Steps:
I can fix the problem by making the table configuration to use multiselection, this does not fix the enter button issue but since you click the box to designate the selected record, the sql query knows that the selected record is the intended target, regardless of the enter button moves the highlighted recorded after clicking enter and saving. I do not want to implement this solution since it will cause confusion for the intended user, since they should only be able to edit a record one at a time. I also have a delete icon, which when you click selects the record, so using multiselection creates the extra step of clicking the box to select the record then clicking the delete icon, which is redundant, so I want the table configuration to use single selection.
Details:
This is the current query to update the record when performing an edit on the records that exist in the table:
update JLEWIS.TABLE_TEST
set column2= {{table1.changesetArray[0].column2}}
where column1= {{table1.selectedRow.column1}}
So the first picture is just my attempt of illustrating the issue, if I edit column 2, and click enter the highlight moves to the next record, making the second record the "selectedRow", which changes the target for the query. In this picture the table is using single selection.
I found this table method table.selectPreviousRow() which selects the previous record but I do not know how to really make use of it since I am not the most well versed using JS/transformers.
In the setup of the table, you can "Send full row data to Changeset" in the interaction options. Then you can just use column1 from the changesetArray.
So the save option is sending the full row to the changeset array. I found the send full row to changeset option in the table configuration but I do not need to turn it on since the save option is already sending that information. Your suggestion of using the primary key from the changeset is working. Thank you again, really appreciate it.
Here is updated query for anyone who stumbles upon this issue.
update JLEWIS.test_table
set column2= {{table1.changesetArray[0].column2}}
where column1= {{table1.changesetArray[0].column1}}
@pyrrho If you can help me out with this new issue I have due to a change I made, instead of using the save option, I made an event handler with column2 upon the cell changing to run that update query, but there is a confirmation message first. My issue is that when you click cancel on the confirmation message I need the changeSet to clear on the table so that the edit removes itself and the original content in column2 for that row comes back.
So I need to understand if the cancel in the confirmation message counts as a failure for a query attempting to run. If that counts as a failure, I would just clear the table's changeSet in the failure run.
I don't believe you are able to access the results of the confirmation modal using the default query options. You could have the triggering cell handler open a modal that is functionally the same as the built-in confirmation modal and then link the Cancel button to clear the changes with yourTableName.clearChangeset() and trigger the update query on the OK.