Automatically color a row

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

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)

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 []
2 Likes

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:

	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:

[
    {'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:

[
    {
        '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.

2 Likes

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
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

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
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:

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)]
3 Likes

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!

3 Likes