# Adding a column to Perspective Table using a script transform on a dataset **URL:** \ **Category:** Ignition\ **Tags:** perspective, ignition81\ **Created:** [November 13, 2024, 11:44pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588 "2024-11-13T23:44:10Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [November 13, 2024, 11:44pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588/1 "2024-11-13T23:44:10Z")
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. ```python 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: > [@Preserve or reimplement column settings in a table data script transform?](https://forum.inductiveautomation.com/t/preserve-or-reimplement-column-settings-in-a-table-data-script-transform/54993): > > I have a perspective table that is populated with a named query. One column that comes through as an int had the column made to turn it into a progress bar like so [image] which resulted in [image] I wanted to color the background with a transform script so I did that with the following import system.date pydset = system.dataset.toPyDataSet(value) returned\_rows = [] for row in pydset: row\_dict = {} unStyledColumns = ["plantNumber","plantName","LocalDataTableName","LastUpdated","Las… ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 13, 2024, 11:48pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588/2 "2024-11-13T23:48:50Z")
If all you're trying to do is add a column to a dataset, just add the column with `system.dataset.addColumn`: > **[system.dataset.addColumn | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-dataset/system-dataset-addColumn)** > > Takes a dataset and returns a new dataset with a new column added or inserted into it. ```python def transform(self, value, quality, timestamp): return system.dataset.addColumn( value, value.columnCount - 1, [False] * value.rowCount, "Delete", bool ) ``` ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [November 13, 2024, 11:59pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588/3 "2024-11-13T23:59:25Z")
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_?) ```python 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. ---
**Author:** ![jiml](https://avatars.discourse-cdn.com/v4/letter/j/779978/32.png) [@jiml](https://forum.inductiveautomation.com/u/jiml)\ **Post date:** [November 14, 2024, 1:12pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588/4 "2024-11-14T13:12:09Z")
Why not add it in the named query with something like 0 as 'Delete' or False as 'Delete' ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [November 14, 2024, 6:55pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588/5 "2024-11-14T18:55:25Z")
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. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [November 15, 2024, 12:59pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-to-perspective-table-using-a-script-transform-on-a-dataset/98588/6 "2024-11-15T12:59:23Z")
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: ```python 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) ```