Extracting data from one table on button click and moving it to another

App Building
, ,
1

Hello, I currently have my PostgreSQL database connected to Retool the database features both a Live Table and an Archive. Both tables have identical columns structures etc.

The Live Table is represented in my Retool app in a table component.

I am looking to have a way whereby a user can select a row on the table of the Live Table, click a "record complete" button and the selected record will be deleted from the Live Table and added to the Archive Table.

I feel like I am missing something obvious here, any help is appreciated!

2

@OwainB I am assuming both tables are on the Retool side but also coming from your database?

For the Live Table on the Retool side, you could simply add an Action button that fires off a query that marks the record as completed. ( You should add a boolean field to the "Live Table" in your database.

This will allow you to have only one table in the database.

Then you can

  1. Run 1 postgres query (query1) to get all data from one table in the database.
    query1 = select * from "yourlivetableinthedb"

Then you can

  1. Run a Query JSON with SQL on that data to get all records that marked as complete (true) and then (This would populate your Live table on the retool side)
    query 2 = select * from {{formatDataAsArray(query1.data)}} where completed = true
    Note: You may not need to add formatDataAsArray

  2. run another Query JSON with SQL to get all records Not marked as complete (false) (This would populate the Archive table on the retool side.
    query 3 = select * from {{formatDataAsArray(query1.data)}} where completed = false

Hope this helps and if you want to send over a snippet of JSON from your db table, more than happy to mock it all up.