> 1. start a transaction

Not needed

> 2. set lock field of record to user number

Set and commit - at same time take copy of record to work on.

> 3. user edits the record

All the work involved in working out what to edit is done
off-line. No open transactions <g>
Other users get a message that the record is being updated
by X if they try and edit it.
As the order is processed, additional transactions take care
of stock allocation - and these have to be unravelled if an
order is cancelled, but that depends on wheather we are
creating a new order, or picking a previously generated order.

> 4. user has finished editing, so clear lock field

The updated records are loaded and the lock cleared in the
same new transaction.

> 5. if user saves the doc then commit the transaction,
> otherwise rollback transaction

This step is not needed. Part of 2 and 4.

Rollback should not be needed as the updates are checked
before commiting them - just need to clear the flag if the
user decides not to update.

It does need some housekeeping though, as you can lock
records and leave them locked. I have a sort of 'clear all'
which resets any outstanding 'X' flags when X logs off and
also when they log on again, in case of a network crash, but
I have not needed the 'supervisor override' to unlock a
problem record.

> Have I understood you correctly?

The main point here is that stage 3 could take 30 minutes,
and I don't want to lock the record in a transaction for
that time, hence splitting the transactions.

Things do get a little more complex as well, as I have to
'lock' the stock that is being used to complete the order.
The system is processing the creation of orders, and
allocating stock, and the pulling of stock to complete an
order, once allocated. The quantity of stock available is
reduced, the particular pallet location is adjusted and the
required stock locked, and once all product for an order is
'picked', then the picking list can be committed and
printed. Any problems, and the whole order may need to be
cancelled, or just part picked. Since Firebird does not
support nested transactions, this was part of my system for
getting round that problem, but works well when multiple
staff are feeding multiple stock pickers, as it also shows
who is processing or actually physically picking and order.

( It gets even more complex still as multiple orders can be
batched and sorted to provide an ordered pick list, with a
batch of picking labels printed in batch order and provided
for an area of the warehouse to speed up picking. But that
is another story and another level of nested transactions
<g> )

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services