Using styles in a script transform?

I have a perspective table with a column “Record Count” that varies can be as low as 0 and arbitrarily high.

If the value of the Record Count in a row is 0 to 10, I want to apply a style “BadRecordCount”. If its from 11 to 20, I want to apply a style “WarningRecordCount”, and anything above that gets the style “GoodRecordCount”.

The styles are basic and all look like this just with a different background color -
image

I’ve seen people use data transforms to set the background color, but in those examples I’ve seen the color is hardcoded. How can I use my predefined styles instead of hardcoding it into a script? Or perhaps there’s a way to do this in a different manner using styles? Using styles here is important because I have many tables that will require similar styling. I haven’t touched Perspective in a while and I can’t seem to get this to work.

How are you getting the data onto the table?
You should be able to add a styles key to the row object.

It is coming through as NamedQuery.

I do see the rows object with a style component and setting one of them I do see all the rows take on that style -
image

How can I make an expression such that “If row[‘recordTotal’] <10 BadWarningRecordCount then BadRecordCount if row[‘recordTotal’] < 20 WarningRecordCount Else GoodRecordCount”. Could I do this in an expression or would I need to do it a different way?

Bind the classes property to the record count, then use a map transform to set the class as appropriate.

I think what @PGriffith is speaking is to put the style into the data props like this
{
“value”: { // The rows value.
“city”: { // Custom cell configuration.
“value”: “Folsom”,
“editable”: true,
“style”: {
“backgroundColor”: “ignition-orange”,
“classes”: “some-class”
},
“align”: “top” // Also accepts center and bottom
“justify”: “left” // Also accepts center and right
},
“country”: “United States”,
“population”: 1000000
},
“style”: { // Custom row styles.
“backgroundColor”: “#F7901D”,
“classes”: “some-class”
},
“subview”: { // Row subview configuration. Can override configuration from rows config.
“enabled”: true,
“viewPath”: “myView”,
“viewParams”: {
“param1”: 3
}
}
}

3 Likes

Sorry it’s been so long since using perspective I’m not sure I understand. I have this column which is the column that is going to be responsible for coloring the row

This is the classes property I would bind? Or I would bind the rows style (or classes) property to the rows[‘recordTotal’] somehow?

I’m going to try with the binding first but good to know this is available as an option.

i have an example running for subview (probably the same method for style) at work with a transform script on a named query. I will post here tomorrow.

Hi @bkarabinchak.psi, the others are correct, but for further context this may help: Changing colour of row based off value in cell in perspective table - #17 by cmallonee

The only difference is that instead of defining "style":{"backgroundColor":"#000000"} you would use "style":{"classes":"BadRecordCount"} as @De_Clerck_Arnaud has eluded.

To do this you will need to iterate through your data binding and create the appropriate structure, with the logic in place to assign the appropriate style class.

4 Likes

So digging in a bit, it seems that you have to manipulate the data so that it has the needed structure. A map transform will not work in this instance.

Either way you’ll just need to set the style for that data item so that it matches your criteria.

The link provided by @matthew.ayre will guide you pretty well in this.

2 Likes

Hi @bkarabinchak.psi,

Since a good example is better than a lot of text
TableStyleTransform.zip (8.6 KB)

I use a dataset in the custom props of the table component for this example but you can use it with a named query also (Just make sure that the return format is set to Dataset)
You will need to change also the name of the column where you want to do the check for the style definition (Yellow highlight).

in the zip file i’ve include the view and styles used.

Hope this help

Fyi: if you use this structure with column definition using a viewPath make sure the render is set to auto. Otherwise the cell value will not be send to the render view.

Regards

2 Likes

Thanks for all the help everyone.

Here is what I came up with so far and I just had two follow up questions because I think I am not doing this the “right” or “clean” way

This is what is my current data transform is and it works in that bu

	import system.date
	pydset = system.dataset.toPyDataSet(value)
	returned_rows = []
	for row in pydset:
		row_dict = {}
		constantColumnsBeforeStyle = ["plantNumber","plantName","LocalDataTableName"]
		constantColumnsMiddle = ["LastUpdated","LastRecordDateTime"]
		constantColumnsAfterStyle = ["recordsPerHourAvg","LastAttemp_Status","LastUpdated_Status","LastRecordDateTime_Status"]
		# Set constants first
		for column in constantColumnsBeforeStyle:
			row_dict[column] = {'value':row[column]}
			
#		# Style LastAttempt
#		now = system.date.now()
#		if system.date.hoursBetween(row['LastAttempt'], now) > 1:
#			row_dict['LastAttempt'] = {'value': row['LastAttempt'], 'style':{'classes':'TableStyles/BadRecordCount'}}
#		else:
#			row_dict['LastAttempt'] = {'value': row['LastAttempt']}
#		
		# Middle constants		
		for column in constantColumnsMiddle:
			row_dict[column] = {'value':row[column]}
			
		# Style Record Columns 
		if int(row['recordsTotal']) < 5:
			row_dict['recordsTotal'] = {'value': row['recordsTotal'], 'style': {'classes':'TableStyles/BadRecordCount'}}
		elif int(row['recordsTotal']) < 10:
			row_dict['recordsTotal'] = {'value': row['recordsTotal'], 'style': {'classes':'TableStyles/WarningRecordCount'}}
		else:
			row_dict['recordsTotal'] = {'value': row['recordsTotal'], 'style': {'classes':'TableStyles/GoodRecordCount'}}

		# Set constants after		
		for column in constantColumnsAfterStyle:
			row_dict[column] = {'value':row[column]}

		returned_rows.append(row_dict)
	return returned_rows

Question 1 - am I handling the non styled columns wrong? Is it ok to just do them all in one go or to preserve the order of the columns in the query do I need to do it like how I currently have it?

Question 2 - Normally the recordsTotal is a progress bar -
image

but after my styling it becomes a normal int - how can I preserve the progress bar?
image

Last Question - I have a column LastAttempt that is a datetime in the database and selected directly. I wanted to make it so that if the last attempt was over an hour ago it would be styled red. However my attempt

#		now = system.date.now()
#		if system.date.hoursBetween(row['LastAttempt'], now) > 1:
#			row_dict['LastAttempt'] = {'value': row['LastAttempt'], 'style':{'classes':'TableStyles/BadRecordCount'}}
#		else:
#			row_dict['LastAttempt'] = {'value': row['LastAttempt']}

just gives me a java.lang.NullPointerException hence why its commented out but I also don’t know why it’s giving me that exception specifically.

I know there is a column called LastAttempt in the dataset which is what I thought would throw me the a NullPointException - trying to get a key from the row that doesn’t exist, but it does exist, so I’m lost as to what’s causing this.
image

Answer to my first question is that no the order of the constant columns in my script transform does not matter, I can do them all in one go at the end, which is good news and makes the script simpler. However I still can’t seem to get the number to come through as a progress bar on the styled column or get the date comparison to work without getting a java.lang.NullPointerException

New script -

	import system.date
	pydset = system.dataset.toPyDataSet(value)
	returned_rows = []
	for row in pydset:
		row_dict = {}
		unStyledColumns = ["plantNumber","plantName","LocalDataTableName","LastUpdated","LastRecordDateTime","recordsPerHourAvg","LastAttemp_Status","LastUpdated_Status","LastRecordDateTime_Status"]
			
#		# Style LastAttempt
#		now = system.date.now()
#		if system.date.hoursBetween(row['LastAttempt'], now) > 1:
#			row_dict['LastAttempt'] = {'value': row['LastAttempt'], 'style':{'classes':'TableStyles/BadRecordCount'}}
#		else:
#			row_dict['LastAttempt'] = {'value': row['LastAttempt']}
#				# Set constants first
			
		# Style Record Columns 
		if int(row['recordsTotal']) < 5:
			row_dict['recordsTotal'] = {'value': row['recordsTotal'], 'style': {'classes':'TableStyles/BadRecordCount'}}
		elif int(row['recordsTotal']) < 10:
			row_dict['recordsTotal'] = {'value': row['recordsTotal'], 'style': {'classes':'TableStyles/WarningRecordCount'}}
		else:
			row_dict['recordsTotal'] = {'value': row['recordsTotal'], 'style': {'classes':'TableStyles/GoodRecordCount'}}

		# Unstyled columns
		for column in unStyledColumns:
			row_dict[column] = {'value':row[column]}

		returned_rows.append(row_dict)
	return returned_rows

Just for extra context I’ve confirmed system.perspective.print(str(type(row['LastAttempt']))) gives me <type 'java.sql.Timestamp'>

@matthew.ayre @lrose @PGriffith

I realized the script transform provides a timestamp argument for when the dataset comes in. I tried using that instead of doing now = system.date.now() but the following

		if system.date.hoursBetween(row['LastAttempt'],timestamp) > 1:
			row_dict['LastAttempt'] = {'value': row['LastAttempt'], 'style':{'classes':'TableStyles/BadRecordCount'}}
		else:
			row_dict['LastAttempt'] = {'value': row['LastAttempt']}

still gives me a java.lang.NullPointerException error.

Either of those two lines separately work as expected.

It seems like it is if system.date.hoursBetween(row['LastAttempt'],timestamp) > 1 specifically that is giving me a NullPointerException. Any thoughts?

My guess is that system.date.hoursBetween is having trouble with the java.sql.Timestamp.

What happens if you do something like:

		lastAttempt = system.date.parse(row['LastAttempt'])
		if system.date.hoursBetween(lastAttempt,timestamp) > 1:
			row_dict['LastAttempt'] = {'value': row['LastAttempt'], 'style':{'classes':'TableStyles/BadRecordCount'}}
		else:
			row_dict['LastAttempt'] = {'value': row['LastAttempt']}
1 Like

Ok that got me closer. I realized some of these columns are blank unfortunately because I got a new IllegalArugmentException from system.date.parse(row[‘LastAttempt’]).

This ended up working with that knowledge -

		if row['LastAttempt'] is None:
			row_dict['LastAttempt'] = {'value': row['LastAttempt']}
		elif system.date.hoursBetween(row['LastAttempt'],timestamp) > 1:
			row_dict['LastAttempt'] = {'value': row['LastAttempt'], 'style':{'classes':'TableStyles/BadRecordCount'}}
		else:
			row_dict['LastAttempt'] = {'value': row['LastAttempt']}