Hi,
I have the screen setup as below,
Script in search button:
vDate= event.source.parent.inDate
vProcess=event.source.parent.Process
params ={"iDate":vDate,"iProcess":vProcess}
query = system.db.runNamedQuery("Prod_Plan/getPlan", params)
event.source.parent.getComponent('Prod_Plan').data = query
I want to edit cell value only if date column >= current date. and on clicking a save button these records should be updated.
Please note I have inserted 0s already in PlanQty column for more than a year record. So i am going to update not insert.
Script in onCell edited:
from datetime import datetime
row = event.rowIndex
col = event.colIndex
x = datetime.strptime(event.colName,'%d-%m-%Y')
colCon = x.strftime('%Y-%m-%d')
value = event.newValue
#id = event.source.data.getValueAt(row, 0)
vProcess = event.source.parent.Process
query = "UPDATE tbl_productionplan SET PlanQty = ? WHERE PlanDate = ?"
system.db.runPrepStmt(query, [value, colCon])
but however, this is not working. Any suggestions to achieve this?
The event in the extenstion function is the mouse event and it doesn't reference the power table component.
Instead of event.source you need to use self. I.E. self.data, self.visible etc.
event.rowIndex is incorrect syntax for the onCellEdited extension function. Same for colIndex and colName. Also use value instead of event.newValue.
from datetime import datetime
x = datetime.strptime(colName,'%d-%m-%Y')
colCon = x.strftime('%Y-%m-%d')
#id = self.data.getValueAt(rowIndex, 0)
vProcess = self.parent.Process
query = "UPDATE tbl_productionplan SET PlanQty = ? WHERE PlanDate = ?"
system.db.runPrepStmt(query, [value, colCon])
Good refactor, value in this line should be newValue
This could be ensured with an if statement
Edit:
I wasn't familiar with system.db.runPrepStmt, but I found an example of it, and I realized what it was for. I find this much easier:
self.data = system.dataset.updateRow(self.data, rowIndex, {colName: newValue})
For simply updating the table using the onCellEdited extension function, the following code should work if the name of the column being edited is a date that is greater than or equal to today:
from datetime import date, datetime
x = datetime.strptime(colName,'%d-%m-%Y')
if x.date() >= date.today():
colCon = x.strftime('%Y-%m-%d')
self.data = system.dataset.updateRow(self.data, rowIndex, {colName: newValue})
Yes it works for display. But how to update in db with the below query,
query = "UPDATE tbl_productionplan SET PlanQty = ? WHERE PlanDate = ?"
I'm not familiar with using runPrepStmt, so I don't know how to make it update a database. If you have a database connection set up in the gateway then you can use system.db.runPrepUpdate. For example, if the database name was "myDataBase" then your code would look like this:
from datetime import date, datetime
x = datetime.strptime(colName,'%d-%m-%Y')
if x.date() >= date.today():
colCon = x.strftime('%Y-%m-%d')
self.data = system.dataset.updateRow(self.data, rowIndex, {colName: newValue})
query = "UPDATE tbl_productionplan SET PlanQty = ? WHERE PlanDate = ?"
system.db.runPrepUpdate(query, [newValue, colCon], 'myDataBase')
If your table is bound to a SQL query, then you can add this line of code to refresh the table after database writes:
system.db.refresh(self, "data")
Hey it is working fine,
But when enter value for one cell and enter, entire column got updated for the day. How to include the machine no as well in then query
Assuming that the machine name in column zero is the value you are looking for, then add it as another argument in the query:
machine = self.data.getValueAt(rowIndex, 0)
query = "UPDATE tbl_productionplan SET PlanQty = ? WHERE PlanDate = ? AND MachineNo = ?"
system.db.runPrepUpdate(query, [newValue, colCon, machine], 'myDataBase')
You'll have to change MachineNo to what ever the actual column name is in the database.
1 Like
Thanks a lot Justin, You saved my day.
1 Like
