Power table cell colors with comparisons

Ignition
1

Good afternoon!

I have been through several different items on the forum related to this, but none of the ones I have found have led me to a solution.

I have a power table that contains numerous readings. The first 6 columns of the table contain general details of the items the readings pertain to (i.e. Location, ID, Tool Name, Date, Upper Limit, Lower Limit). After the Lower Limit column, there are 15 columns containing the readings (i.e. Reading #1, Reading #2, etc. etc.). Effectively, each row is a unique tool and has 15 readings.

What I am trying to do is change the background of the cell based on the reading value, making it red if it is above the value in the upper limit column for that tool and blue if it is below the value in the lower limit column. Any value that falls within the two ranges simply stays with the default white background. However, the columns containing the tool details prior to the readings should remain with the default white background.

Below is my code:

data = self.data
	
	if data.getValueAt(colIndex, 'Reading #1') > data.getValueAt(rowIndex, 'Upper Limit'):
		return {'background': '#FF0000'}
	elif data.getValueAt(colIndex, 'Reading #1') < data.getValueAt(rowIndex, 'Lower Limit'):
		return {'background': '#0096FF'}

It is very sporadic with the coloring. Cells that should be getting colored red are blue, vice versa, others that should be colored are not, and it is even sporadic throughout the first 6 columns as well.

What am I missing?

2

So I found what I missed in terms of keeping the cell coloring out of the first few columns in the table (I think I’d been staring at it too long :slightly_smiling_face: ). I also added another statement that should color cells that fall within the upper and lower limit green.

However, this is still not coloring the cells properly. As you can see in the screenshot, there are some values that are colored correctly, while others are not.

Cell Coloring
Cell Coloring695Ă—373 84.6 KB

The code now looks like this:

	data = self.data
	
	if colIndex > 5:
		if data.getValueAt(colIndex, 'Reading #1') > data.getValueAt(rowIndex, 'Upper Limit'):
			return {'background': '#FF0000'}
		elif data.getValueAt(colIndex, 'Reading #1') < data.getValueAt(rowIndex, 'Lower Limit'):
			return {'background': '#0096FF'}
		elif data.getValueAt(colIndex, 'Reading #1') >= data.getValueAt(rowIndex, 'Lower Limit') and data.getValueAt(colIndex, 'Reading #1') <= data.getValueAt(rowIndex, 'Upper Limit'):
			return {'background': '#73C6B6'}```
3

It looks like the problem is your hard coded column names. You are making all determinations based on the value in the column “Reading #1”. Also, your comparison uses colIndex where it should have used rowIndex. However it’s not neccesary anyways because you can use the “value” property to compare the value of the cell against the limits.

Here’s how I would do it.

if colIndex > 5:
    attributes = {'background':  "FFFFFF"}
    if value > data.getValueAt(rowIndex, "Upper Limit"):
        attributes["background"] = "FF0000"
    elif value < data.getValueAt(rowIndex, "Lower Limit"):
        attributes["background"] = "0096FF"
    return attributes
1 Like
4

Also, consider loading your limits into local variables so you don’t look them up in the dataset for all of your branches. You want this extension method to be screaming fast.

1 Like
5

Thank you both!

Here is what I wound up with, and it seems to run quite fast.

	data = self.data
	upper = data.getValueAt(rowIndex, "Upper Limit")
	lower = data.getValueAt(rowIndex, "Lower Limit")
	
	if colIndex > 5:
		attributes = {'background': "4DED30"}
				
		if value > upper:
			attributes["background"] = "FF0000"
		elif value < lower:
			attributes["background"] = "0096FF"
		return attributes
1 Like
6

That’s great. However if you can tolerate a little nit-picking, for optimization purposes the set point values are not needed unless the column index is greater than 5 so it would be more efficient to only grab those values if the column index is greater than 5. In terms of speed this is probably negligible but still keeping the mindest of doing things the most efficient way possible can be a help when problem solving.

	data = self.data
	
	if colIndex > 5:
		attributes = {'background': "4DED30"}
        upper = data.getValueAt(rowIndex, "Upper Limit")
       	lower = data.getValueAt(rowIndex, "Lower Limit")				
		if value > upper:
			attributes["background"] = "FF0000"
		elif value < lower:
			attributes["background"] = "0096FF"
		return attributes

[/quote]

1 Like
7

I actually did have it that way and then decided to declare outside of the if statement. :laughing:

Thanks so much for the help!