# Cell color scripting

**URL:** <https://forum.inductiveautomation.com/t/cell-color-scripting/36386>\
**Category:** Ignition\
**Tags:** ignition80, vision\
**Created:** [July 6, 2020, 1:29pm UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386 "2020-07-06T13:29:55Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [July 6, 2020, 1:29pm UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/1 "2020-07-06T13:29:55Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/2/b2008dc26a34b24c52ea222c62f799f2739fd8c8.png)  
i have a power table, in one column Name is ‘SerNum’  
in this picture we can see there are 3 row have the same data , I want to use a different color to highlight it(such as yellow)  
i don’t know how to scripting it.

---

<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:** [July 6, 2020, 2:52pm UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/2 "2020-07-06T14:52:28Z")

</div>

This will highlight the row, but it’s inefficient, as it has to extract the column for every cell.

```python
	config = {'foreground': 'black', 'background' : 'white'}
	
	data = self.data
	testColName = 'ColumnName'
	testColIndex = data.getColumnIndex(testColName)
	testColList = data.getColumnAsList(testColIndex)
	testValue = data.getValueAt(rowIndex, testColName)
	
	if testColList.count(testValue) > 1:
		config['background'] = 'yellow'
	return config

```

I’d recommend limiting the highlight it to the specific column, as then you don’t have to run the check for every cell:

```python
	config = {'foreground': 'black', 'background' : 'white'}

	if colName == 'String Column':
		data = self.data
		testColList = data.getColumnAsList(colIndex)
	
		if testColList.count(value) > 1:
			config['background'] = 'yellow'

	return config

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [July 6, 2020, 5:32pm UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/3 "2020-07-06T17:32:20Z")

</div>

You could also do this on a propertyChange event. If your table data is coming from a query, add `select 0 as duplicate` to your query. Then in the property change event

```auto
if event.propertyName == 'data':
	table = system.dataset.toPyDataSet(event.source.data)
	uniqueData = []
	for i,row in enumerate(table):
		value = table.getValueAt(i,'yourColumnToTest')
		if value in uniqueData:
			isDuplicate = 1
		else:
			uniqueData.append(value)
                        isDuplicate = 0
		table = system.dataset.setValue(table,i,'duplicate',isDuplicate)
	event.source.data = table

```

This will only update when the table data changes. Then in your configureCell function,

```auto
	data = self.data
	if data.getValueAt(rowIndex,'duplicate'):
		return {'background':'yellow'}

```

You can hide the duplicate column in table customizer. This code only highlights the duplicate values, and not the first instance of the duplicate values

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [July 7, 2020, 8:01am UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/4 "2020-07-07T08:01:15Z")

</div>

> [@JordanCClark](#):
>
> colName == 'String Column':

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/4/f42351af1357a323bb3d550b55b435bada8a1117.png)  
it works.  
I found the data.getColumnAslist(ColIndex) don't have explain in the manual ,  
[https://docs.inductiveautomation.com/display/DOC79/Datasets](https://docs.inductiveautomation.com/display/DOC79/Datasets)  
i use print know how it works finally

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [July 7, 2020, 8:30am UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/5 "2020-07-07T08:30:24Z")

</div>

> [@dkhayes117](#):
>
> data = self.data if data.getValueAt(rowIndex,'duplicate'): return {'background':'yellow'}

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/1/e1d88aaa09040e89b8a2c61d0be9b04f32c4d4b7.png)  
i understand, i add one column in the sql for duplicate as signal  
but this will base on the property change to active, thanks for your help give me another angle to think the issue

---

<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:** [July 7, 2020, 9:59am UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/6 "2020-07-07T09:59:39Z")

</div>

The `.getColumnAsList()` method is particular to the `BasicDataset` class and its subclasses, not part of the `Dataset` interface. Fortunately, most datasets in Ignition inherit from `BasicDataset`, so you aren’t likely to find one where it doesn’t exist.

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [July 17, 2020, 1:50am UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/7 "2020-07-17T01:50:01Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/4/544fc983a1a2505a47065ecb6a8e6a6d55ae2ab0.png)  
Dear Jordan：  
previous you give me feedback on how to check one column whether there have the same data, now the situation is a little difference  
you can see the picture, there are 2 lines, my request is if the two column have the same data , the color will become yellow, how to scripting it, thanks a lot.

here is current code:

```
config = {'foreground': 'black', 'background' : 'white'}
if colName == 'SerNum':	
	data = self.data
	ColName = 'SerNum'
	ColIndex = data.getColumnIndex(ColName)
	ColList = data.getColumnAsList(ColIndex)
	Value = data.getValueAt(rowIndex, ColName)

	if ColList.count(Value) > 1:
		config['background'] = 'yellow'
return config
```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [July 17, 2020, 12:22pm UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/8 "2020-07-17T12:22:38Z")

</div>

```auto
	config = {'foreground': 'black', 'background' : 'white'}
	data = self.data
	if data.getValueAt(rowIndex, 'yourColOneName') == data.getValueAt(rowIndex, 'yourColTwoName'):
		config['background'] = 'yellow'
	return config

```

---

<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:** [July 17, 2020, 12:26pm UTC](https://forum.inductiveautomation.com/t/cell-color-scripting/36386/9 "2020-07-17T12:26:52Z")

</div>

Check for duplicate rows using data in two columns:

```python
	config = {'foreground': 'black', 'background' : 'white'}
	
	# Column name of primary value
	colName1 = 'ColumnName1'
	# Column name of secondary value
	colName2 = 'ColumnName2'
	
	if colName == colName1:
		
		data = self.data

		# Get column indexes
		colIndex1 = data.getColumnIndex(colName1)
		colIndex2 = data.getColumnIndex(colName2)
		
		# Get column lists
		colList1 = data.getColumnAsList(colIndex1)
		colList2 = data.getColumnAsList(colIndex2)
		
		if colList1.count(value) > 1:
			# Get all indexes of primary value
			indexList = [index for index, item in enumerate(colList1) if item == value]
			# Create list of secondary values using indexes we just created
			compareList = [colList2[x] for x in indexList]
			# get secondary value
			compareItem = data.getValueAt(rowIndex, colIndex2)
			# Check for duplicates of secondary value
			if compareList.count(compareItem) > 1:
				config['background'] = 'yellow'
	return config

```