# Perspective scripting-how to reference a specific cell in a table in python

**URL:** <https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961>\
**Category:** Ignition\
**Tags:** perspective, scripting\
**Created:** [November 21, 2024, 10:23pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961 "2024-11-21T22:23:08Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alex\_Jaffe](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@Alex\_Jaffe](https://forum.inductiveautomation.com/u/Alex_Jaffe)\
**Post date:** [November 21, 2024, 10:23pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/1 "2024-11-21T22:23:08Z")

</div>

Help please, I have been stuck for the last 2 hours.

Overview of what i am trying to do:  
I have this table in Perspective- it is pulling from a SQL database

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/1/c119812f903e4b74cd48b5dcdd8b814677e4982d.png)

I am trying to configure a button to use a for loop to go though each row of this table.

on each row the loop would grab the SheetID cell value and then use that as a parameter to run a query. (it has to be done in python).

Here is the issue i am running into and I desperately need help on. How do I reference a specific cell value in this table???

the loop is simple  
R=0  
While R\< num rows

- grab sheet ID value (I can not do this)\<------------(this is where i need help)!!!
- run query pass sheet id as parameter ( i can do this)
- R +=1

All I need is to know how to reference a specific cell (When it is NOT selected). If you can help thank you!!

Past Tries: This is where i am coming from (I do not need help on anything below this, i am just giving you background of where i am stuck)

I have tried using Event configuration on a label to get the label to display the cell value  
##table = event.source.parent.getComponent("Table")  
##value = table.data.getValueAt(0, "SheetID")  
##self.props.text = value

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 21, 2024, 11:33pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/2 "2024-11-21T23:33:45Z")

</div>

Are you using a dataset or JSON for the table's `data` property?

This works with the default table component data.

```python
def runAction(self, event):
	data = self.getSibling("Table").props.data
	for row in data:
		country = row['country']
		# Do something with country ...

```

Please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831).

---

<div class="post-metadata">

**Author:** ![Alex\_Jaffe](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@Alex\_Jaffe](https://forum.inductiveautomation.com/u/Alex_Jaffe)\
**Post date:** [November 22, 2024, 12:26am UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/3 "2024-11-22T00:26:04Z")

</div>

Data set, the table is a dataset from a sql query

---

<div class="post-metadata">

**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\
**Post date:** [November 22, 2024, 3:02am UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/4 "2024-11-22T03:02:09Z")

</div>

In the SQL Query binding window, set it to output JSON as the output format.  
Then you can easily use python to iterate through the table.

---

<div class="post-metadata">

**Author:** ![Alex\_Jaffe](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@Alex\_Jaffe](https://forum.inductiveautomation.com/u/Alex_Jaffe)\
**Post date:** [November 22, 2024, 3:30pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/5 "2024-11-22T15:30:06Z")

</div>

That worked, thank you

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [November 22, 2024, 4:54pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/7 "2024-11-22T16:54:50Z")

</div>

> [@Alex\_Jaffe](#):
>
> on each row the loop would grab the SheetID cell value and then use that as a parameter to run a query. (it has to be done in python).

What's the query that needs to be done for each row ?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 22, 2024, 5:41pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/8 "2024-11-22T17:41:39Z")

</div>

For posterities sake, you don't have to use the JSON return format to accomplish this.

```python
def runAction(self, event):
    data = system.dataset.toPyDataSet(self.getSibling("Table").props.data)
    for row in data:
         sheetID = row['SheetID']
         specificData = system.db.runNamedQuery('YourNamedQuery',{'SheetID':sheetID})

```

Or, if you don't want to use a PyDataSet it would look like this:

```python
def runAction(self, event):
    data = self.getSibling("Table").props.data
    for row in range(data.rowCount):
        sheetId = data.getValueAt(row,'SheetID')
        specificData = system.db.runNamedQuery('YourNamedQuery',{'SheetID':sheetID})

```

---

<div class="post-metadata">

**Author:** ![Alex\_Jaffe](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@Alex\_Jaffe](https://forum.inductiveautomation.com/u/Alex_Jaffe)\
**Post date:** [November 27, 2024, 3:56pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/9 "2024-11-27T15:56:56Z")

</div>

Thank you, that will be helpful in the future

---

<div class="post-metadata">

**Author:** ![Alex\_Jaffe](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@Alex\_Jaffe](https://forum.inductiveautomation.com/u/Alex_Jaffe)\
**Post date:** [November 27, 2024, 3:58pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/10 "2024-11-27T15:58:17Z")

</div>

I already solved it but the query is to find all sheets with that SheetID and change an assigned bit value from 1 to 0 or 0 to 1.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [November 27, 2024, 4:08pm UTC](https://forum.inductiveautomation.com/t/perspective-scripting-how-to-reference-a-specific-cell-in-a-table-in-python/98961/11 "2024-11-27T16:08:06Z")

</div>

If I understand correctly, I think you should be able to do it in one query with something like this:

```python
ids = data.getColumnAsList(data.getColumnIndex('SheetId'))

q = """
update your_table
set column = ~column
where SheetId in ({})
""".format(','.join("?" for _ in ids))
system.db.runPrepUpdate(q, ids)

```