I’m having trouble updating a data table when a cell is edited.
The mysql table looks like this.
I want to pivot the table to look like this
Using this query with a bunch of unions
SELECT "Number" Description, Recipe_Number
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
Union All
SELECT "Product" Description, Product_Code
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
Union All
SELECT "Grist Weight To Add" Description, Grist_Weight_To_Add
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
Union All
SELECT "Strike vol" Description, Strike_Water_to_Add
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
Union All
SELECT "Ramp Soak Cycles" Description, Samp_Soak_Cycles
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
Union All
SELECT "Ramp Soak Time 1" Description, Mix_Tank_Soak_Time_1
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
Union All
SELECT "Ramp Soak Temp 1" Description, Mix_Tank_Temperature_1
FROM pilotrecipe
Where Recipe_Number Like {Root Container.CurrentRecipe}
This update script works just fine on a “select All” type of a table. It does not function on my pivoted version. Maybe something to do with changing Column names into a single row?
row = event.row
col = event.column
colName = event.source.data.getColumnName(col)
Recipe_Number=event.source.data.getValueAt(row,0)
value = event.newValue
query = "Update pilotrecipe SET %s = ? WHERE Recipe_Number = ?"
system.db.runPrepUpdate(query % colName, [value, Recipe_Number], 'history')
Is there a more effective way to pivot this table and allow cell updates, or does my update scrip need to evolve?
Welcome, Mike.
That query needs some TLC, edit your post to change the query and script into code blocks. This way we can easily copy and paste them to help you out.
Wiki - how to post code on this forum - General Discussion - Inductive Automation Forum
Nothing wrong with the query, technically, I missed the pivot part. Your problem seems to be when you get the Recipe_Number. You are getting the first value in which ever row is edited.
Instead, with the pivot, you always want to get the second value of the first row. Try this,
Recipe_Number=event.source.data.getValueAt(0,'Recipe')
Recipe_Number=event.source.data.getValueAt(0,'Recipe')
Gives me an error for Column 'Recipe' doesn't exist in this dataset.
Recipe_Number=event.source.data.getValueAt(0,'Recipe_Number')
Trying this changes the recipe number instead of the intended cell
Add Recipe after your selected values in the query like you did Description.
For the pivot, it should be more like this
row = event.row
# if you don't want to allow the recipe number to be edited
if row > 0:
# the field_name is not the column name but the first value in the edited row
field_name = event.source.data.getValueAt(row,0)
# recipe number is always second value in the first row
recipe_number = event.source.data.getValueAt(0,'Recipe') # or use 1 instead of Recipe
value = event.newValue
query = "Update pilotrecipe SET %s = ? WHERE Recipe_Number = ?"
system.db.runPrepUpdate(query % field_name, [value, recipe_number], 'history')
Try checking your values too.
system.perspective.print(query % colName)
caused by Exception: Error executing system.db.runPrepUpdate(Update pilotrecipe SET Ramp Soak Cycles = ? WHERE Recipe_Number = ?, [4, 1], history, , false, false)
caused by GatewayException: SQL error for "Update pilotrecipe SET Ramp Soak Cycles = ? WHERE Recipe_Number = ?": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Soak Cycles = '4' WHERE Recipe_Number = '1'' at line 1
caused by SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Soak Cycles = '4' WHERE Recipe_Number = '1'' at line 1
I’ve added Recipe to the original query and used your update script.
Thank you dk for the help. I’m in the weeds on this one.
I think it’s the spaces, it doesn't match the table column names in the db. Keep the underscores in your query, or use `replace` to swap spaces for underscores.
