# Change specific row background on Perspective Table based upon int value in dataset **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [July 14, 2020, 12:31pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678 "2020-07-14T12:31:50Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 12:31pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/1 "2020-07-14T12:31:50Z")
Greetings. I have a sql query that is returning performance values that I have linked to a Perspective Table. I'm trying to apply conditional formatting on a column that is hidden on the table but in the dataset called "Perf Int". On the Table.props.cells.style.backgroundColor property I've set the following Expression: > if({this.props.data[0]['Perf Int']}\>=95,'#CCFFCC','#FFCCCC') This is changing the background color to green which is the first value, but when it falls below 95 it remains green. I'm sure that I need to loop it through the table, and I believe I'm just calling the column heading but I'm not sure how to bridge the gap between what I've got and where I need it to be at. Can anyone assist? [Example](https://forum.inductiveautomation.com/uploads/short-url/dEWwpoAEScDxJFU7WfL3ww3OUdd.jpeg) ![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/f/5fbd1bc5249edd6e940d1dd1e08f999b369ffc33.jpeg) Thank you all in advance! ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 12:40pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/2 "2020-07-14T12:40:53Z")
I think what's happening with your expression is that your evaluating the first row of the dataset; in your screenshot its \>=95 therefore all rows will be green. You can achieve what you want by reading this post: > [@\[Feedback\] NEW Perspective Table](http://forum.inductiveautomation.com/t/feedback-new-perspective-table/21199/48): > > For anyone wondering how they might be able to add custom row styles (and configuration) based upon a rows value derived from a dataset. You’ll need to create a transform on the bound dataset using a script like this example: 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 r… ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:03pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/3 "2020-07-14T13:03:56Z")
Hey man I appreciate the reply. Unfortunately I’m still learning my way through all of this. Should I attach this as a scripting transform on the data itself, or should I use this as an expression on the row color background binding? ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:06pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/4 "2020-07-14T13:06:17Z")
> [@dholt](#): > > Should I attach this as a scripting transform on the data itself Yes. The end result will include a value and style object; ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d1e509aa8e4a1948b7edbdebbfffd0cda4e18d9.png) You're basically iterating the dataset, keeping all original values, and appending a style object as well for each row. Hope this helps. ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:15pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/5 "2020-07-14T13:15:08Z")
I appreciate the help but I told you I'm very green at this and learning. I'm getting a script error on line 5 and as far as I can tell my indents are correct? ![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/d/3d6e3dee50a94cf4c7acd130ea76fedf64cc9edc.jpeg) > def transform(self, value, quality, timestamp): > """ > Transform the incoming value and return a result. ``` Arguments: self: A reference to the component this binding is configured on. value: The incoming value from the binding or the previous transform. quality: The quality code of the incoming value. timestamp: The timestamp of the incoming value as a java.util.Date """ output_json = [] style_red = {"backgroundColor": "#FFCCCC"} style_green = {"backgroundColor": "#CCFFCC"} 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) == 'Perf Int': if value.getValueAt(row, col) >= '95': row_style = style_red elif value.getValueAt(row, col) <= '94': row_style = style_green row_object['style'] = row_style output_json.append(row_object) return value ``` ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:18pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/6 "2020-07-14T13:18:42Z")
> [@dholt](#): > > I’m getting a script error on line 5 and as far as I can tell my indents are correct? Sounds like **value** is not a dataset perhaps? What does your data bind look like? ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:19pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/7 "2020-07-14T13:19:39Z")
> [@dholt](#): > > return value You'll want to **return output\_json** and not value. ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:21pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/8 "2020-07-14T13:21:04Z")
![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/3/1361c0fd765e85a1c662ab83e4b8eb30484d3e98.jpeg) This is how I’ve currently got it setup, without a transform, when I add the script transform is when I get the error ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:23pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/9 "2020-07-14T13:23:05Z")
Try changing your **Return Format** to dataset. ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:25pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/10 "2020-07-14T13:25:23Z")
Set as dataset, it returns the values fine with no errors, but it also doesn’t apply the color with the script transform haha. I told you man I’m green! ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:27pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/11 "2020-07-14T13:27:55Z")
In your named query your casting **Perf Int** as INTEGER. ```auto if value.getValueAt(row, col) >= '95': ``` You’re comparing an integer to a string. Drop the quotes and you should be good. ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:30pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/12 "2020-07-14T13:30:35Z")
When I drop the quotes, I still get the following script error set on JSON return, and when I put to dataset it goes through without the background changing still: ![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/3/135ffb2428034d7b5c123902cde3df40f98f208c.jpeg) ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:31pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/13 "2020-07-14T13:31:29Z")
Your return is still incorrect. > [@Change specific row background on Perspective Table based upon int value in dataset](http://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/7): > > You’ll want to return output\_json and not value. ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:33pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/14 "2020-07-14T13:33:03Z")
I didn’t see that, my fault there. Even with change still got same script error of array.array object has no attribute “getRowCount” ---
**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\ **Post date:** [July 14, 2020, 1:33pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/15 "2020-07-14T13:33:44Z")
That’s because you changed the return format from dataset. ---
**Author:** ![dholt](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dholt/32/4458_2.png) [@dholt](https://forum.inductiveautomation.com/u/dholt)\ **Post date:** [July 14, 2020, 1:35pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/16 "2020-07-14T13:35:19Z")
Bingo, got it my man, thank you for the help. I had changed back to JSON return, that was my issue at the end. Return dataset, and then return json on the script transform! ![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b3523c2115b759766a249f184daee62cfe4845ff.jpeg) Thank you so much for the help my friend! ---
**Author:** ![hankhaines](https://avatars.discourse-cdn.com/v4/letter/h/ecd19e/32.png) [@hankhaines](https://forum.inductiveautomation.com/u/hankhaines)\ **Post date:** [April 8, 2021, 7:10pm UTC](https://forum.inductiveautomation.com/t/change-specific-row-background-on-perspective-table-based-upon-int-value-in-dataset/36678/17 "2021-04-08T19:10:11Z")
[quote="[Feedback] NEW Perspective Table, post:48, topic:21199"] `"#00AA00"}` [/quote] You can “break” after the row\_style is set and unindent row\_object[‘style’] assignment one level to reduce some CPU usage.