Using styles in a script transform?

bkarabinchak.psi · January 12, 2022, 6:44pm

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 -

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.

PGriffith · January 12, 2022, 7:17pm

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

bkarabinchak.psi · January 12, 2022, 7:53pm

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 -

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?

lrose · January 12, 2022, 8:02pm

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

Arnaud_Declerck · January 12, 2022, 8:06pm

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

bkarabinchak.psi · January 12, 2022, 8:13pm

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?

bkarabinchak.psi · January 12, 2022, 8:14pm

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

Arnaud_Declerck · January 12, 2022, 8:16pm

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.

matthew.ayre · January 12, 2022, 8:40pm

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 @Arnaud_Declerck 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.

lrose · January 12, 2022, 8:50pm

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.

Arnaud_Declerck · January 13, 2022, 6:50am

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

bkarabinchak.psi · January 13, 2022, 2:42pm

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 -

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

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.

bkarabinchak.psi · January 13, 2022, 2:58pm

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

bkarabinchak.psi · January 13, 2022, 4:26pm

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

lrose · January 13, 2022, 4:36pm

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']}

bkarabinchak.psi · January 13, 2022, 4:45pm

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']}