# Named Query, Power table, Row color based off column value

**URL:** <https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779>\
**Category:** Ignition\
**Created:** [November 12, 2018, 7:36pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779 "2018-11-12T19:36:18Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![jaysteffen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jaysteffen](https://forum.inductiveautomation.com/u/jaysteffen)\
**Post date:** [November 12, 2018, 7:36pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/1 "2018-11-12T19:36:19Z")

</div>

This should be fairly easy however I am quite new at this, scoured the forms and much of what I’ve found is quite vague.  
I have a power table running a named query that updates every 5 min. I would like to change the row colors based on the value in column 8. The values in column 8 go up to 400. From 0-90 I would like the row to be red, from 90-120 the row yellow, from 120-400 green. I have tried various scripts in the configureCell to no avail. Can this be done by binding in the Background Color property?

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [November 12, 2018, 7:46pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/2 "2018-11-12T19:46:30Z")

</div>

To do that you would use the component scripting for the power table. The extension function configureCell. You would want to check for the value using something like:

```auto
 val = self.data.getValueAt(rowIndex,"column title")

 if val >= 0 and val <=90:
 	return{'background': 'red'}
 if val >= 91 and val <=120 :
 	return{'background': 'yellow'}

```

---

<div class="post-metadata">

**Author:** ![jaysteffen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jaysteffen](https://forum.inductiveautomation.com/u/jaysteffen)\
**Post date:** [December 3, 2018, 6:56pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/3 "2018-12-03T18:56:41Z")

</div>

The code looks good however, I still get the error ‘return’ outside function. I know this is a beginner python error here are some other beginner things I’ve done to try and resolve this issue. Removed the return (fixes the code but doesn’t change the cell colors), replaced return with print (which again, fixes the code but doesn’t change anything). Can you please point me in a good direction?

---

<div class="post-metadata">

**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:** [December 3, 2018, 7:16pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/4 "2018-12-03T19:16:35Z")

</div>

Did you put the script inside of the power table’s configureCell extension function? That’s where it belongs.

If so and you are still getting exceptions then post your script here and put triple back quotes before and after so that it formats correctly and somebody may be able to spot the problem.

```auto

```

script```

```auto

```

---

<div class="post-metadata">

**Author:** ![jaysteffen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jaysteffen](https://forum.inductiveautomation.com/u/jaysteffen)\
**Post date:** [December 3, 2018, 10:21pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/5 "2018-12-03T22:21:53Z")

</div>

I got some help, much was with my formatting. The first line needed to be indented based on the top line. And else statements needed added as such. I’m posting this so other noobs in the future will have something as a reference. Also, we had to look up the type of data it was in the SQL table that is where int came from.

```auto
value = int(self.data.getValueAt(rowIndex,"SQLrowName"))

	if value >= 0 and value <= 90 :
		return{'background': '#FF0100'}
	else:
		if value >= 91 and value <=120 :
			return{'background': 'yellow'}
		else:
			return{'background': 'green'}

```

---

<div class="post-metadata">

**Author:** ![jaysteffen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jaysteffen](https://forum.inductiveautomation.com/u/jaysteffen)\
**Post date:** [December 3, 2018, 10:39pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/6 "2018-12-03T22:39:51Z")

</div>

How can I get more specific? So, if I want to make different rows have different values to change colors? I tried changing rowIndex to a number in quotes, failed, single quotes, failed. Ideas?

---

<div class="post-metadata">

**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:** [December 3, 2018, 11:17pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/7 "2018-12-03T23:17:38Z")

</div>

The configureCell extension function runs once for every cell in the table. The rowIndex indicates which row the cell being formatted is in.

If you want to rows to have different color then the data must have different values in the column that you are checking the value of.

In the following the 1st row would be colored yellow and the 2nd row green:

```auto
Column1 SQLrowName
----------- -------------------
row 1 val 92
row 2 val 135

```

---

<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:** [December 4, 2018, 1:24pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/8 "2018-12-04T13:24:25Z")

</div>

May I also suggest, in an effort to help you in the future, that you can use the elif (else if) key word. It may not seem like it matters but a nested if statement will execute slower. Which granted for this small example makes no difference, but when you apply the same rules to more complicated scripts it will make a big difference.

There is also the small advantage of making it a bit more readable.

```auto
value = int(self.data.getValueAt(rowIndex,"SQLrowName"))

if value >= 0 and value <= 90 :
	return{'background': '#FF0100'}
elif value >= 91 and value <=120 :
	return{'background': 'yellow'}
else:
	return{'background': 'green'}

```

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [December 4, 2018, 1:30pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/9 "2018-12-04T13:30:11Z")

</div>

JGJohnson is right, the configureCell script runs once per cell so I don’t know as setting a fixed value instead of the rowIndex would work. If your trying to test it out I’d use test data in your table so you can test that the color changes work correctly then bring it over to the table you want to use it on.

Irose beat me to mentioning using elif. I used just if statements in my example because I just wanted it to change away from the default value when I was in a range. When it doesn’t meet the requirements of one of the if’s it would return to the default color taking away my need for a final else statement.

Your question about getting more specific, are you talking about changing based on different columns or?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2018, 2:10pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/10 "2018-12-04T14:10:57Z")

</div>

> [@lrose](#):
>
> Which granted for this small example makes no difference

Well, for this example, no else clauses are necessary at all, since each branch does a **`return`**. This would be the most efficient in this case:

```auto
value = int(self.data.getValueAt(rowIndex,"SQLrowName"))

if value >= 0 and value <= 90 :
	return{'background': '#FF0100'}
if value >= 91 and value <=120 :
	return{'background': 'yellow'}
return{'background': 'green'}

```

---

<div class="post-metadata">

**Author:** ![jaysteffen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jaysteffen](https://forum.inductiveautomation.com/u/jaysteffen)\
**Post date:** [December 4, 2018, 2:25pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/11 "2018-12-04T14:25:09Z")

</div>

I was trying to be specific but after reviewing it, it was vague. What I meant was, how do you create different parameters for each row. So, lets say rows 1-20 I would like it to go red at less than 90, rows 21-30 I would like it to go red at less than 60, how would I define different values at different rows?

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [December 4, 2018, 2:32pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/12 "2018-12-04T14:32:13Z")

</div>

I would add another and another set of and’s to your if statements. I’m using pturmel’s code example as a starting point. I haven’t tested it but just checking the row index as part of your if statement should allow you to set different colors for different ranges. It just means adding more conditional if statements.

```auto
value = int(self.data.getValueAt(rowIndex,"SQLrowName"))

if value >= 0 and value <= 90 and rowIndex >= 1 and rowIndex <= 20:
	return{'background': 'red'}
if value >= 91 and value <=120 and rowIndex >= 1 and rowIndex <= 20:
	return{'background': 'yellow'}
if value >= 0 and value <= 60 and rowIndex >= 21 and rowIndex <= 30:
	return{'background': 'red'}
if value >= 61 and value <=120 and rowIndex >= 21 and rowIndex <= 30:
	return{'background': 'yellow'}
return{'background': 'green'}

```

---

<div class="post-metadata">

**Author:** ![jaysteffen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jaysteffen](https://forum.inductiveautomation.com/u/jaysteffen)\
**Post date:** [December 4, 2018, 2:36pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/13 "2018-12-04T14:36:21Z")

</div>

Thank you all: bpreston, JGJohnson, Lrose and pturmel. This has been a great learning experience. I hope this will help others in the future with similar struggles. I have the Ignition book which is excellent however, I think I need to invest more time in learning python any book or website recommendations?

---

<div class="post-metadata">

**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:** [December 4, 2018, 2:46pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/14 "2018-12-04T14:46:10Z")

</div>

Here’s another way to approach what bpreston is doing in his last reply. Both approaches should work fine (although neither have been tested).

```auto
value = int(self.data.getValueAt(rowIndex,"SQLrowName"))

if rowIndex <=20:
	if value < 90:
		return{'background': 'red'}
	if value < 120:
		return{'background': 'yellow'}
elif rowIndex <= 30:
	if value < 60:
		return{'background': 'red'}
	if value < 120:
		return{'background': 'yellow'}

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2018, 3:07pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/15 "2018-12-04T15:07:54Z")

</div>

For truly configurable behaviour, you could include the desired thresholds for each row in your DB, and include those columns in your query. Set those columns to hidden, and use them like so:

```auto
value, yellow, red = [int(self.data.getValueAt(rowIndex,c)) for c in ["SQLrowName", "YellowThreshold", "RedThreshold"]]

if value < red:
    return {'background': 'red'}
if value < yellow:
    return {'background': 'yellow'}
return {'background': 'green'}

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 4, 2018, 3:12pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/16 "2018-12-04T15:12:35Z")

</div>

If you really want to be terse…

```auto
value, yellow, red = [int(self.data.getValueAt(rowIndex,c)) for c in ["SQLrowName", "YellowThreshold", "RedThreshold"]]
return {'background': 'red' if value<red else ('yellow' if value<yellow else 'green')}

```

Python’s special “if” syntax for expressions can be handy.

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [February 28, 2019, 1:50pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/17 "2019-02-28T13:50:31Z")

</div>

I have a table where I alternate the row color white/grey. Now we would like to keep this the same while adding a feature that will high light a row if it’s greater than 100 in column “value”.  
Below is my last attempt. I thought it worked until I checked the table this morning and there was two rows with a value greater than 100 high lighted and one that was not high lighted. The two high lighted rows were odd rows and the other one was an even row.

```
cellConfigurations = {}
value = int(self.data.getValueAt(rowIndex,"value"))`
if selected:
    cellConfigurations['background'] = self.selectionBackground
elif rowView % 2 == 0:
    cellConfigurations['background'] = system.gui.color(255,255,255)
elif value > 100:
    cellConfigurations['background'] = system.gui.color(255,138,138)
else:
    cellConfigurations['background'] = system.gui.color(230,230,230)
return cellConfigurations
```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 28, 2019, 2:08pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/18 "2019-02-28T14:08:36Z")

</div>

Swap the order of your two `elif` blocks.

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [March 4, 2019, 4:37pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/19 "2019-03-04T16:37:51Z")

</div>

Perfect, it’s always the little things that trip me up.  
Thanks

```
if selected:
	cellConfigurations['background'] = self.selectionBackground
elif evalue > 100:
			cellConfigurations['background'] = system.gui.color(255,138,138)
elif rowView % 2 == 0:
	cellConfigurations['background'] = system.gui.color(255,255,255)
else:
	cellConfigurations['background'] = system.gui.color(230,230,230)
return cellConfigurations
```

---

<div class="post-metadata">

**Author:** ![Tony\_DiBattista](https://avatars.discourse-cdn.com/v4/letter/t/c2a13f/32.png) [@Tony\_DiBattista](https://forum.inductiveautomation.com/u/Tony_DiBattista)\
**Post date:** [September 24, 2022, 7:35pm UTC](https://forum.inductiveautomation.com/t/named-query-power-table-row-color-based-off-column-value/20779/20 "2022-09-24T19:35:25Z")

</div>

> [@bpreston](#):
>
> `val = self.data.getValueAt(rowIndex,"column title")`

It worked for me 5 yrs later, thx 👍 👍 👍