Best practice - duplicating a header + detail set of records

I'm looking for some guidance on how to duplicate both a header record and the related detail records for two related tables. My app is a product master with a record for each product () in a table called products, and related BOM expense items in a table called expenses (related by ).

I've thought to do this with two steps: 1. duplicate the header record under a new temporary name (copy of - ). and 2. duplicate new detail records, using the new <'copy of' name> as the replaced value for the name field that relates the two tables.

Step 1 seems to work - successfully added a button to the table that duplicates the product record, creating a new one with all the same field values except that 'name', the primary key, in the new record = 'copy/ '. I've also saved this new name in a state variable to used to create a new set of detail records.

Step 2 has me lost - I do not understand how to next make a copy of all of the detail records, inserting them but with this new state as their value for ...thereby creating a complete duplicate of header and detail but with a new name, 'copy of/...'.

So, two questions. Is this the best way to do this kind of duplicate creation? Is there a reference somewhere to an example of the best way to complete this?

Hi @Al_Sambar,

Actually this is pretty easy using a INSERT INTO .....SELECT....FROM....WHERE query.

For both the header, details and BOMs do something like this :

INSERT INTO headertable (name, description, style) SELECT (concat("Copy/",name),description, style FROM headertable WHERE name = 'productname'

This will inserts the same values as the original record but name is being changed.

Create 3 different queries using SQL and let the next one run on handler event succes.

Wow...very grateful for the help.
I'm trying this on the detail records and think I'm doing some syntax wrong.
My SQL...
INSERT INTO expenses (product, resource, type, quantity, unit_cost, extended_cost, purpose, note) SELECT (concat("Copy/", {{}}), resource, type, quantity, unit_cost, extended_cost, purpose, note) FROM expenses WHERE product = {{}}

I receive the error message: * message:"column "Copy/" does not exist"

Attaching a picture of the detail table (expenses) that I'm trying to insert into in case that is helpful.

try this:

INSERT INTO expenses (product, resource, type, quantity, unit_cost, extended_cost, purpose, note) SELECT concat("Copy/", {{}}), resource, type, quantity, unit_cost, extended_cost, purpose, note FROM expenses WHERE product = {{}}

no need tot put () around the new values when using SELECT, only when inserting static values.

That gives me a different error message: "column "Copy/" does not exist"

Hi @Al_Sambar,

Can you try single quotes instead of double quotes around Copy/

I did. That turns it green but gives me a different error message...even though you can see both insert and select have eight target fields.

  • "INSERT has more target columns than expressions"

Hi @Al_Sambar,

:thinking: I'm having trouble reproducing that error on my side. The following query, which is similar to yours, is working for me, but it only works when prepared statements are disabled on the resource

Ahhh, I found the flag. It is at the database level. I clicked it , but still get the error, and two other queries now also have errors.

Hi @Al_Sambar,

I think the parentheses around the select are causing that error.

Since the prepared statements setting impacts the way queries are formatted, changing the setting is likely to break other queries. By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields. You can disable this setting in the resource setup for specific cases like this one, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query.

We typically recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

Hi @Al_Sambar,

I was able to do a bit more testing here. I think we can solve this without the concat part & without having to disable prepared statements (which is great given the security impact described in my previous message) :crossed_fingers:

Can you try enabling prepared statements, removing those extra parentheses, and replacing concat('Copy/', {{}}) with {{'Copy/'}} ?

Hi Tess,
I think I accidentally didn't send my reply when I tried this. It didn't work. I received the error:

  • "operator is not unique: unknown + unknown".

Hmm can we see a screenshot of the current query with with the green popup below the query like my message on Jun 14?

Hi All. Tess, thank you for helping. Jay Lee from Retool reached out to my email and helped me through this. We tried many variations of sql syntax using concat but finally got it to work by using JS in the SQL box.
For anyone struggling with this challenge, here is the script that ultimately worked for me:

INSERT INTO expenses (product, resource, type, quantity, unit_cost, extended_cost, purpose) 
SELECT {{'Copy/' +}}, resource, type, quantity, unit_cost, extended_cost, purpose 
FROM expenses 
WHERE product = {{}};

Thank you so much for sharing and sticking with us as we worked through this! Sincere apologies that it took awhile :disappointed: