Hi I just want to write a selected cell of DB from a table. Could you give me a simple script sample?
Regards,
Leandro
If you want to edit a database table using a Perspective table then the basic sequence is:
- Add the table to the view.
- Create a binding on the table's data property to the database.
- Fill in the
columns.X.fieldproperties so the table knows which database column to use for each table column. - Set the
columns.X.editableproperties as required. - Right-click on the table and select Configure Events. Create an onCellEdit event. This is where you will write your script.
- In the script, do any necessary data validation and then run an UPDATE query.
- Finally, refresh the table's data properties.
You should be able to find further details on the forum.
Hi thank you it was very helpful. I’m stuck at the point 5 and 6.
I don’t know how to send the parameters to named query since it’s a selected cell. Could you give me a sample?
Thanks
I might be able to dig out a sample later. Meanwhile have a look at the docs.
https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table+Scripting
Then have a look at Quick help - editable Perspective Table - #3 by PGriffith and pay particular attention to the posts by the IA staff (identifiable by the IA icon on their photographs).
Hi,
Thank you, sorry for bothering you about this. I’m not so skilled.
Regards,
Leandro
You're missing the closing ) on the runPrepUpdate command.
Tip: when showing code it's a good idea to post formatted code rather than pictures of code. That way we can edit it / test it. If the context is relevant then post a cropped screengrab as well.
Hi, got it
Now this error message is appearing
Please look the code
def runAction(self, event):
r = event['row']
c = event['column']
v = event['value']
id = self.props.data[r].ID
system.db.runPrepUpdate("UPDATE holdLog SET reasonforHold = ? WHERE ID=?", [v, id])
self.refreshBinding("props.data")
Its still not working. COuld you help me?
It looks like you re-posted the old screen grab. The ) is still missing and there is an error warning red rectangle on the right. Your pasted code is correct though.
- Is there an
IDcolumn in the table? - Is it actually
IDand notidorId?
And all the ID entries are NULL.
That's your problem. You can't identify the row and you won't be able to update it.
3 Likes
Can you modify the table and set the ID column to "primary key" and "auto-increment". (The names may vary depending on the database - MySQL / MS SQL, etc.) I don't know if you can apply this retrospectively.
Here's a procedure for MySQL:
The idea is that the ID column is unique number for each row. That way the database engine knows which row to modify with the UPDATE query and there is no danger of having duplicate IDs.
Hi thank you,
I just made it and stills not working.
This is my table
this is my code:
def runAction(self, event):
r = event['row']
c = event['column']
v = event['value']
id = self.props.data[r].id
system.db.runPrepUpdate("UPDATE HoldLog2 SET reasonforHold = ? WHERE id=?", [v, id])
self.refreshBinding("props.data")
Check the fault message below
Could you help me again?
It seems your table data is in Dataset format? If so, you will need line #6 to be
id = self.props.data.getValueAt(r, 'id')
You need to provide the database connection name as a 3rd argument to runPrepUpdate().
Sorry, this is the name CD_HUBTRAININGEXPORT.
where should I place it?
SOmething like this?
system.db.runPrepUpdate("UPDATE HoldLog2 SET reasonforHold = ? WHERE id=?", [v, id], "CD_HUBTRAININGEXPORT")
That works!!!! Amazing
Thank you all
@pturmel and @Transistor
sorry for disturbing you !!!!
See you!!!
2 Likes