Updating Balance Field

Hi,

Just want some suggestions on the following issues. I currently have a
Balance Field in
my Invoice.DBF that gets updated with every payment. In a multi-user
environment,
I encountered a problem when the balance field was updated by two different
user, with
both user entering different payment amount. The balance was incorrect
because the
calculation was based on the user that last saved the data.

eg.

Invoice amount : $1,000
Invoice Balance : $1,000

User 1 - Entered $400 as Payment
User 2 - Entered $500 as Payment

Both user was entering their data at the same time, User 2 being the last
to click the save button.

Invoice Balance : $500 (which should be $100 only)


I really need to maintain the Balance Field so I could trace which invoice
is still unpaid.
There are times that the clients pays only the latest invoice so I could not
implement
a running balance on the program.

Thanks for any ideas or comments you can give.

Bernhard
Sat Oct 28 09:25:54 CDT 2006

Hi Abet,

> Just want some suggestions on the following issues. I currently have a
> Balance Field in
> my Invoice.DBF that gets updated with every payment. In a multi-user
> environment,
> I encountered a problem when the balance field was updated by two different
> user, with
> both user entering different payment amount. The balance was incorrect
> because the
> calculation was based on the user that last saved the data.
This effect is called concurrency, you could find lots of texts in the internet.
If you use pessimistic locking, then you never have this problem.
If you use optimistic locking, then you should check at every update, whether
the values in the table are still the same as they were when you read the data
the last time before you make the update. You have to write some lines of code
to deal with this situation.
In a view definition, there are several settings for this check. Have a look
into the help file at cursorsetprop, WhereType or cursoradapter.wheretype or
view designer, Update Criteria Tab

Another way could be: instead of calculating the new amount in your application
calculate it right in the update command:

instead of (this is the way as normal views work):
SELECT value FROM table
newValue = value + amount
UPDATE table SET value = newValue

call (this must be hand coded):
UPDATE table SET value = value + amount


Regards
Bernhard Sander

AA
Sat Oct 28 12:42:36 CDT 2006

This is calculated data so it might not be advisable to maintain a column.
You can query for the current balance amount
SELECT SUM(payments) as balance FROM account WHERE invoiceno = n INTO CURSOR
x
will read directly from the disk. In a multuser situation where
modifications can be made at any time, the result may of course be invalid a
split second later.
-Anders

"Abet" <herbert.bautista@yahoo.yahoo> skrev i meddelandet
news:u0yB7Fj%23GHA.4472@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> Just want some suggestions on the following issues. I currently have a
> Balance Field in
> my Invoice.DBF that gets updated with every payment. In a multi-user
> environment,
> I encountered a problem when the balance field was updated by two
> different
> user, with
> both user entering different payment amount. The balance was incorrect
> because the
> calculation was based on the user that last saved the data.
>
> eg.
>
> Invoice amount : $1,000
> Invoice Balance : $1,000
>
> User 1 - Entered $400 as Payment
> User 2 - Entered $500 as Payment
>
> Both user was entering their data at the same time, User 2 being the last
> to click the save button.
>
> Invoice Balance : $500 (which should be $100 only)
>
>
> I really need to maintain the Balance Field so I could trace which invoice
> is still unpaid.
> There are times that the clients pays only the latest invoice so I could
> not
> implement
> a running balance on the program.
>
> Thanks for any ideas or comments you can give.
>
>

Abet
Sun Oct 29 19:07:40 CST 2006

Thanks for the reply