Duplicate a selected row

App Building
1

I'm trying to write a query to duplicate a selected table rows data – adding it to the same table just with a new auto-incrementing ID.

It doesn't seem like the 'columns' property is available? Not sure where I'm going wrong.

INSERT INTO maintenance_requests ({{table23.selectedRow.columns}})
SELECT {{table23.selectedRow.columns}} 
FROM maintenance_requests
WHERE request_id = {{table23.selectedRow.request_id}};
2

Hi @dru_nasty!

It doesn't look like we expose columns (it's just an object of the row's data):

Is this for a postgres query? You could still get an array of columns by calling {{Object.keys(table1.selectedRow)}}.

3

If possible, I'd highly recommend using the GUI mode so that you don't have to worry as much about syntax.

To insert a new row with the same data that is in your selected row, I'd use a GUI mode query like this one. I'm using lodash's _.omit to remove the id, which is the primary key for my table so that the primary key just auto increments

4

Hi Tess, that's exactly what I was looking for thank you.

{{_.omit(table23.selectedRow, ['request_id'])}}

When i preview my query, it removes my 'request_id' as it should, but when i actually run the query on a selected row i get this error:

  • invalid input syntax for type integer: "Open""

My Postgres database table i'm trying to insert the data into, has a foreign key set up for my Status, so in the table i'm trying to insert what the mapped value was on the table for that column.
Natively the status comes in as a number, but as you see I change it to read something different.

Somehow I need that column changed back to it's original (a number) before inserting data. Not sure even how to do that.

Screenshot 2023-06-09 at 12.28.59 PM
Screenshot 2023-06-09 at 12.28.59 PM610×810 38.2 KB

5

Hi @dru_nasty!

You'll have to add a similar logic back on the data. You could do something like this:

{{_.update(_.omit(table23.selectedRow, ['request_id']), 'Open', val=>{val==='Open'? val=2: val==='New' ? val=1 : val==='Bill Back'? val=3 : val==='Closed'? val=4 : val=null; return val})}}

Keep in mind that this part, ':val=null' is a catchall for the No Status items, but you may need different logic if you don't want that to be null

6

Thanks Tess! I ended up with this:

{{_.update(_.omit(table23.selectedRow, ['request_id']), 'ticket_status', val=>{val==='Open'? val=2: val==='New' ? val=1 : val==='Bill Back'? val=3 : val==='Closed'? val=4 : val=null; return val})}}
1 Like
7