Hello everyone,
Does anyone know how to change the background color of a specific cell (follow some condition) of the component “Table” in Perspective via a script ?
I have a componant Table (data binded to a NameQuery Dataset). I would like to change the background color of some cell (follow my condition) via a script Jython
Thank you so much
Hi @truonghaibk94 this may be best done in a script transform of the database binding.
You can loop through the dataset and form an array of dictionaries for each element like this:
data
[
{
"colTitle1": {
"value": "SomeValue",
"style": {
"backgroundColor": "#F7901D",
"classes": "SomeClass"
}
},
"colTitle2": {
"value": "SomeValue2",
"style": {
"backgroundColor": "#FFFFFF",
"classes": "SomeClass"
}
}
}
{
"colTitle1": {
"value": "SomeValueOnNewRow",
"style": {
"backgroundColor": "#00000",
"classes": "SomeClass"
}
},
"colTitle2": {
"value": "SomeValueOnNewRow2",
"style": {
"backgroundColor": "#FFFFFF",
"classes": "SomeClass"
}
}
}
]
Basically instead of just having key/value pairs for the columns, turn them into a dictionary in which you can specify value and style properties.
If you place down a a table from the component palette with the default data in you can see this in action for the first cell. It also shows you can do much more than styles.
If you would like any information on how to script this, then let me know.
It works perfectly .
Thank you so much 
@matthew.ayre Do you know how to change the order of the colonne after it ? It's a dictionary and Ignition reorder my dataset after convert to an array of dictionary
Hi @truonghaibk94, would you be able to provide an example of your dataset or images of the table before and after to highlight your issue?
This is just so I can understand your question correctly. Just to confirm, your issue is that the data in the table is not ordered correctly?
This is Before : [Name => Enabled => State => Driver => Description ]
This is after : [Description => State => Enabled => Driver => Name ]
My dataset is with the order “Name , Enable, State,Driver,Description”.
I did a loop that add a pair (key:value) to a dictionary for each member of final array.
I added with the order “Name , Enable, State,Driver,Description”. But when we work with a dictionary, every time we add a new pair (key,value), it reorder the list of key. And finally, Ignition take this order to display in the table [Description => State => Enabled => Driver => Name ].
I would like to keep my order after add the style
Hi @truonghaibk94, you can set the order of your columns using the table’s ‘columns’ prop. The order they appear in this prop will be the order they appear in the table. Make sure you explicitly define the field property in the array object so that it matches the column title.
You can also control many other things for individual columns (such as headers, footers, etc.) on the columns prop. For more information refer to the columns property in the manual here
Thank you so much, you made my date
Hello Matthew,
Sorry if I am replying to an old post.
I would like to use this script in my table, however, is it possible to have this script dynamic?
This is what I am trying to do:
Changing PV column based on LL and HL value.
I am already changing the cell color using json method but wanted to try the script you provided instead.
Hi @pkhoshroo, I assume you are trying to dynamically change the colour as the PV changes?
You could loop through the rows and use an if statement to set the colour based on your HL and LL conditions. Not sure if there is something more efficient off the top of my head.
Hello @matthew.ayre
You are correct!
This is what I did in JSON format and it worked. Now I am trying to use the scripting method you suggested but not sure if I can apply the same if statement formatting in the script you provided.
output_json = []
style_red = {"classes": "CellColor/AboveHigh"}
style_green = {"classes": "CellColor/BelowLow"}
style_yellow = {"classes": "Between"}
for row in range(value.getRowCount()):
row_object = {}
for col in value.getColumnNames():
cell_object={}
cell_style={}
cell_object['value']=value.getValueAt(row,col)
if col == 'process_value':
if value.getValueAt(row,col)>=value.getValueAt(row, 'low_limit') and value.getValueAt(row,col)<= value.getValueAt(row, 'high_limit'):
cell_style= style_green
elif value.getValueAt(row,col)>value.getValueAt(row, 'high_limit'):
cell_style= style_red
elif value.getValueAt(row,col)<value.getValueAt(row, 'low_limit'):
cell_style= style_yellow
cell_object['style']=cell_style
row_object[col]=cell_object
output_json.append(row_object)
return output_json
The following code works for me with the following conditions:
# Note: due to formatting issues, your script might throw a parse error here because it is encountering
# spaces instead of an indent. delete the spaces before output_json and replace them with
# indents. Do the same for this comment or remove it entirely.
output_json = []
style_red = {"classes": "Above"}
style_green = {"classes": "Below"}
style_yellow = {"classes": "Between"}
for row in range(value.getRowCount()):
row_object = {}
for col in value.getColumnNames():
cell_object={}
cell_style={}
cell_object['value']=value.getValueAt(row,col)
if col == 'process_value':
if value.getValueAt(row,col)>=value.getValueAt(row, 'low_limit') and value.getValueAt(row,col)<= value.getValueAt(row, 'high_limit'):
cell_style= style_yellow # between
elif value.getValueAt(row,col)>value.getValueAt(row, 'high_limit'):
cell_style= style_red
elif value.getValueAt(row,col)<value.getValueAt(row, 'low_limit'):
cell_style= style_green # below
cell_object['style']=cell_style
row_object[col]=cell_object
output_json.append(row_object)
return output_json
Thank you very much @cmallonee for your valuable time and response.
I am able to get the cell color with this script without any problem; however, my problem is with the edit cell command. So I have a simple script on “onEditCellCommit”:
rowIndex=event.row # row number of the commit
colName=event.column # column name of the commit
newValue=event.value
data = self.props.data # Dataset [86R ? 8C]
newData = system.dataset.setValue(data, rowIndex,colName,newValue)
self.view.custom.part.spec.data = newData
I am able to edit my cells with the script above without any problem but as soon as I apply the cell color to that script, I get this error:
setValue(): 1st arg can’t be coerced to com.inductiveautomation.ignition.common.Dataset
output_json=[]
style_red = {"classes": "Above"}
style_green = {"classes": "Between"}
style_yellow = {"classes": "Below"}
for row in range(value.getRowCount()):
row_object = {}
for col in value.getColumnNames():
cell_object={}
cell_style={}
cell_object['value']=value.getValueAt(row,col)
if col == 'process_value':
if value.getValueAt(row,col)>=value.getValueAt(row, 'low_limit') and value.getValueAt(row,col)<= value.getValueAt(row, 'high_limit'):
cell_style= style_green
elif value.getValueAt(row,col)>value.getValueAt(row, 'high_limit'):
cell_style= style_red
elif value.getValueAt(row,col)<value.getValueAt(row, 'low_limit'):
cell_style= style_yellow
cell_object['style']=cell_style
row_object[col]=cell_object
output_json.append(row_object)
return output_json
Been on this for two days now and I could not figure out what is the problem. I changed the dataset to json and tried to apply the transform script and it did not work.
I change the dataset in editcellcommit script to pydataset but that did not solve the problem either.
Okay, so there's a lot to unpack here.
This is because the data of the table is a list - not a Dataset. It's important to remember that targeted cell styling is not compatible with datasets, so you previously converted the data property of the table to a list of objects in order to apply styling.
You're encountering this error because you're treating your data in an inadvisable manner. I recommend only having one data source for area of data. In your current code, you're treating a view.custom.part.spec.data as the source for your table, but then you're treating the table as the basis for updating the view property. Try this instead:
rowIndex=event.row # row number of the commit
colName=event.column # column name of the commit
newValue=event.value
# Below, I've referenced the source-of-truth dataset instead of the "translated" props.data of the Table
data = self.view.custom.part.spec.data # Dataset [86R ? 8C]
newData = system.dataset.setValue(data, rowIndex,colName,newValue)
self.view.custom.part.spec.data = newData
Even this approach, however, assumes that the custom property is not a binding, which I suspect it is. If that property is the result of a binding, then you should be editing whatever source drives that binding (perhaps a query to update a SQL table).
Thank you very much, @cmallonee !!!
That fixed the problem!!! Thanks a milion :))))))
How would this look if I was using a query to update a SQL table?
Can you show me where you are scripting this?
I want a similar result as @pkhoshroo.
I populate a table with my data and I want to change the cell color based on the value like @pkhoshroo has. Can I populate the table via a button and then use a binding on the data property?
You usually do one or the other, not both.
If you want something similar to what was originally asked, then you can use what was already explained.
If it's not enough, you'll have to explain why.
They're using a binding and then a transform to get their desired results correct?
If so I'll try it that way and here's what I have so far:
So I just need to add the transform correct? I'll read more and see how others did it. I'm sure you'll hear from me in about half hour or perhaps tomorrow.