Hi All
Anothe question. I have a table that is linked to a data via a query. When a user makes a change to the data (in this case set a value as active/inactive), I would like to push the change to the database. I’m wondering which method is better and how to do either.
Method 1: On change run an update statement (On each change write)
Method 2: Bank changes and have them click a “save” button
I have found how to do this via a button, but think that in in-table editor will be a lot quicker/easier.
Thanks S
Is this vison or perspective?
Both the methods are valid. It total depends on what you want to do, sometimes you only have one record to update, in that case you just reference the rowid that you want to update on your database table and run a update named query just for that row.
Othertimes, you might want to make multiple updates in different rows, then you need to go for the Method 2 and save all updated records to the database on click of a "save" button.
I strongly prefer method 2. And I generally arrange to highlight table cells that have changes.
Thanks, I'm leaning towards method 2 as it minimises writes.
Is there any guidance you can provide on how to link the table correctly and then how the save function would work to grab the data? When I bind it, it doesn't seems to want to allow me to change values.
I recommend a named query binding (dataset mode, no polling) to a custom property, not directly to the table's props.data. Use a unidirectional binding to a second custom property that will hold modified data as you go. (The unidirectional binding will clear changes when you make the named query refresh.)
Use a property binding on props.data with a script transform, or perhaps an expression binding with my toolkit's iterators, to capture changes to the 2nd custom prop, and add highlighting for cells that do not match between custom props.
Your table handling of changes would write to the 2nd custom prop. The save button would iterate through the 2nd custom prop, comparing to the first, to generate SQL updates. After all SQL updates, it would refresh the custom prop with the named query binding.
Thanks for this. Can you confirm if this could be done any better or if it’s a suitable path?
Table “onEditCellCommit”
def runAction(self, event):
# Get current tableData
_tableData = self.custom.tableData
#Grab the current values from the database
_DowntimeCategoryId = _tableData.getValueAt(event.row, 'DowntimeCategoryId')
_IsActive = _tableData.getValueAt(event.row, 'IsActive')
# Modify the specific row/column
changes = {"DowntimeCategoryId" : _DowntimeCategoryId, "IsActive": event.value}
#store the data in a new variable
_newTableData = system.dataset.updateRow(_tableData, event.row, changes)
#set that variable to the custom props
self.custom.tableData = _newTableData
#enable the save button to indicate changes
self.getSibling("btnSave").props.enabled = True
Then on my save button, I have the following “onClick"
def runAction(self, event):
#create a logger to log data
logger = system.util.getLogger("DowntimeDB")
#grab the data from the custom objects
tableData = self.getSibling("tblCategories").custom.tableData
loadedData = self.getSibling("tblCategories").custom.loadedData
# Build a dictionary lookup of original values keyed by ID
originalById = {}
for row in loadedData:
key = row['DowntimeCategoryId']
value = row['isActive']
originalById[key] = value
currentById = {}
for row in tableData:
key = row['DowntimeCategoryId']
value = row['isActive']
currentById[key] = value
# Capture changes/error counting
changes = 0
errors = 0
# Compare the two dicts — only IDs that exist in both and have changed
for rowId, newIsActive in currentById.items():
#check if the new value is different to the original value
if rowId in originalById and originalById[rowId] != newIsActive:
#write the changes to the database
try:
rows_affected = system.db.runNamedQuery(
"Downtime/Update/UpdateCategoryStatus",
{
"DowntimeCategoryId": rowId,
"IsActive": newIsActive
}
)
#log the output for testing
if rows_affected > 0:
logger.info("Updated DowntimeCategoryId {}: isActive = {}".format(rowId, newIsActive))
changes += 1
else:
logger.warn("No record updated for DowntimeCategoryId {}".format(rowId))
errors += 1
except Exception as e:
logger.error("Failed to update DowntimeCategoryId {}: {}".format(rowId, str(e)))
errors += 1
# Refresh bindings if anything changed
if changes > 0:
tbl = self.getSibling("tblCategories")
tbl.refreshBinding("custom.loadedData")
#disable the save button if no errors
if errors == 0:
self.props.enabled = False
logger.info("Save complete. {} updated, {} errors.".format(changes, errors))
Looks pretty good. You may want to consider adjustments for new rows and/or deleted rows, which will require separate SQL statements.
Thanks, this screen won’t have any add/delete functionality but definately a good call for the other screens I’m working on. Appreciate the guidance!