You could start with adding a custom dataset property to the power table. Call it originalData. Then bind that property to the SQL query to get the noteless data from the database. The query will have to include a primary key
Then, you could create a dataset memory tag with two columns: “name of Primary Key” and “Notes”
Afterwards, you would be able to create a custom method called “refreshTable”
The code for the custom method would look like this:
system.db.refresh(self,"originalData")
dataSetOriginal = self.originalData
dataSetNotes = system.tag.read('[default]yourTagPath/DatasetTag').value
notesColumnHeader = "Notes"
notesColumnData = []
for row in range(dataSetOriginal.getRowCount()):
notesForPrimaryKeyExists = "false"
for noteSearch in range(dataSetNotes.getRowCount()):
if dataSetOriginal.getValueAt(row, "Primary Key") == dataSetNotes.getValueAt(noteSearch, "Primary Key"):
notesColumnData.append(dataSetNotes.getValueAt(noteSearch, "Notes"))
notesForPrimaryKeyExists = "true"
break
if notesForPrimaryKeyExists == "false":
notesColumnData.append(" ")
self.data = system.dataset.addColumn(dataSetOriginal,notesColumnData,notesColumnHeader, str)
This will tie your notes to your database information based on the primary key.
With this approach, the refreshTable method will need to be called from the initialize extention function
self.refreshTable()
Finally, the dataset tag will need to be updated during edits using the onCellEdited extension function. That code will look like this:
self.data = system.dataset.setValue(self.data, rowIndex, colIndex, newValue)
editedDataHeaders = ["Primary Key", "Notes"]
editedData = []
for row in range(self.data.getRowCount()):
editedData.append([self.data.getValueAt(row, "Primary Key"), str(self.data.getValueAt(row, "Notes"))])
system.tag.write('[default]yourTagPath/DatasetTag', system.dataset.toDataSet(editedDataHeaders, editedData))
self.refreshTable()
system.db.refresh(self,"data")