Changing colour of row based off value in cell in perspective table

Hi,

I just want to change the colour of the rows in my table based off the values on one of the cells. I am unsure on how to go about this in perspective, any help is welcome.

If you want to style individual cells or even a row based off of a value within a row, then you need to provide that as part of the data. The default data in the Table has a simple application of this which you can see for the city of Folsom.

You will want to manipulate your data so that each cell contains a style object, which in turn contains a property key of backgroundColor, where this property has the color you want the cell to have. If you want a cell to not have any additional background, you can leave the cell as a simple value.

In this example, you can see that I’ve kept the original orange background for my city cell, but Ive added a green background to my country cell by providing the cell’s data as an object with value as a key (which is where the displayed value should go) and a style object with an internal backgroundColor property.


I usually include a transform on whatever binding I’m using for my Table data:

	returned_rows = []
	for row in value:
		row_dict = {}
		row_dict['country'] = row.country
		row_dict['city'] = row.city
		population = row.population
		if int(population) < 3000000:
			population = {'value': population, 'style': {'backgroundColor': '#00FF00'}}
		row_dict['population']= population
		returned_rows.append(row_dict)
	return returned_rows

This allows for easy highlighting of values which match a condition:

2 Likes

Is this method only usable if the table data is given as an array of objects? If the original source is a dataset does it need to be transformed?

That’s sort of implied by your example transform, I’m just double checking.

If the original data is a dataset, then yes, it will need to be transformed before reaching the code I supplied.

Is there an example using a dataset because I am pulling my info in from a binding with a query and if so is it different?

1 Like

If your incoming value was a dataset, the example above would be adjusted like this:

	pydset = system.dataset.toPyDataSet(value)
	returned_rows = []
	for row in pydset:
		row_dict = {}
		row_dict['country'] = row['country']
		row_dict['city'] = row['city']
		population = row['population']
		if int(population) < 3000000:
			population = {'value': population, 'style': {'backgroundColor': '#00FF00'}}
		row_dict['population'] = population
		returned_rows.append(row_dict)
	return returned_rows
2 Likes

And to update a bit since I failed to provide an example which would change the color of an entire row:

	pydset = system.dataset.toPyDataSet(value)
    backgroundColor = '#00FF00'  # if condition met
	returned_rows = []
	for row in pydset:
		row_dict = {}
		if int(row['population']) < 3000000:
			row_dict['population'] = {'value': row['population'], 'style': {'backgroundColor': backgroundColor}}
		    row_dict['country'] = {'value': row['country'], 'style': {'backgroundColor': backgroundColor}}
		    row_dict['city'] = {'value': row['city'], 'style': {'backgroundColor': backgroundColor}}
        else:
            row_dict['population'] = row['population']
            row_dict['country'] = row['country']
            row_dict['city'] = row['city']
		returned_rows.append(row_dict)
	return returned_rows
2 Likes

Sorry I had tabs in my sample. You, um … missed some indent issues in your version. :slight_smile:

This worked but then it prevented a feature I had where I would select a row and the values in the row would appear in textboxs to be edited. Is there any way of having both?

You might need to include that each cell is editable as part of the object.

...
row_dict['population'] = {'value': row['population'], 'style': {'backgroundColor': backgroundColor}, 'editable': True}
...

I tried that and it did not work, I get the values into the textbox through selectedRow so I am unsure whether it matters if the cells are editable or not as the textboxes execute an update query and change the value that way.

Oh, I misunderstood what you were asking here:

How are you getting the selected row in order to feed the TextFields their data?

try({../tblRequestView.props.data}[{../tblRequestView.props.selection.selectedRow}, "userID"], "")

I have this binding expression on each textbox to grab the values from the selected row.

That binding expects the original structure (dataset), which we’ve altered with this script to be an array/list.
You’ll need to update the binding references to use the new structure of the data of the table. You’ll want to update the code below and replace “city” with “userID”.

Hi, I tried this and I am getting 2 errors…
#1: text; null found, string expected
#2: Data Quality: Error_ScriptEval(“traceback(most recent call last); file “”, line 3, in transform AttributeError: 'unicode@ object has no attribute ‘value’”)

It is now working but it will only pull in the values of rows that have the “In progress” value which is what dictates whether the row is green or not. So only the green rows are returning values the rest return null.

It sounds like you’re not making the row data homogeneous.

Early examples in this thread suggested you only need modify the piece of data you wanted to color. Later examples moved toward setting the background color of the row. based on some condition.

To use the binding/transform combo I supplied, each row will need to have the same structure whether it meets the condition or not. so you’ll want to adjust the logic so that each row has the background color set, but the color is different based on your condition:

	pydset = system.dataset.toPyDataSet(value)
    backgroundColor = '#00FF00'  # if condition met
	returned_rows = []
    default_background_color = ''
	for row in pydset:
		row_dict = {}
		if int(row['population']) < 3000000:
			row_dict['population'] = {'value': row['population'], 'style': {'backgroundColor': backgroundColor}}
		    row_dict['country'] = {'value': row['country'], 'style': {'backgroundColor': backgroundColor}}
		    row_dict['city'] = {'value': row['city'], 'style': {'backgroundColor': backgroundColor}}
        else:
            row_dict['population'] = {'value': row['population'], 'style': {'backgroundColor': default_background_color}}
            row_dict['country'] = {'value': row['country'], 'style': {'backgroundColor': default_background_color}}
            row_dict['city'] = {'value': row['city'], 'style': {'backgroundColor': default_background_color}}
		returned_rows.append(row_dict)
	return returned_rows
3 Likes

I have it working now, thanks for your help with everything, it gave me a much better understanding.

1 Like

Hi,

I am trying to change the colors based off one cells value also. In trying to follow the examples shown I see that the data provided is the default data. I then want to add the transform to the sample data. Where exactly do I put that transform? If I bind it to the data property I then need to provide it data from a query or something. I made a small table in Sql to mimic the example provided and added a query as well as a transform with the code provided. The transform is not executing in my code. I have it enabled and a poll rate of 2 Seconds. I know the code in the transform is not executing because I added a label and referenced the label in my transform to give the label a value. The label does not get that value.

Thanks for the help.

Is this the only way to change the row color for large tables or is there a simpler way? Don’t mind but want to check before i begin typing.