Adding a column to Perspective Table using a script transform on a dataset

Hi all,

I am wondering if this is a good way of doing this.

Dataset comes from a named query, the table.data binding uses this transform to add one additional column to the table, which will allow (I hope) a user to delete a line item from an order.

def transform(self, value, quality, timestamp):
	import system.date
	pyds = system.dataset.toPyDataSet(value)
	returned_rows = []
	for row in pyds:
			row_dict = {}
			columns = list(pyds.getColumnNames()) #returns a list of columns, java Rand.Access List
#			columns.append('Delete')
			for column in columns:
				row_dict[column] = {'value':row[column]}
				
			returned_rows.append(row_dict)
			for i in returned_rows:
				i['Delete'] = False
	return returned_rows

Then, a confirm button will complete the delete action with the DB.
I tried appending the new column name before retrieving the values, but obviously that does not work because the dataset has no column Delete! Also figured out that I had to use the list() function to move from a Java object (Immutable Random Access List) to a Python list.

So, given that:

  • this dataset will always be relatively small
  • only one user should be editing this at a time...(which means I need to find out how to lock out other users from editing the same records/data!)

Is this a good method for adding an additional column?

I got the structure for this script from @bkarabinchak.psi on this thread:

If all you're trying to do is add a column to a dataset, just add the column with system.dataset.addColumn:

def transform(self, value, quality, timestamp):
    return system.dataset.addColumn(
        value,
        value.columnCount - 1,
        [False] * value.rowCount,
        "Delete",
        bool
    )

Well, then... That broke the table's event script for editing the boolean field. I changed the onEditCellCommit script to update the Delete field, but I think it is because the data is bound that will not allow it to accept changes. (Perhaps it's not that it is bound but in a dataset format rather than a list or array?)

def runAction(self, event):
	valueToSet = event.value
#	self.props.data[event.row][event.column] = valueToSet
	self.props.selection.data[0].Delete = valueToSet

The commented out line worked with my previous transform, but neither work with system.dataset.addColumn transform.

Why not add it in the named query with something like

0 as 'Delete'
or
False as 'Delete'

1 Like

That does add the extra column, but does not allow me to edit the value. Not clear if it's because it's a dataset or what, yet.

The problem is not adding the column, or even editing the data, it's the persistence of the user selection.

Why use a check box in an extra column at all? The table component already has the means to "display user selection". Why not set the selection mode to multiple interval and when the user clicks on the delete button you would just loop through the selection data, and build the query to run the delete operation:

rowsToDel = [row['rowIdentifier'] for row in table.props.selection.data]

query = 'Delete FROM YourTable WHERE rowIdentifier in ({})'.format(','.join(['?'] * len(rowsToDel)
system.db.runPrepUpdate(query,rowsToDel)