SQL Update Query Not Working with dataChangeSet.value in Retool

💬 Queries and Resources
1

Hi everyone,

I'm trying to write an SQL UPDATE query in Retool to update a row in my freight_forwarder table using values from the table's edit mode. Here's what I wrote:

sql

CopyEdit

UPDATE freight_forwarder
SET
  "Loading Port" = {{ dataChangeSet.value["Loading Port"] }},
  "Loading at" = {{ dataChangeSet.value["Loading at"] }},
  "Discharge Port" = {{ dataChangeSet.value["Discharge Port"] }},
  "Deliver to" = {{ dataChangeSet.value["Deliver to"] }},
  "Container/Truck Type" = {{ dataChangeSet.value["Container/Truck Type"] }},
  "Price" = {{ dataChangeSet.value["Price"] }},
  "Additional Cost" = {{ dataChangeSet.value["Additional Cost"] }},
  "All in price" = {{ dataChangeSet.value["All in price"] }},
  "Validity Date" = {{ dataChangeSet.value["Validity Date"] }},
  "Term and Condition" = {{ dataChangeSet.value["Term and Condition"] }},
  "Carrier" = {{ dataChangeSet.value["Carrier"] }},
  "Incoterm Buying" = {{ dataChangeSet.value["Incoterm Buying"] }},
  "Incoterm Sales" = {{ dataChangeSet.value["Incoterm Sales"] }}
WHERE "Id" = {{ freightForwarderTable.selectedRow.Id }};

However, this doesn't seem to be working — no errors show up, but the data is not updating in the database or in the UI.

Has anyone run into this before? Am I using dataChangeSet.value correctly? Any tips or best practices for updating editable table rows with SQL queries in Retool?

Thanks in advance!

1 Like
2

Hi there @parnian94 and welcome to the forum!

the table's edit values are accessible at table1.changesetArray.

I created a youTube tutorial on this here, which explains the logic of updating a table.

With regards to your specific example, i think the easiest way of achieving this is by:

  • Changing to GUI mode
  • Setting edit row via primary key
  • add {{ table1.changesetArray }} as object (if you want to pass the full row and not only the edited cells, you can activate that functionality as explained here

HAppy to go into more detail if none of the above helps

1 Like
4

Thanks for the reply, It is fixed now. How about creating a new record, row in the table through Modal. Could you please help me with that? @MiguelOrtiz

1 Like
5

Hi there @parnian94,

Well, it is more or less the same, you just need to add a form into your modal and create a query that submits the form1.data (usually if you create a form and map it to a table's database it will create the query automatically for you).

Let me know where are you struggling and I can guide you through those obstacles.

6

@MiguelOrtiz thanks Thanks for your response!

I created a form inside the modal and followed the same structure. I’ve attached a screenshot. it looks like the query is running, but there’s no update on the table in the UI or the database.

Would appreciate any thoughts on what might be missing.

image
image1534×680 37.1 KB

7

Hi @parnian94,

You need to set up an event handler (right at the bottom of your query settings) in your query that re runs the query that is the data source for your table. The query is updating your database, but your query needs to rerun to get that new data.

8

Could you please guide me on how to set this up? @MiguelOrtiz
I want to make this new record available on both retool database table and UI table

image
image900×1282 71.4 KB

9

This is how I set up the event handler, but I don’t see any updates in either table. @MiguelOrtiz

image
image642×694 31.2 KB

10

@MiguelOrtiz Do you have any suggestion for me based on the shared screenshots ? Thanks in advance