Update Power Table Data

Ignition
1

Hello all,

I am trying to update a power table data from a button in vision. So I have a table with 10 columns in my database now I want to select only 6 columns so I am doing this in a named query where I select 6 columns out of 10. Then I bind that data to a power table in vision. Now when the user selects a row in the table a specific cell of that table should be updated. Now for doing this I wrote my script on the action performed of a button:

table = event.source.parent.getComponent('Power Table')
selRow = table.getSelectedRow()
selCol = table.data.getColumnName(5)
if selRow != -1 and selCol != -1:
   newData = system.dataset.setValue(table.data, selRow, selCol, 'newOrder')
   table.data = newData

Which updates the table but does not update the original SQL table. Not sure how to fix it. I have tried different things and updated query scripts as well but non of them worked.

2

No, you’re doing it well.
The problem is that you are not writing the actual UPDATE QUERY or at least this is what is seems it’s happening. Add the query on “onCellEdited” Extension Function.

1 Like
3

I have to use the button but you are suggesting that I write a query on the table script too? and keep the current script?

4

I suggest what better fits better for your. Personally I prefer to have a update button and a field for the new value.

By the nature your prev script I thought you were doing the changes on the power table itself.

If you are doing the update in a button, I don’t see the need of your previous script. Just send a refresh to the data prop binding with system.db.refresh - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

At the end of the script in your button.

5

Thank you! I have tried that but that does not update the SQL table itself.

6

That means your query is the problem.

Assuming you’re using named query.
Did you changed its type to “Update Query”?
image

7

@jespinmartin1 So what I am doing exactly is I have a table with 10 columns in my database now I want to select only 6 columns so I am doing this in a named query where I select 6 columns out of 10. Then I bind that data to a power table in vision. Now when the user selects a row in the table a specific cell of that table should be updated. Now for doing this I wrote my script on a button:

table = event.source.parent.getComponent('Power Table')
selRow = table.getSelectedRow()
selCol = table.data.getColumnName(5)
if selRow != -1 and selCol != -1:
   newData = system.dataset.setValue(table.data, selRow, selCol, 'newOrder')
   table.data = newData

Which updates the table but it does not update the original SQL table. This is my issue now.

8

dear @pkhoshroo ,
If that is your entire script, so I return to my 1st reply. You are not actually writing nothing to the database because you don’t have any UPDATE QUERY.

The script you are showing just alters the data in you dataset. It doesn’t mean it will be bidirectional to the table a the database. To actually change data at SQL table is by using an Update Query.

9

Yes, I know to use an Update query but I am not sure how to capture the selected row and column in the query and set that value and even if I should write that on the button or on the table. Since capturing the whole row is not possible in the query I need to write where col1=… and col2=… and … col6=…write?
And for specifying column based on selected row I should write:
col1= event.source.parent.getComponent(‘Power Table’).data.getValueAt(table.selectedRow, “col1”)

correct?

10

This is SQL design. Your SQL table should have a Primary Key, this works like an unique id for each row. That is what you need to catch and use it in Update Query.

It doesn’t matter if you use it in a button or in the Power Table itself

2 Likes
11

You may want to have something like this.
1.-
image
2._ Maybe you can hide it
image
3.-Send the name of the colName to a custom property with

image
image790×719 59.7 KB

4.-It the button at least this properties
image

Note the id binding looks like this try({Root Container.Power Table.data}[{Root Container.Power Table.selectedRow},'id'],-1)
5.- Finally the script, in the button

updateQuery = \
"""
UPDATE table
SET %s = ?
WHERE id = ?
""" % event.source.selectedColumnName
newValue = event.source.newValue
id = event.source.id
if event.source.selectedColumnName not in ('id','') and id != -1:
	system.db.runPrepUpdate(updateQuery,[newValue, id])
	system.db.refresh(event.source.parent.getComponent('Power Table'),'data')
1 Like
12

Take a look to this
Updating the Database through the Power Table - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

1 Like
13

Thank you so much, Jonathan! I do really appreciate it! Will try it and see if it works on my side.

14

Thank you so much, Jonathan. I used the methods you mentioned and it worked. Appreciate your help.

15

I would really appreciate, to get solution how to bind selectedColumnName

16

You can bind directly to the selectedColumn index, and from there it's trivial to convert the index into a column name. I suppose a custom property could be added that could be bound to. The custom property could then be automatically updated via a property change script.

Here are two ways that I know of to obtain the selected column name of a power table with scripting:

if event.propertyName == 'selectedColumn':
	powerTable = event.source
	if powerTable.selectedColumn != -1:
		selectedColumnName = powerTable.table.getColumnModel().getColumn(powerTable.selectedColumn).getHeaderValue()
		print selectedColumnName
		
		#or
		
		selectedColumnName = powerTable.table.getModel().getColumnName(powerTable.selectedColumn)
		print selectedColumnName
17

Thank you so much, I do really appreciate it! Will try this way.

1 Like