Duplicate a table row that is linked to another table with foriegn keys

Ignition
1

Our customer has asked if we could add a copy button to our recipe screen. What they want is to select a row and press a copy button to duplicate that row and all the coat info. I have attached a screen shot of the screen as it is. Along with screen shots of the two tables that make up the recipes.

Ignition Screen:

image
image2240×1502 155 KB

Recipe Table:

image
image1296×416 15.8 KB

Coats Table:

image
image1372×382 8.37 KB

When the user hits copy, recipe 11 would be duplicated. to do this I need to be able to: generate the next available recipes_id and the next available recipe number and I also need to be able to duplicate all the coats that have a recipes_id of 39 and replace that with the new recipes_id.

I am struggling to come up with a solution on how to do it and was hoping somebody here would have some insight.

2

Generally, you would use two INSERT .... SELECT .... queries. The first would duplicate the recipe row, and the other would duplicate the coats. I would allow the recipe id to auto-increment, and retrieve it in the first operation. You’ll have to list all the columns to insert in each case, omitting the auto-increment column in the first, and supplying the returned constant as the recipe ID in the second.

3

Thank you, how would I go about retrieving the recipe id during the first operation?

4

Typically using the getKey option to system.db.runPrepUpdate() or your DB’s equivalent.

5

Ok so internet is spotty on the job site so I am trying to write this offline in my hotel but this is the rough code that I came up with:

newnumber = ...
date=...
selectID=...

Query = "INSERT INTO recipes (Number, Description, DateCreated) SELECT (?, Description, ?) FROM recipes WHERE recipes_id = ?"
id = system.db.runPrepUpdate(query, [newnumber, date, seletedID], getKey=1)

Im not sure though if the way I am doing the arguments is correct.

6

Close:

Query = "INSERT INTO recipes (recipes_id, Description, DateCreated) SELECT ? As recipes_id, Description, ? As DateCreated FROM recipes WHERE recipes_id = ?"

Note the use of actual column names for your insert.

7

I see you used recipes_id in the insert, that is the auto incrementing column so I would want to leave that one alone correct? I was referencing Number which is a user provided recipe number.

8

I was able to get it all working so I tried to get fancy and I am having an issue. I want to copy the Description but append “- Copy” at the end but I am having an syntax problem. I’m sure its a simple fix.

query = "INSERT INTO recipes (Number, Description, DateCreated) SELECT ? AS Number, Description + '-Copy', ? AS DateCreated FROM recipes WHERE recipes_id = ?" NewID = system.db.runPrepUpdate(query, [number, date, id], database, getKey=1)

9

Use the concat() function in SQL with strings – many (most?) DBs don’t implement ‘+’ for strings.

2 Likes