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?
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/", {{stateCurrentProduct.value.name}}), resource, type, quantity, unit_cost, extended_cost, purpose, note) FROM expenses WHERE product = {{stateCurrentProduct.value.name}}
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.
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
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.
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)
Can you try enabling prepared statements, removing those extra parentheses, and replacing concat('Copy/', {{stateCurrentProduct.value.name}}) with {{'Copy/'+stateCurrentProduct.value.name}} ?
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/' + stateCurrentProduct.value.name}}, resource, type, quantity, unit_cost, extended_cost, purpose
FROM expenses
WHERE product = {{stateCurrentProduct.value.name}};
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
