Disappearing or changed user input - Concurrency checking and logging

[CaseyR]

Hi, Bruce

On today's user group,  a group member (sorry, I didn't note the name) mentioned user input disappearing after saving the record.  It was a reasonable assumption that this was caused by another user editing the same record possibly at the same time.  Just in case the group member looks in this forum, here is a way to minimize such events.

Concurrency checking can be done fairly easily using memory forms or In-Memory tables for user input:
Load existing record into memory form;
Save record ID and current date/time as session values;
Edit memory form;
On validation retrieve record,
If record change date/time is after session value date/time reject the edits and alert user,  if not transfer the edits to the record with an updated change date/time and save

As to logging changes,  logging every change to every field would indeed be a monster, but a simple log of action (created, changed, deleted) along with user ID and date/time goes a long way to identifying editing problems. 

[Mike McLoughlin]

Thanks for that Casey -  its a neat idea

I need this because the central form of the app is contributed to by different levels of staff in the school - teachers, observers, line managers, subcontractors etc.  So there is a fair amount of "concurrency risk". And of course it would be one of the management team who "lost" her data.

They are coming up to their annual renewal so I need to calm the horses with something soon.  I was thinking that a quick fix solution might be to display a message to other users when someone has already opened the record in edit mode. 

Or force subsequent would-be editors into view only mode until the initial editor has finished. That would avoid rejecting any edits.

[CaseyR]

Hi, Mike

The problem with both locks and alerts is the possibility of a user opening a record for editing and then going for lunch without saving or cancelling. Or worse, going home.  You will need to build in a function for determining and closing abandoned edits.

Memory forms will work for displaying existing records and getting user input, but I have found the In-Memory driver tables somewhat easier to work with.  The In-Memory tables can use a SessioID-RecordID pair for a single unique instance of the form record.  You do need to cleanup after a session closes and once a day or so to clear out abandoned orphaned In-Memory records.

[bshields]

For what its worth:

1. Can the "central" form be redesigned into perhaps smaller logical pieces to minimise concurrency issues?
2. If the backend database is SQL, you can hand code the updates so that only the fields changed by the user are in the update query (removes issues of reading and writing whole record buffers - but only suitable in some circumstances) E.g. If the manager has to say approve a transaction, you may only need to add their ID and maybe a date and time to the record. In this case hand code the approval screen (don't use a big edit screen that allows you to edit everything), build a screen that performs just this process, and use PROP:SQL to issue an SQL UPDATE on only the required fields.

Regards
Bill

[Mike McLoughlin]

Thanks for the ideas Bill but the Observation Form needs to be seen by everyone in its entirety, and then when they agree with it they digitally sign it.  But I'll look into what can be done in compartmentalising edit permission based on access level.

Mike