Vision Table Scripting

Hello, Looking for some help with Table Scripting.

The table has 2 date columns (Need by Date and First Dispatch). I would like to add a conditioning scripting with adding background color to entire row
if First Dispatch <= Need by date - 4Days : Color to Yellow.

Any help/tips?

image

You could use a power table, then under scripting you could write code to set the background color in the configureCell script.

Otherwise if you want/need to use a regular table, it looks like you could add a column that is a boolean value which has the value of First Dispatch <= Need by date - 4Days. Then you could use the values in that column to map the colors of each row. You can find these settings under Customizers > Table Customizer > Background Color Mapping. If you use this route, you would then want to go into Customizers > Table Customizer > Column Configuration and set the visibility of this new boolean column to false, so it doesn’t show in your table.

I don’t know how you are getting the data, but if it is coming from a database, it would be pretty easy to calculate the boolean value in your query, then use that to map the background color. Otherwise, the power table solution may be the easier route.

2 Likes

a script like this in the configureCell script of a power table should be close to what you are looking for, I think:

	if self.data.getValueAt(rowIndex, 'First Dispatch') <= system.date.addDays(self.data.getValueAt(rowIndex, 'Need By Date'), -4):
		return {'background': 'yellow'}
	else:
		return {'background': 'white'}
2 Likes

This is the script I was thinking to implement.

I am getting the following error…

The indentation of that function didn’t paste correctly. Since the code you put into the editor here is within the function configureCell that is defined at the top of the window, you need the if to be indented once to the right in order to follow python’s whitespace rules.
this is the indentation that you want:

sorry the script no longer matches the one I sent earlier. I deleted that script and wrote this simpler one to show the indentation you want…

1 Like

Thanks for this clarification.

The following script is still not working:
if self.data.getValueAt(rowIndex, ‘First Dispatch’) <= system.date.addDays(self.data.getValueAt(rowIndex, ‘First Delivery Need by’), -40):
return {‘background’:‘Yellow’}
else:
return {‘background’:‘white’}

The first row’s background should fill ‘yellow’

the error on output console is:
14:09:50.340 [AWT-EventQueue-2] ERROR Vision.Components.AdvancedTable - Error invoking extension method.
org.python.core.PyException: Traceback (most recent call last):
*** File “”, line 34, in configureCell***
TypeError: addDays(): 1st arg can’t be coerced to java.util.Date


image

From the error message I can see that the part of your code that is getting the date values from the table’s data property is not returned a datetime object. If I had to guess, that is either giving you the date as a integer (milliseconds since unix epoch) or maybe the string representation of the date. What you need to do is pull those values from the dataset before your if statement, then convert them to datetime objects.

If it is a string, then you can use the function system.date.parse to turn it into a datetime obejct, and if it is an integer representing milliseconds since epoch you can use the system.date.fromMillis function to convert it.

This is just a guess, so I very well could be wrong. If you store those values into variables above your if statement and print out their types and values it may give you more insight on what the solution is.

2 Likes

Thank you so much!

I was able to get this working with the following script:

    from datetime import datetime
from datetime import timedelta

#Define dates
firstdispatch = self.data.getValueAt(rowIndex, 'First Dispatch')
firstdelneedby = self.data.getValueAt(rowIndex, 'First Delivery Need by')
#Define date format
firdisdate = datetime.strptime (firstdispatch, '%m/%d/%y')
firdelneedby = datetime.strptime (firstdelneedby, '%m/%d/%y')

#Substract 10 days from need by
needby = firdelneedby - timedelta(days = 10)

#Date range check
if needby > firdisdate:

	return {'background':'yellow'}
else:
	return {'background':'red'}
1 Like

Just a note that there is no need to import the datetime package. Ignition’s built-in system.date.* functions will do everything you need, and be a bit more readable IMHO.


#Define dates
firstdispatch = self.data.getValueAt(rowIndex, 'First Dispatch')
firstdelneedby = self.data.getValueAt(rowIndex, 'First Delivery Need by')

firdisdate = system.date.parse(firstdispatch, 'MM/dd/yyyy')
firdelneedby = system.date.parse(firstdelneedby,'MM/dd/yyyy')

#Subtract 10 days from need by
needby = system.date.addDays(firdelneedby,-10)

if system.date.isAfter(needby,firdisdate):
    return {'background':'yellow'}
return {'background':'red'}
2 Likes

I would also recommend making your dataset’s columns actual Date types and only formatting them for presentation. Parsing strings into dates is a relatively expensive operation (formatting is as well, but you’re doing more parsing than formatting in this use case).

2 Likes