# Update Power Table Data

**URL:** <https://forum.inductiveautomation.com/t/update-power-table-data/53556>\
**Category:** Ignition\
**Created:** [November 30, 2021, 2:34am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556 "2021-11-30T02:34:56Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 2:34am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/1 "2021-11-30T02:34:56Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 3:01am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/2 "2021-11-30T03:01:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 3:02am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/3 "2021-11-30T03:02:45Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 3:09am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/4 "2021-11-30T03:09:00Z")

</div>

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)](https://docs.inductiveautomation.com/display/DOC81/system.db.refresh)

At the end of the script in your button.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 3:11am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/5 "2021-11-30T03:11:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 3:12am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/6 "2021-11-30T03:12:23Z")

</div>

That means your query is the problem.

Assuming you’re using named query.  
Did you changed its type to “Update Query”?  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/4/149c3bcaa66bb9f8f83cb92e3a77cf96b510b977.png)

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 3:18am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/7 "2021-11-30T03:18:27Z")

</div>

@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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 3:24am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/8 "2021-11-30T03:24:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 3:30am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/9 "2021-11-30T03:30:30Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 3:38am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/10 "2021-11-30T03:38:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 3:54am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/11 "2021-11-30T03:54:36Z")

</div>

You may want to have something like this.  
1.-  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5dc2b19255beadac47b624e0483468e99dc35347.png)  
2.\_ Maybe you can hide it  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/3/53d61fb3b43a7b87eac4f04846e63452b1f9d7e3.png)  
3.-Send the name of the colName to a custom property with

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/4/54c43f341bef1fd37404413a352c6aa2dab1b90a.png)  
4.-It the button at least this properties  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7a99f3188de6927f598d1cb63abba4a47cae4065.png)

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

```auto
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')

```

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [November 30, 2021, 4:00am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/12 "2021-11-30T04:00:10Z")

</div>

Take a look to this  
[Updating the Database through the Power Table - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/Updating+the+Database+through+the+Power+Table)

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 4:08am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/13 "2021-11-30T04:08:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 30, 2021, 5:55am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/14 "2021-11-30T05:55:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Watson\_Durom](https://avatars.discourse-cdn.com/v4/letter/w/edb3f5/32.png) [@Watson\_Durom](https://forum.inductiveautomation.com/u/Watson_Durom)\
**Post date:** [January 11, 2023, 2:07pm UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/15 "2023-01-11T14:07:00Z")

</div>

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

---

<div class="post-metadata">

**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:** [January 12, 2023, 6:11am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/16 "2023-01-12T06:11:48Z")

</div>

> [@Watson\_Durom](#):
>
> bind selectedColumnName

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Watson\_Durom](https://avatars.discourse-cdn.com/v4/letter/w/edb3f5/32.png) [@Watson\_Durom](https://forum.inductiveautomation.com/u/Watson_Durom)\
**Post date:** [January 12, 2023, 6:25am UTC](https://forum.inductiveautomation.com/t/update-power-table-data/53556/17 "2023-01-12T06:25:40Z")

</div>

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