# Count background color in a power table

**URL:** <https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876>\
**Category:** Ignition\
**Created:** [April 26, 2022, 3:02pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876 "2022-04-26T15:02:48Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 3:02pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/1 "2022-04-26T15:02:48Z")

</div>

I have a script that turns the background “green” of a row based off another column value for that same row. Is there a way to count the how many rows in the same column are green?

---

<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:** [April 26, 2022, 3:06pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/2 "2022-04-26T15:06:22Z")

</div>

How is your table’s data generated, what is your color script doing, and where do you want the count to be ?  
And, more importantly, why ?

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 3:13pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/3 "2022-04-26T15:13:42Z")

</div>

Table data was entered manually for roof fan tracking except one column uses a cell update binding which reads amps from different tags. I have three columns Running , Reduced, and Off. The script reads the amps in each row and in the running column it changes that rows background color to green. Yellow for Reduced and Red for Off. I have a numeric label I want to put the total count of for each one in.

```auto
from java.awt import Font
	
	data = self.data		
	returnValue = {}	
	
	
	
	if (colName == '% of FLA'):
		col1 = self.data.getValueAt(rowIndex, "FLA")
		col2 = self.data.getValueAt(rowIndex, "Amps")
		if col2:
			col3 = int(100*(col2/col1))
			return {'text': col3}

	
	if colName == "Running": 
			col1 = self.data.getValueAt(rowIndex, "FLA")
			col2 = self.data.getValueAt(rowIndex, "Amps")
			if col2:
				col3 = int(100*(col2/col1))
				if col3>=50:
					if data.getValueAt(rowIndex,"Amps" )> .2:
						returnValue["background"] = "Green"	
						#returnValue["text"]= 1
	
	if colName =='Reduced':	
		col1 = self.data.getValueAt(rowIndex, "FLA")
		col2 = self.data.getValueAt(rowIndex, "Amps")
		if col2:
			col3 = int(100*(col2/col1))
			if col3<50:
				returnValue["background"] = "Yellow"
				#returnValue["text"]= 1
	
	if colName =="Off":		
			if data.getValueAt(rowIndex,"Amps" )== 0:
				returnValue["background"] = "Red"
				#returnValue["text"]= 1
	return returnValue

```

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 3:25pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/4 "2022-04-26T15:25:48Z")

</div>

Here is what the screen looks like

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

---

<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:** [April 26, 2022, 3:26pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/5 "2022-04-26T15:26:16Z")

</div>

When and where is that script run ?

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 3:27pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/6 "2022-04-26T15:27:33Z")

</div>

in the power table configureCell Extension Function

---

<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:** [April 26, 2022, 3:38pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/7 "2022-04-26T15:38:19Z")

</div>

Don’t do this of the configureCell script. It gets called repeatedly whenever the table gets redrawn, and might not be called for all cells if some are hidden. It must only affect _display_, not data.

Process your data separately to produce this answer, applying the same logic as for display. You might find my `view()` expression function’s filtering and grouping capabilities helpful.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 3:41pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/8 "2022-04-26T15:41:42Z")

</div>

I read the total number of rows using a custom property on the table with a expression. How would I read the color count the same way?

---

<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:** [April 26, 2022, 3:48pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/9 "2022-04-26T15:48:20Z")

</div>

I’m not sure I can help with finding a solution as I don’t use vision, but I thought I might contribute some code refactoring:

```python
from java.awt import Font
	
hp = self.data.getValueAt(rowIndex, "FLA")
amps = self.data.getValueAt(rowIndex, "Amps")
if amps:
	ratio = int(100*(amps/hp))

if colName == "% of FLA":
	ret = {'text': ratio}
elif colName == "Running":
	if ratio >= 50 and amps > .2:
		ret = {'background': "Green"}
elif colName == "Reduced":
	if ratio < 50:
		ret = {'background': "Yellow"}
elif colName == "Off":
	if amps == 0:
		ret = {'background': "red"}
return ret

```

---

<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:** [April 26, 2022, 3:50pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/10 "2022-04-26T15:50:39Z")

</div>

> [@jshaffer09](#):
>
> How would I read the color count the same way?

You can't. The color isn't stored anywhere. It is regenerated _ **on demand** _ by calling configureCell.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 3:53pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/11 "2022-04-26T15:53:49Z")

</div>

ok, so if I want to turn the background green and put a one in the column. Then can I count the ones?

---

<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:** [April 26, 2022, 3:58pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/12 "2022-04-26T15:58:02Z")

</div>

Yes, that’s one approach, but not in configureCell. Take your color decision out of configureCell and put it in a propertyChange script (or view() expression) to produce a dataset with the color boolean per row of data. Count the true values while generating the data. Write the dataset and the count to custom properties. Then the configureCell function just has to check the boolean in the custom property for the requested row.

---

<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:** [April 26, 2022, 3:59pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/13 "2022-04-26T15:59:51Z")

</div>

If you share your current code, we might be more helpful.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [April 26, 2022, 4:02pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/14 "2022-04-26T16:02:11Z")

</div>

here is what I have currently

> [@jshaffer09](#):
>
> ```auto
> from java.awt import Font
> 	
> data = self.data		
> returnValue = {}	
> 	
> 	
> 	
> if (colName == '% of FLA'):
> col1 = self.data.getValueAt(rowIndex, "FLA")
> col2 = self.data.getValueAt(rowIndex, "Amps")
> if col2:
> col3 = int(100*(col2/col1))
> return {'text': col3}
> 
> 	
> if colName == "Running": 
> col1 = self.data.getValueAt(rowIndex, "FLA")
> col2 = self.data.getValueAt(rowIndex, "Amps")
> if col2:
> col3 = int(100*(col2/col1))
> if col3>=50:
> if data.getValueAt(rowIndex,"Amps" )> .2:
> returnValue["background"] = "Green"	
> #returnValue["text"]= 1
> 	
> if colName =='Reduced':	
> col1 = self.data.getValueAt(rowIndex, "FLA")
> col2 = self.data.getValueAt(rowIndex, "Amps")
> if col2:
> col3 = int(100*(col2/col1))
> if col3<50:
> returnValue["background"] = "Yellow"
> #returnValue["text"]= 1
> 	
> if colName =="Off":		
> if data.getValueAt(rowIndex,"Amps" )== 0:
> returnValue["background"] = "Red"
> #returnValue["text"]= 1
> return returnValue
> 
> ```

---

<div class="post-metadata">

**Author:** ![gdube](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gdube/32/41701_2.png) [@gdube](https://forum.inductiveautomation.com/u/gdube)\
**Post date:** [April 26, 2022, 4:02pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/15 "2022-04-26T16:02:37Z")

</div>

That’s the better way in my mind as well.

If you use a binding on table.data, create a custom property of type `DataSet` on your table on which you’ll recreate your existing binding, then on event.propertyName == ‘custProp’, add your additional columns (eg. Running) and set your modified dataset to table.data.

---

<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:** [April 26, 2022, 4:17pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/16 "2022-04-26T16:17:24Z")

</div>

```python
# propertyChange event on table
# Will write to custom Dataset property 'GreenDS' and
# custom integer property 'NumGreen'.

if event.propertyName == 'data':
  pyds = system.dataset.toPyDataSet(event.newValue)
  greens = []
  count = 0
  for row in pyds:
    green = row['Amps'] and row['Amps']/row['FLA'] >= 0.5 and row['Amps'] > 0.2
    if green:
      count += 1
    greens.append([green])
  event.source.GreenDS = system.dataset.toDataSet(['Green'], greens)
  event.source.NumGreen = count

```

```auto
# Snippet of updated configureCell

	if colName == "Running":
		if self.GreenDS.getValueAt(rowIndex, 0):
			returnValue["background"] = "Green"

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 26, 2022, 4:22pm UTC](https://forum.inductiveautomation.com/t/count-background-color-in-a-power-table/58876/17 "2022-04-26T16:22:51Z")

</div>

My take is a bit different, in that I’d use a dictionary to help count all the statuses and then write it to a dataset. A lookup expression can extract the number you want.

```python
if event.propertyName == 'data':

	dataIn = system.dataset.toPyDataSet(event.newValue)
	GYRheaders = ['green', 'yellow', 'red', 'unknown']

	#Initialize counters
	GYRCounters = {header: 0 for header in GYRheaders}
	
	for row in dataIn:
		if row['Amps']:
			load = int(100 * row['Amps'] / row['FLA'])

			if load >= 50:
				GYRDict['green'] += 1

			elif .2 < load < 50:
				GYRDict['yellow'] += 1

			else:
				GYRDict['red'] += 1

		else:
			GYRDict['unknown'] += 1
	
	dataOut = [[GYRDict(color) for color in GYRheaders]]
	
	event.source.GYRDatasetProp = system.dataset.toDataSet(headers, dataOut)

```