Updating Tables

So an annoying glitch got uncovered. If someone is editing a column on a table and someone else adds a new row to the table, the table being edited gets updated and the edits will get put onto the row above. Is there a way to get around this happened?

If your polling mode is Relative, then it will continue to refresh the data. Try changing it to OFF.

CORRECTION: It sounds like you are not using Unique IDs in whatever place you are storing and editing the shared data. Create a PRIMARY KEY in your SQL Table, or find some other unique way to represent each row in your table.

Yes that could work. But the table needs to be updating. I wish it were that easy.

Sorry, see correction above.

Here is the table, and the script that is used to save the notes.

Allows for updating on the table.
row = event.row
column = event.column
columnName = event.source.data.getColumnName(column)
newValue = event.newValue

if columnName == ‘Note’:
if newValue == “”: # User changed it to an empty note. Delete the Comment
# Remove from Runtime db
query = “DELETE FROM Comment WHERE ID = ?”
values = [event.source.runtimeID]
# Remove from Analysis db
query = “DELETE FROM PROD_Comment_Summary WHERE ID = ?”
values = [event.source.analysisID]
# Update table
else: # User changed it to another string. Update the comment.
# update Runtime db
query = “UPDATE Comment SET Note = ? WHERE ID = ?”
values = [newValue, event.source.runtimeID]
# update Analysis db
query = “UPDATE PROD_Comment_Summary SET Note = ? WHERE ID = ?”
values = [newValue, event.source.analysisID]
elif columnName == “IsSticky”:
# update Runtime db. Note: there is no editing needed in the Analysis db
query = “UPDATE Comment SET IsSticky = ? WHERE ID = ?”
values = [newValue, event.source.runtimeID]

Try changing your DELETE statements to UPDATE statements instead. Something like:

      query = "UPDATE Comment SET Note = ? WHERE ID = ?"
      values = [None, event.source.runtimeID]

the only reason the delete statement is in there is when they make it a blank string. if it is anything other then that it pushes it to the update statement.

I am assuming that the trouble is since the data is being updated, the “selectedrow” property is being changed when someone adds something to the table. perhaps, instead of using an editable table, popping up a window that allows you to make the edits, then send them to the database would solve your issue.