Perspective Table - Style Classes based on Column Date Value

Ignition
1

I'm very new to Ignition, and coding in general. I have a table that uses a named query to populate the data. The fourth column is a date value (stored in datetime data type). I want to dynamically change the style class of each row, comparing that date against a custom property target date, so if it's before (less than?) the target date, it gets one style class, within one month past the target date gets another style class, and further out gets no style class.

How can I accomplish this?

2

You'll have to add a transform to your binding, and loop through your data to set the proper style for your row.

There's an example in the docs:

1 Like
3

@pascal.fragnoud Thank you for this.

So, since my Named Query is returning a dataset, I should use the method in Example 1 including the additional transform to change it to an array if I want the entire row to highlight?

4

You can change the return format of query bindings:

image
image792×277 19.9 KB

You can change it to json to make iterating through the data easier.

edit:
I'd probably do something like this, with return format set to dataset:

Create a script in the script library for the comparison function:

def style_by_date(date, target_date):
	if system.date.isBefore(date, target_date):
		return {'backgroundColor': 'red'}
	elif system.date.monthsBetween(target_date, date):
		return {'backgroundColor': 'blue'}
	else:
		return {}

then in the transform, something like this:

def transform(self, value, quality, timestamp):
	headers = value.columnNames
	target_date = system.date.now()
	return [
		{
			'value': dict(zip(headers, row)),
			'style': some_module.style_by_date(row['date'], target_date)
		} for row in system.dataset.toPyDataSet(value)
	]

you'd obviously have to adapt the styles to whatever you need, and get the actual target date instead of using system.date.now.

Things that you might need to consider:

  • if the target date can be changed and the table needs to update dynamically to reflect that change, put your query binding on a custom property. Then bind the table's data to both that custom property and your target_date property with a structure binding. This will ensure the binding is reevaluated when the date changes, without re-querying the database.
  • You may need to add some validation on the styling function: What if the date is null for example ?
1 Like
5

@pascal.fragnoud

Thanks a ton! I had to modify it a bit. Here's how it ended up:

I added this script to the Library:

def style_by_date(Next_Cal_Due, Current_Date, Cal_Soon_Date):
    if system.date.isBefore(Next_Cal_Due, Current_Date):
        return {'backgroundColor': 'red'}
    elif system.date.isBefore(Next_Cal_Due, Cal_Soon_Date):
        return {'backgroundColor': 'yellow'}
    else:
        return {}

and placed this transform on my data binding:

def transform(self, value, quality, timestamp):

	headers = value.columnNames
	Current_Date = system.date.now()
	dataset = system.dataset.toPyDataSet(value)
	Cal_Soon_Date = system.date.addMonths(Current_Date, 1)
	
	return [
		{
			'value': dict(zip(headers, row)),
			'style': KF_VCal_Table_Style.style_by_date(row['NextCalDue'], Current_Date, Cal_Soon_Date)
		} for row in system.dataset.toPyDataSet(value)
	]

	return result

And now I have my highlighted rows.

I figured out I can use Hex codes for the colors to change them.

6

You can use hex color codes but you should get into the habit of using theme color variables. See Perspective Built-In Themes | Ignition User Manual.

Then rather than defining colors you set the style classes instead.

This makes consistent styling much simpler, controllable and easy to change.

Good web design says, "Keep style and content separate."

1 Like
7

@Transistor

Thank you for the advice.