# Power Table editable with query Vision **URL:** \ **Category:** Ignition\ **Tags:** vision\ **Created:** [November 17, 2022, 9:16am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626 "2022-11-17T09:16:01Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\ **Post date:** [November 17, 2022, 9:16am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/1 "2022-11-17T09:16:01Z")
Hi, I have the screen setup as below, ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/1/c186d00e73a2f5fd2fcb3134c9a93614e4bf94f4.png) Script in search button: ```auto 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: ```auto 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? ---
**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\ **Post date:** [November 17, 2022, 2:01pm UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/2 "2022-11-17T14:01:53Z")
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. ```auto 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]) ``` ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [November 18, 2022, 3:35am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/3 "2022-11-18T03:35:37Z")
> [@JGJohnson](#): > > `system.db.runPrepStmt(query, [value, colCon])` Good refactor, value in this line should be newValue > [@Dharani\_T](#): > > I want to edit cell value only if date column \>= current date This could be ensured with an `if` statement **Edit:** > [@Dharani\_T](#): > > `system.db.runPrepStmt(query, [value, colCon])` 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: ```auto 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}) ``` ---
**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\ **Post date:** [November 18, 2022, 5:52am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/4 "2022-11-18T05:52:02Z")
Yes it works for display. But how to update in db with the below query, ```auto query = "UPDATE tbl_productionplan SET PlanQty = ? WHERE PlanDate = ?" ``` ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [November 18, 2022, 6:33am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/5 "2022-11-18T06:33:11Z")
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](https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate). For example, if the database name was "myDataBase" then your code would look like this: ```auto 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")` ---
**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\ **Post date:** [November 18, 2022, 6:44am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/6 "2022-11-18T06:44:44Z")
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 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/d/2de626886a1b5a9bbc22e28d946b7b05a059f9c8.png) ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [November 18, 2022, 6:53am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/7 "2022-11-18T06:53:23Z")
> [@Dharani\_T](#): > > 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: ```auto 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. ---
**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\ **Post date:** [November 18, 2022, 6:58am UTC](https://forum.inductiveautomation.com/t/power-table-editable-with-query-vision/66626/8 "2022-11-18T06:58:59Z")
Thanks a lot Justin, You saved my day.