Perspective Table - Invididual Cell Conditional Formatting

[msusi]

Hello everyone.

I am having some difficulty with individual cell conditional formatting on the new perspective tables. I am using the script below from another post to apply formatting based upon a cell return. While this works for applying the background colour to the entire row, I need this to be cell specific. I assume I need to make my JSON return more specific/requiring a specific key/value pattern but have not been successful in doing so. Has anyone had luck with this?

Ignition Version: 8.0.5
OS: Ubuntu LTS 18.04

	output_json = []

	style_orange = {"backgroundColor": "#F7901D"}
	style_green = {"backgroundColor": "#00AA00"}
	
	for row in range(value.getRowCount()):
			row_object = {}
			row_value = {}
			row_style = {}
			for col in range(value.getColumnCount()):    
				row_value[value.getColumnName(col)] = value.getValueAt(row, col)
				row_object['value'] = row_value
				
				if value.getColumnName(col) == 'CC_msval':
					if value.getValueAt(row, col) < 95:
						row_style = style_orange
					elif value.getValueAt(row, col) >= 95:
						row_style = style_green
	
				row_object['style'] = row_style
				
			output_json.append(row_object)
			
	return output_json

[msusi]

A quick chat with support has gotten this solved! For those that come across this, see attached.

	output_json = []
	style_orange = {"backgroundColor": "#F7901D"}
	style_green = {"backgroundColor": "#00AA00"}
	for row in range(value.getRowCount()):
		row_object = {}
		row_value = {}
		row_style = {}
		for col in range(value.getColumnCount()):    
#				row_value[value.getColumnName(col)] = value.getValueAt(row, col)
#				row_object['value'] = row_value
			if value.getColumnName(col) == 'CC_msval':
				if value.getValueAt(row, col) < 95:
					row_object[value.getColumnName(col)] = {"value": value.getValueAt(row, col), "style": style_orange}
#						row_style = style_orange
				elif value.getValueAt(row, col) >= 95:
					row_object[value.getColumnName(col)] = {"value": value.getValueAt(row, col), "style": style_green}
#						row_style = style_green
			else:
				row_object[value.getColumnName(col)] = value.getValueAt(row, col)
#				row_object['style'] = row_style
		output_json.append(row_object)
	return output_json

[Transistor]

For anyone wondering where this code goes -

• Select the table.
• Click on the table data binding link.
• Select Add transform.
• Select Script.
• Paste in the code (and remove the existing return value line at the bottom).

Thanks, msusi.

[PGriffith]

A maybe more maintainable variation, if you ever intend on adding other columns you need to style:

	def cc_msval(v, r, c):
		style_orange = {"backgroundColor": "#F7901D"}
		style_green = {"backgroundColor": "#00AA00"}
		return {
			"value": v.getValueAt(r, c),
			"style": style_orange if v.getValueAt(r, c) < 95 else style_green
		}

	transforms = {
		"CC_msval": cc_msval
	}

	return [
		{
			value.getColumnName(col): transforms.get(value.getColumnName(col), lambda v, r, c: v.getValueAt(r, c))(value, row, col)
			for col in xrange(value.columnCount)		
		}
		for row in xrange(value.rowCount)
	]

You could just make a new function, add it to the transforms dictionary, keyed to your column name. The slightly weird syntax at the end is just a nested comprehension to build the required JSON structure.

[Transistor]

Thanks, Phil.
How would you reference a Perspective style in the code? (Adding inline style is naughty!)

[PGriffith]

Just adjust the inner function to return another nested object:

	def cc_msval(v, r, c):
		return {
			"value": v.getValueAt(r, c),
			"style": {
				"classes": "style_orange" if v.getValueAt(r, c) < 95 else "style_green"
			}
		}

[Transistor]

Thanks, Phil. Received and understood!

[pturmel]

Pssst! That’s Paul!

[suryawanshinr2011]

Can we add more than two colour?Actually in our case we want four colours but it is taking time to discover , anyone having solution it would be helpful

[Eugene49]

@Transistor

For anyone wondering where this code goes -

• Select the table.
• Click on the table data binding link.
• Select Add transform.
• Select Script.
• Paste in the code (and remove the existing return value line at the bottom).

I want to clarify.
Did you miss one step above, before "Add Transform".
Bind to the datasource (a dataset in this case)?

Reason I ask is, my datasource, a dataset, comes from a script.
I did my conversion from dataset to object on the script before writing to table.data property.

I like the idea where, the datasource and the formatting script are independent and on different places. Like the quoted above.

[Eugene49]

@PGriffith

Just adjust the inner function to return another nested object:

I wish I could follow, I will try.
Below is sample dataset written to a table, executed on button click.

	headers = ["Month","Score"]
	rows = []
	
	rows.append(["June",98])
	rows.append(["April",90])
	rows.append(["December",95])
	
	ds = system.dataset.toDataSet(headers, rows)
	
	self.getSibling("Table_0").props.data = ds

if score is < 95. cell background must be red, otherwise green.
My goal is clean and fast to understand code.

Output dsObject, Should look like below

[
  {
    "Month": "June",
	"Score": {
      "value": 98,
      "style": {
        "backgroundColor": "green"
      }
    }
  },
  {
    "Month": "April",
	"Score": {
      "value": 90,
      "style": {
        "backgroundColor": "red"
      }
    }
  },
  {
    "Month": "December",
	"Score": {
      "value": 95,
      "style": {
        "backgroundColor": "green"
      }
    }
  }
]

[Eugene49]

Alas! here it is:

#ds is the input source dataset
#dsObject is the Object Version with condition style formatting
	
dsObject= [] #list of row objects
	
styleRed   = {"color": "white", "backgroundColor": "red"}
styleGreen = {"color": "blue",  "backgroundColor": "green"}
	
for row in range(ds.getRowCount()):
	rowObject = {}
	for col in range(ds.getColumnCount()):
		cellObject = {} # has value, and style
		cellStyle  = {} # has the style define above
		cellObject['value'] = ds.getValueAt(row,col) # value = "June"

		#< modify this part of code>
		if ds.getColumnName(col) == 'Score':
			if ds.getValueAt(row,col) < 95:
				cellStyle = styleRed
			else:
				cellStyle = styleGreen
		#</Modify>

		cellObject['style'] = cellStyle #empty for column "Month"
		rowObject[ds.getColumnName(col)] = cellObject
	dsObject.append(rowObject)
	
self.getSibling("Table_0").props.data = dsObject