Get difference of two cells and place that value into another cell

Ignition
,
1

Hello!

I am trying to get the difference of the status and goal column and place it in the Behind/ Ahead column. I have looked into the system.dataset functions, but I do not see any mathematical equations within the manual. This is the screenshot below, I am using a regular table form the component palette in vision.
image

Let me know if if you need anymore information!

2

How are you getting the data into the table currently?

You could bind this data to a custom property, then rebuild the dataset, using the calulated value.

My answer will likely change depending on the answer. :wink:

2 Likes
3

Currently it is just through the dataset. I made the table editable and they would input the values within the weekly needs, status and goal. The remaining and Behind/ ahead would be calculated for them. Each row in my table will have differing data, not sure if that would mess up the custom property idea!

Thank you

4

Not at all, though make sure you make the calculated columns un-editable. Though there really isn't a need for a custom property if your data is only ever going to reside in the table, and you don't need to keep a copy of the unmodified dataset.

Also, user data entry should be validated, so I would probably use the cellEdited event on the table to validate the data entry and then an external component to have the user trigger an update to the table when all the data has been entered.

So for instance you might place this script in a Button's actionPerformed script.

#get data from table
data = event.source.parent.getComponent('YourTableName').data

#convert data to a pyDataSet (technically not requiered but makes working with the data much easier
pyData = system.dataset.toPyDataSet(data)

#loop through each row and do calculations
for row in pyData:
    row['Behind / Ahead'] = row['Goal'] - row['Status']

#update the table with the new dataset
event.source.parent.getComponent('YourTableName').data = system.dataset.toDataSet(pyData)
2 Likes
5

If you are looking to keep it inside the table, then consider using a power table instead. It will give you more options. Here’s a window you can play with.

TableUpdate.zip (8.1 KB)

The internal function onCellEdited() can take care if it for you.

def onCellEdited(self, rowIndex, colIndex, colName, oldValue, newValue):
	data = self.data
	data = system.dataset.setValue(data, rowIndex, colIndex, newValue)
	difference = data.getValueAt(rowIndex, 'Status') - data.getValueAt(rowIndex, 'Goal')
	data = system.dataset.setValue(data, rowIndex, 'Behind / Ahead', difference)
	self.data = data
1 Like
6

Thank you so much! That seemed as though it would work but it does say

Traceback (most recent call last):
File "event:actionPerformed", line 9, in
TypeError: com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet$PyRow indices must be integers

I went ahead and tried @JordanCClark solution which worked!

Thank you again for your help!

7

Thank you sir! This worked as intended, I did not think to use a power table for this functionality!

Have a great day!

1 Like
8

You can also use the configureCell() function to color the cells / text, if needed

1 Like
9

I was going to ask about coloring the first header cell, but thought it would be too bothersome lol. Would configureCell() work for that as well?

Thank you

10

configureHeaderStyle() is for that.

def configureHeaderStyle(self, colIndex, colName):
	# Example:
	from javax.swing import SwingConstants
	from java.awt import Color
	config = {'foreground': 'black',
	          'background' : 'lightGray',
			  'horizontalAlignment': SwingConstants.CENTER}
	
	if colName == 'Core':
		config['background'] = Color(217, 217, 0)
		# config['background'] = 'yellow'
	
	return config
1 Like
11

Perfect, thank you again for the valuable information!