# Automatically color a row

**URL:** <https://forum.inductiveautomation.com/t/automatically-color-a-row/93515>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [July 22, 2024, 5:38am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515 "2024-07-22T05:38:09Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Emanuel\_Danielczyk](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@Emanuel\_Danielczyk](https://forum.inductiveautomation.com/u/Emanuel_Danielczyk)\
**Post date:** [July 22, 2024, 5:38am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/1 "2024-07-22T05:38:10Z")

</div>

Hello, I am pulling data from a database into a table, and I want the entire row to automatically turn red if the 'status' column contains 'Failed' or 'Error'. This way, the user can immediately see that an error has occurred. Somethin like that:

> **example**
>
> ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/6/f673efb6250a4e48a493e62ab37460c173c533b9.png)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 22, 2024, 5:51am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/2 "2024-07-22T05:51:30Z")

</div>

You will need to add a script transform to the data in the table to transform your row data into a list of dicts for each of the columns, inside which you can add a `style` key with the styling you need (e.g. set the `backgroundColor` to what you want).

E.g. (`value` is assumed to be a list of dicts _not_ a dataset, e.g. if using a named query the return should be set to JSON)

```python
if len(value) > 0:
	headers = value[0].keys()
	data = []
	rowColours = ('#23ef34', '#ff00ff')
	for i, row in enumerate(value):
		newRow = {header: {
				'value': row[header],
				'style': {'backgroundColor': rowColours[i % 2]}
			} for header in headers}
		data.append(newRow)
	return data
else:
	return []

```

---

<div class="post-metadata">

**Author:** ![Emanuel\_Danielczyk](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@Emanuel\_Danielczyk](https://forum.inductiveautomation.com/u/Emanuel_Danielczyk)\
**Post date:** [July 22, 2024, 6:42am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/3 "2024-07-22T06:42:26Z")

</div>

Thank you for the response, I have only recently started working with databases and tables, can you explain the code to me? I am using `system.db.runPrepQuery` and the parameters are coming from another view, which is used for filtering.

---

<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:** [July 22, 2024, 7:31am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/4 "2024-07-22T07:31:48Z")

</div>

> [@Emanuel\_Danielczyk](#):
>
> Thank you for the response, I have only recently started working with databases and tables, can you explain the code to me? I am using `system.db.runPrepQuery` and the parameters are coming from another view, which is used for filtering.

You should create a named query, and use it in a query binding, instead of running the query in a script.  
Then you can follow the instructions below:

Set the return value of the query to `json` instead of `auto` or `dataset`, and add a script transform that looks something like this:

```python
	return [
		{
			'value': row,
			'style': {'backgroundColor': "red" if row['status'] in {'error', 'failed'} else None}
		}
		for row in value
	]

```

Basically, you can change a row style by changing slightly the data structure you pass to the table.  
Instead of having a list of objects, each describing the row's values like this:

```python
[
    {'col1': value_of_col1, 'col2': value_of_col2}, # that's row 1
    {'col1': value_of_col1, 'col2': value_of_col2}, # that's row 2
    ....
    {'col1': value_of_col1, 'col2': value_of_col2}, # that's row n
]

```

You add a layer on top of this, putting all that data in the 'value' key of a new object, and adding a 'style' key next to it, like this:

```python
[
    {
        'value': {'col1': value_of_col1, 'col2': value_of_col2},
        'style': {'backgroundColor': "blue", 'fontWeight', "bold"}
    },
    ...
]

```

Then it's up to you to figure out what styles you want to apply.

---

<div class="post-metadata">

**Author:** ![Emanuel\_Danielczyk](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@Emanuel\_Danielczyk](https://forum.inductiveautomation.com/u/Emanuel_Danielczyk)\
**Post date:** [July 22, 2024, 9:30am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/5 "2024-07-22T09:30:28Z")

</div>

This is not possible as I have an array as a parameter that doesn't have a defined length. I'm writing each element of the array as a single string and adding that to the query with + where\_step\_type + where\_gesamtergebnis + .

> **script**
>
> ```python
> def runAction(self):
> 
> # Please wait Icon sichtbar machen
> self.getSibling("PleaseWait").meta.visible = True
> # defenieren der Parameter für Übergabe
> PARAMETER_TESTGRUPPE = self.view.params.PARAMETER_TESTGRUPPE
> PARAMETER_CLIENT = self.view.params.PARAMETER_CLIENT
> PARAMETER_VIN = self.view.params.PARAMETER_VIN
> PARAMETER_MODELL = self.view.params.PARAMETER_MODELL
> PARAMETER_EINZELERGEBNIS = self.view.params.PARAMETER_EINZELERGEBNIS
> PARAMETER_START_ZEIT = self.view.params.PARAMETER_START_ZEIT
> PARAMETER_ENDE_ZEIT = self.view.params.PARAMETER_ENDE_ZEIT
> where_step_type = ""
> where_gesamtergebnis = ""
> PARAMETER_KOMPONENTENBAUM = self.view.params.PARAMETER_KOMPONENTENBAUM  
> PARAMETER_GESAMTERGEBNIS = self.view.params.PARAMETER_GESAMTERGEBNIS	      
> 	
> # Überprüfen, ob etwas im Komponentenbaum ausgewählt ist. Ja: jedes Element als Array mit jeweiligen AND und OR Operatoren als string ausgeben
> if PARAMETER_KOMPONENTENBAUM:
> where_step_type += "AND ("
> where_step_type += " OR ".join(["sr.step_type = '" + element +"'" for element in PARAMETER_KOMPONENTENBAUM])
> where_step_type += ")"
>     
> # Überprüfen, ob etwas im Gesamtergebnis ausgewählt ist. Ja: jedes Element als Array mit jeweiligen AND und OR Operatoren als string ausgeben
> if PARAMETER_GESAMTERGEBNIS:
> where_gesamtergebnis += "AND ("
> for index, element in enumerate(PARAMETER_GESAMTERGEBNIS):
> where_gesamtergebnis += "ur.uut_status = '" + element + "'"
> if index < len(PARAMETER_GESAMTERGEBNIS)-1:
> where_gesamtergebnis += " OR "
> where_gesamtergebnis += ")"	
> 	
> # query zur Datenausgabe einsetzten mit where_step_type und where_gesamtergebnis	
> query = "SELECT ur.batch_serial_number, ur.station_id, ur.uut_serial_number, ur.updated_date_time, CASE WHEN CHARINDEX('__', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__', sr.step_name) + 2) WHEN CHARINDEX('_ ', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('_ ', sr.step_name) + 2) WHEN CHARINDEX('__', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__', sr.step_name) + 3) ELSE sr.step_name END AS step_name, sr.step_type, ur.uut_status, sr.status, COALESCE(pr.Max_Limit, '') AS Max_Limit, COALESCE(pr.Verify_Values, '') AS Verify_Values, COALESCE(pr.Min_Limit, '') AS Min_Limit, COALESCE(pr.Units, '') AS Units FROM hive_metastore.edl_current.lx_smarttest_uut_result_1_0_0 ur JOIN hive_metastore.edl_current.lx_smarttest_step_result_1_0_0 sr ON ur.id = sr.uut_result JOIN (SELECT step_result AS ID, MAX(CASE WHEN name = 'Max_Limit' THEN data END) AS Max_Limit, MAX(CASE WHEN name = 'Verify_Values' THEN data END) AS Verify_Values, MAX(CASE WHEN name = 'Min_Limit' THEN data END) AS Min_Limit, MAX(CASE WHEN name = 'Units' THEN data END) AS Units FROM hive_metastore.edl_current.lx_smarttest_prop_result_1_0_0 GROUP BY step_result) AS pr ON sr.id = pr.ID WHERE (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 12, 6)) = ? OR ur.uut_serial_number = ?) AND (? = '' OR ? IS NULL OR ur.station_id = ?) AND (? = '' OR ? IS NULL OR ur.batch_serial_number = ?) AND (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 4, 1), SUBSTRING(ur.uut_serial_number, 8, 1), SUBSTRING(ur.uut_serial_number, 5, 3)) = ?) AND (? = '' OR ? IS NULL OR sr.status = ?) AND(? = '' AND ? = '' OR ? IS NULL AND ? IS NULL OR ur.updated_date_time BETWEEN ? AND ?)" + where_step_type + where_gesamtergebnis + " AND sr.step_type IS NOT NULL AND sr.step_type <> '' AND sr.step_type NOT IN ('NI_Flow_Case') AND sr.step_type NOT IN ('NI_Flow_DoWhile') AND sr.step_type NOT IN ('NI_Flow_Else') AND sr.step_type NOT IN ('NI_Flow_ElseIf') AND sr.step_type NOT IN ('NI_Flow_End') AND sr.step_type NOT IN ('NI_Flow_If') AND sr.step_type NOT IN ('NI_Flow_Select') AND sr.step_type NOT IN ('NI_Flow_While') AND sr.step_type NOT IN ('NI_Wait') AND sr.step_type NOT IN ('Battery_Adaptor_Log') AND sr.step_type NOT IN ('Add_AllStepDetails') AND sr.step_type NOT IN ('Add_TestDetails') AND sr.step_type NOT IN ('CCS_Start_Stop') AND sr.step_type NOT IN ('CheckForPreviousResults') AND sr.step_type NOT IN ('Clear_DTCs') AND sr.step_type NOT IN ('Wait_With_Prompt') AND sr.step_type NOT IN ('Config_Retest_Updated') AND sr.step_type NOT IN ('Create_Report_Path') AND sr.step_type NOT IN ('Create_Shop_Summary') AND sr.step_type NOT IN ('Get_DTCs_DB') AND sr.step_type NOT IN ('Get_POI_Build_Data') AND sr.step_type NOT IN ('Get_Product_Line') AND sr.step_type NOT IN ('Get_Test_Location') AND sr.step_type NOT IN ('Get_VIN') AND sr.step_type NOT IN ('Get_Vehicle_Options_Updated') AND sr.step_type NOT IN ('Get_Verify_TLAs') AND sr.step_type NOT IN ('Goto') AND sr.step_type NOT IN ('Initialize_Monitor') AND sr.step_type NOT IN ('NI_Notification') AND sr.step_type NOT IN ('Process_Sequence_Names') AND sr.step_type NOT IN ('SequenceCall') AND sr.step_type NOT IN ('Call_Sub_Sequence') AND sr.step_type NOT IN ('Call_Sub_System_Sequence') AND sr.step_type NOT IN ('Call_Test_Location_Sequence') AND sr.step_type NOT IN ('Stop_Periodic_CAN_Frame') AND sr.step_type NOT IN ('EDAT_Statement') AND sr.step_type NOT IN ('Action') AND sr.step_type NOT IN ('AdditionalResults') AND sr.step_type NOT IN ('ClearDTCUDS') AND sr.step_type NOT IN ('Get_Apps_TLAs') AND sr.step_type NOT IN ('Label') AND sr.step_type NOT IN ('Load_CAN_Database') AND sr.step_type NOT IN ('MessagePopup') AND sr.step_type NOT IN ('Retest_Multiple_Failed_SubSystem') AND sr.step_type NOT IN ('Start_CAN_Chn_Monitor') AND sr.step_type NOT IN ('Terminate_All_Monitors') AND sr.step_type NOT IN ('Tester_Present') AND sr.step_type NOT IN ('Update_GUI_Seq_List') ORDER BY ur.updated_date_time"
> 
> # Parameter in richtiger Reihenfolge definieren 
> params =[PARAMETER_VIN, PARAMETER_VIN, PARAMETER_VIN, PARAMETER_VIN, PARAMETER_CLIENT, PARAMETER_CLIENT, PARAMETER_CLIENT, PARAMETER_TESTGRUPPE, PARAMETER_TESTGRUPPE, PARAMETER_TESTGRUPPE, PARAMETER_MODELL, PARAMETER_MODELL, PARAMETER_MODELL, PARAMETER_EINZELERGEBNIS, PARAMETER_EINZELERGEBNIS, PARAMETER_EINZELERGEBNIS, PARAMETER_START_ZEIT, PARAMETER_ENDE_ZEIT, PARAMETER_START_ZEIT, PARAMETER_ENDE_ZEIT, PARAMETER_START_ZEIT, PARAMETER_ENDE_ZEIT]
> 
> #Prep Query ausführen und in result schreiben
> result = system.db.runPrepQuery(query, params, 'LX_EDL_ST')		
> system.perspective.print(result)
> 
> for row in range(getRowCount()):
> row_object = {}
> system.perspective.print(row_object)
> 		
> # Please wait Icon unsichtbar machen
> self.getSibling("PleaseWait").meta.visible = False
> 
> # Daten in die Tabelle schreiben
> self.props.data = result
> 
> ```

---

<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:** [July 22, 2024, 9:46am UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/6 "2024-07-22T09:46:08Z")

</div>

Well then still use `runPrepQuery` and make the list of dict from the results, it should work the in pretty much the same way. You may have to process the data structure you get, but it shouldn't be too hard.

But I must say that query looks like a red flag to me.  
Here it is reformatted, if someone wants to take a crack at it - I really don't have time for this:

> **query**
>
> ```python
> query = """
> SELECT
> ur.batch_serial_number,
> ur.station_id,
> ur.uut_serial_number,
> ur.updated_date_time,
> CASE
> WHEN CHARINDEX('__', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__', sr.step_name) + 2)
> WHEN CHARINDEX('_ ', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('_ ', sr.step_name) + 2)
> WHEN CHARINDEX('__', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__', sr.step_name) + 3)
> ELSE sr.step_name
> END AS step_name,
> sr.step_type,
> ur.uut_status,
> sr.status,
> COALESCE(pr.Max_Limit, '') AS Max_Limit,
> COALESCE(pr.Verify_Values, '') AS Verify_Values,
> COALESCE(pr.Min_Limit, '') AS Min_Limit,
> COALESCE(pr.Units, '') AS Units
> FROM hive_metastore.edl_current.lx_smarttest_uut_result_1_0_0 ur
> JOIN hive_metastore.edl_current.lx_smarttest_step_result_1_0_0 sr
> ON ur.id = sr.uut_result
> JOIN (
> SELECT
> step_result AS ID,
> MAX(CASE
> WHEN name = 'Max_Limit' THEN data
> END) AS Max_Limit,
> MAX(CASE
> WHEN name = 'Verify_Values' THEN data
> END) AS Verify_Values,
> MAX(CASE
> WHEN name = 'Min_Limit' THEN data
> END) AS Min_Limit,
> MAX(CASE
> WHEN name = 'Units' THEN data
> END) AS Units
> FROM hive_metastore.edl_current.lx_smarttest_prop_result_1_0_0
> GROUP BY step_result
> ) AS pr
> ON sr.id = pr.ID
> WHERE	(? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 12, 6)) = ? OR ur.uut_serial_number = ?)
> AND (? = '' OR ? IS NULL OR ur.station_id = ?)
> AND (? = '' OR ? IS NULL OR ur.batch_serial_number = ?)
> AND (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 4, 1), SUBSTRING(ur.uut_serial_number, 8, 1), SUBSTRING(ur.uut_serial_number, 5, 3)) = ?)
> AND (? = '' OR ? IS NULL OR sr.status = ?)
> AND (? = '' AND ? = '' OR ? IS NULL AND ? IS NULL OR ur.updated_date_time BETWEEN ? AND ?)""" + where_step_type + where_gesamtergebnis + """ AND sr.step_type IS NOT NULL AND sr.step_type <> ''
> AND sr.step_type NOT IN ('NI_Flow_Case')
> AND sr.step_type NOT IN ('NI_Flow_DoWhile')
> AND sr.step_type NOT IN ('NI_Flow_Else')
> AND sr.step_type NOT IN ('NI_Flow_ElseIf')
> AND sr.step_type NOT IN ('NI_Flow_End')
> AND sr.step_type NOT IN ('NI_Flow_If')
> AND sr.step_type NOT IN ('NI_Flow_Select')
> AND sr.step_type NOT IN ('NI_Flow_While')
> AND sr.step_type NOT IN ('NI_Wait')
> AND sr.step_type NOT IN ('Battery_Adaptor_Log')
> AND sr.step_type NOT IN ('Add_AllStepDetails')
> AND sr.step_type NOT IN ('Add_TestDetails')
> AND sr.step_type NOT IN ('CCS_Start_Stop')
> AND sr.step_type NOT IN ('CheckForPreviousResults')
> AND sr.step_type NOT IN ('Clear_DTCs')
> AND sr.step_type NOT IN ('Wait_With_Prompt')
> AND sr.step_type NOT IN ('Config_Retest_Updated')
> AND sr.step_type NOT IN ('Create_Report_Path')
> AND sr.step_type NOT IN ('Create_Shop_Summary')
> AND sr.step_type NOT IN ('Get_DTCs_DB')
> AND sr.step_type NOT IN ('Get_POI_Build_Data')
> AND sr.step_type NOT IN ('Get_Product_Line')
> AND sr.step_type NOT IN ('Get_Test_Location')
> AND sr.step_type NOT IN ('Get_VIN')
> AND sr.step_type NOT IN ('Get_Vehicle_Options_Updated')
> AND sr.step_type NOT IN ('Get_Verify_TLAs')
> AND sr.step_type NOT IN ('Goto')
> AND sr.step_type NOT IN ('Initialize_Monitor')
> AND sr.step_type NOT IN ('NI_Notification')
> AND sr.step_type NOT IN ('Process_Sequence_Names')
> AND sr.step_type NOT IN ('SequenceCall')
> AND sr.step_type NOT IN ('Call_Sub_Sequence')
> AND sr.step_type NOT IN ('Call_Sub_System_Sequence')
> AND sr.step_type NOT IN ('Call_Test_Location_Sequence')
> AND sr.step_type NOT IN ('Stop_Periodic_CAN_Frame')
> AND sr.step_type NOT IN ('EDAT_Statement')
> AND sr.step_type NOT IN ('Action')
> AND sr.step_type NOT IN ('AdditionalResults')
> AND sr.step_type NOT IN ('ClearDTCUDS')
> AND sr.step_type NOT IN ('Get_Apps_TLAs')
> AND sr.step_type NOT IN ('Label')
> AND sr.step_type NOT IN ('Load_CAN_Database')
> AND sr.step_type NOT IN ('MessagePopup')
> AND sr.step_type NOT IN ('Retest_Multiple_Failed_SubSystem')
> AND sr.step_type NOT IN ('Start_CAN_Chn_Monitor')
> AND sr.step_type NOT IN ('Terminate_All_Monitors')
> AND sr.step_type NOT IN ('Tester_Present')
> AND sr.step_type NOT IN ('Update_GUI_Seq_List')
> ORDER BY ur.updated_date_time
> """
> 
> ```

Side note: Why not "merge" all the `sr.step_type NOT IN ...` into just one condition ? That's what `in/not in` is for. Doesn't solve anything about the current issue, but it would make it a bit more readable.

edit: I forgot I have this in my lib, I guess you can use it:

```python
def to_json(ds):
	"""
	Transform a dataset into a list of dictionnaries.
	"""
	keys = ds.columnNames
	return [dict(zip(keys, row)) for row in system.dataset.toPyDataSet(ds)]

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 22, 2024, 4:04pm UTC](https://forum.inductiveautomation.com/t/automatically-color-a-row/93515/7 "2024-07-22T16:04:49Z")

</div>

> [@pascal.fragnoud](#):
>
> ```python
> [
> {
> 'value': {'col1': value_of_col1, 'col2': value_of_col2},
> 'style': {'backgroundColor': "blue", 'fontWeight', "bold"}
> },
> ...
> ]
> 
> ```

Interesting, I didn't know the value could be a dict which becomes the column values. Means less style objects and likely more efficient, I'll have to remember this!