Changing the style of table cell based on value in row/column

Ignition
1

Hello, I've been working on this for few days and I think I'm getting close to a solution but I need some guidance. I do want to stress that I am not using a binding. I am running a preped query, the user types into a text box and then clicks a button.

Here is my code:

newData =[]
# RUNNING QUERY	
	results = system.db.runPrepQuery("SELECT  .... MY QUERY", [txt])
		
	# EDIT TABLE BASED ON VALUES
	for row in results:
		system.perspective.print(row)
				
		for col in row:
			system.perspective.print(col)
			cell = row[4]  # Accessing the 'Status' column value				
			row[4] = {"value": "Old", "style": {"backgrounColor": "#00FF00"}}
			newData.append(row)	
	
	
	# POPULATE TABLE WITH QUERY RESULTS
	self.getSibling("Table").props.data = results

The 4 is the column I am examining.
Here is a link to where I was pulling my information form: Link

Here is the error I get:

Traceback (most recent call last):
  File "<function:runAction>", line 29, in runAction
TypeError: can't assign to immutable object

I eventually realized that everyone is using bindings and a JSON format, so I ran this piece of code so that I get a JSON return format:

	results = system.util.jsonEncode(system.dataset.toPyDataSet(system.db.runPrepQuery("SELECT ... MY QUERY, [txt], )))
	
	newData =[]		
	# EDIT TABLE BASED ON VALUES
	for row in results:
		system.perspective.print(row)
				
		for col in row:
			system.perspective.print(col)
			cell = row[col]  # Accessing the 'Status' column value				
			row[col] = {"value": "Old", "style": {"backgrounColor": "#00FF00"}}
			newData.append(row)	
	
	
	# POPULATE TABLE WITH QUERY RESULTS
	self.getSibling("Table").props.data = newData

If I use [col] instead of [4] I get this error:

File "<function:runAction>", line 32, in runAction
TypeError: unicode indices must be integers

If I use [4] instead of [col] I get this error:

  File "<function:runAction>", line 32, in runAction
IndexError: index out of range: 4

LASTLY THE TABLE DOESN'T GET POPULATED AND HERE IS WHAT IT LOOKS LIKE
EXAMPLE

2

There's several confusing (to me) decisions in your code.

Could you explain briefly what you are trying to achieve?

Are you trying to change the style depending on a value?
Are you trying to filter out rows depending on value?

3

My apologies @Daniel.Snyder, at this point it's just a hodgepodge of me throwing things at the wall and seeing what sticks.

The company has a database that the engineers work off. They use a textbox to type in an asset name or perhaps a sheet name. The database then gives them what they want. There is a column called Status, I want to change the cell style "background, font color, font-weight etc" based on the value.

Example: If the cell value is "Old" we change the font to red and the background to grey. If the cell value is "Current" we change the font color to green and the background to white.

4

EXAMPLE
EXAMPLE1184×855 28.1 KB

I'm able to populate the table, just not customize the cells based on their value.

5

If you know which column you want, then you don't have to loop through them. Just access the specific cell. Like this:

for row in results:          
    cell = row[4]

If you want to specify a different style based on the cell value, you'll need to implement that logic somehow. Something along these lines:

  if row["Status"] == "Old":
      style = {'backgroundColor': "#AAAAAA", 'color': "#FF0000"}
  elif row["Status"] == "Current":
      style = {'backgroundColor': "#FFFFFF", 'color': "#00FF00"}
  else:
      style = {}

You are working with different types of objects. In the example, the binding is returning object wrappers for JSON which are mutable. system.db.runPrepQuery returns a PyDataset, which is immutable. You'll have to copy the values over to change them, or use something like system.dataset.setValue() which would return a new dataset.

I would create a new row that you can modify, to then append to newData.

newRow = {key: row[key] for key in results.getColumnNames()}

All together, you could do something like this:

results = system.db.runPrepQuery("SELECT ... MY QUERY", [txt] )
newData = []

for row in results:
    newRow = {key: row[key] for key in results.getColumnNames()}

    if newRow["Status"] == "Old":
        style = {'backgroundColor': "#AAAAAA", 'color': "#FF0000"}
    elif newRow["Status"] == "Current":
        style = {'backgroundColor': "#FFFFFF", 'color': "#00FF00"}
    else:
        style = {}
        
    newRow["Status"] = {'value': newRow["Status"], 'style': style}
    newData.append(newRow)
self.getSibling("Table").props.data = newData
3 Likes
6

Thanks for reaching out, I'll have to try this tomorrow!

7

That worked pretty well, the only issue is that every column and row is populated with the "Old/Current", I am gonna try to... figure it out and then try the data binding option too if I can't manipulate the data so that the new table displays the correct data and the stylized column.

image
image919×191 9.1 KB

8

I made some changes and got this... But now I'm losing my headers, and I jumped a few hoops and converted the list into a dataset and lost my styling.

	# VARS FOR RUNNING QUERY
	if self.getSibling("TextField_Asset").props.text is not None and len(self.getSibling("TextField_Asset").props.text) > 3:        
	    txt = "%" + self.getSibling("TextField_Asset").props.text + "%"
	    query = "SELECT Val1 as Asset, Val2 as Sheet, Val3 as Title, Rev, Status, Path, Val6 as Vendor, Val7 as Category, FileNameModified, ID FROM ProductionData.dbo.DrawingInfo WHERE Val1 LIKE ? AND DocType = 2 ORDER BY Val2, rev DESC"
	    newData = []
	    results = system.db.runPrepQuery(query, [txt])
	    
	    for row in results:
	        newRow = list(row)  # Convert the row to a list
	        
	        if newRow[4] == "Old":  # Access the Status column using integer index
	            status_style = {'backgroundColor': "#AAAAAA", 'color': "#F7901D"}
	            
	        elif newRow[4] == "Current":  # Access the Status column using integer index
	            status_style = {'backgroundColor': "#FFFFFF", 'color': "#00FF00"}
	            
	        else:
	            status_style = {}
	            
	        newRow[4] = {'value': newRow[4], 'style': status_style}  # Update the Status column
	        newData.append(newRow)  # Append the modified row to newData
	        self.getSibling("Table").props.data = newData
	    
	   # Set the dataset as the data for the table, preserving styling
	    table_data = []
	    for rowIndex, rowData in enumerate(newData):
	        row_data_with_style = []
	        for colIndex, cellData in enumerate(rowData):
	            cellValue = cellData['value'] if isinstance(cellData, dict) else cellData
	            cellStyle = cellData.get('style', {}) if isinstance(cellData, dict) else {}
	            row_data_with_style.append({'value': cellValue, 'style': cellStyle})
	        table_data.append(row_data_with_style)
	    
	    self.getSibling("Table").props.data = table_data

image
image925×853 34.3 KB

9

You have to make a deep copy of every row when using dictionaries.

My take on it:

    if self.getSibling("TextField_Asset").props.text is not None and len(self.getSibling("TextField_Asset").props.text) > 3:
	    from copy import deepcopy
		
        txt = "%" + self.getSibling("TextField_Asset").props.text + "%"
        query = "SELECT Val1 as Asset, Val2 as Sheet, Val3 as Title, Rev, Status, Path, Val6 as Vendor, Val7 as Category, FileNameModified, ID FROM ProductionData.dbo.DrawingInfo WHERE Val1 LIKE ? AND DocType = 2 ORDER BY Val2, rev DESC"
        newData = []
        results = system.db.runPrepQuery(query, [txt])

        stylesDict = {'Old'     : {'backgroundColor': '#00FF00', 'color': '#FF0000'},
					  'Current' : {'backgroundColor': '#FFFFFF', 'color': '#00FF00'}
                     }

        colNames = results.columnNames

        # Create a blank version of newRow.
        blankRow = {col : {'value': None, 'style' : {}} for col in colNames}


        for row in results:
	        newRow = deepcopy(blankRow)
            for col in colNames:
                newRow[col]['value'] = row[col]
                if col == 'Status':
                    newRow[col]['style'] = stylesDict[row[col]]
            newData.append(newRow)
        self.getSibling("Table").props.data = newData
1 Like
10

I would not use hard coded "magic" colors. Instead use the provided theme color variables, that away the colors will change with the themes. Will save your self a lot of headaches in the future.

Also, as @JordanCClark said, when you're working with dictionaries you need to deep copy each row, I would, however, caution here as when working with perspective not everything that looks like a dictionary is actually a dictionary and that can cause deepcopy to choke.

I would do something like this:

if self.getSibling("TextField_Asset").props.text is not None and len(self.getSibling("TextField_Asset").props.text) > 3:        
	txt = "%{}%".format(self.getSibling("TextField_Asset").props.text)
	query = """SELECT Val1 as Asset,
					Val2 as Sheet,
					Val3 as Title,
					Rev, 
					Status,
					Path,
					Val6 as Vendor,
					Val7 as Category,
					FileNameModified,
					ID
				FROM ProductionData.dbo.DrawingInfo
				WHERE Val1 LIKE ? AND DocType = 2
				ORDER BY Val2, rev DESC
				"""
	statusStyles = {
					'old':{'backgroundColor':"--neutral-50", 'color':"--qual-4"},
					'current':{'backgroundColor':"--white",'color':"--qual-7"}
					}
	results = system.db.runPrepQuery(query, [txt])
    
	statusData = [{'value':status, 'style':statusStyles.get(status,{})} for status in results.getColumnaAsList(4)]
	
	newData = [{col:{'value':row[col], 'style':{} if not col == 'Status' else statusData[i]} for i,row in enumerate(results)} for col in results.columnNames]
	
	self.getSibling("Table").props.data = newData

Obviously, this is totally untested.

11

You're a hero! :face_holding_back_tears:

I take it I can also edit other properties in the styledict? like the columns width, text alignment etc?

12

That's what self.props.columns is for.

13

Right, I edited them in the property editor, turns out the property is static and it stays. I was afraid the the style would interfere with it.