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

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?

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:

 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'}
1 Like

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?

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.

script```

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.

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'}
1 Like

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?

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:

Column1         SQLrowName
-----------     -------------------
row 1 val        92
row 2 val        135
1 Like

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.

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'}
2 Likes

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?

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:

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'}
3 Likes

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?

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.

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'}

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?

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

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'}

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:

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'}

If you really want to be terse…

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.

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

Swap the order of your two elif blocks.

1 Like

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