>While converting a system for a client, I'm running into problems with the way they approach things. One is simply the age-old "we've always done it that way" problem. Something more specific is their desire to have an overpayment on an invoice make the invoice balance go negative, effectively making it an open credit that can later be applied against another invoice. So you have a situation where it's an invoice one day, a credit the next. One day you're getting a payment against it and the next you're using it as a payment to pay something else. To me, this is the wrong way to go about it. I have the system never letting a balance fall below zero and generating a credit invoice for the overpayment amount. Then the credit invoice can be used later to apply against another invoice. This causes some issues for them, though I'm going to address them. Anyone have any thoughts on these two appoaches?
>
>Russell Campbell

Hi Russel,

It seem to me that you are trying to mix two different things into one application.
No matter how modules/functionalities are allocated or divided, we are talking about essentially two different things.

Invoicing and/or Purchasing
Accounting

In Invoicing/Purchasing you issue (or recieve) invoices
You can (but not necessarily) record - payments received (or paid out)
As Dragan said they all stay as they are - recorded transactions.

Those recorded business transactions (documents) are later interfaced to Accounting (some call it GL) as what
accounants call Double Entry Journal Entries.
Journal entry is always balanced to 0 . If you debit some account then you have to credit another one so everything
always squares to 0.

In accounting now;

Number one; You cannot have 'balance on invoice' . Invoice is simply type of as they call it ''movement'
to an Account. An Account have balance. So invoice is (in simple form) registered as

__________________________
Client (B/S)    SalesControl (P/L) 

Debit           Credit
__________________________
1000        |
            |1000
            |

Left-Right = Nill

When client actually pays an invoice, then you issue RECEIPT and record opposite journal transaction which this time CREDIT client for what he paid and DEBIT something else (bank account)

And this 'story' repeats meny meny times for meny clients .
Of course you don't have to know or care about all this above , it is up to accountants to set up their accounts, ledger etc
you simply interface as they request.

Now,
What is on left and right side of this draw is actual movement of accounts and is usually kept in single table which looks something like .

.
ACCOUNTCODE
TRANDATE
DEBIT
CREDIT
.
.

Now balance of an Account starts with 'begin of time' or some 'point in time' - Opening balance
So you simply calculate all diferent debits and credits to this particular account.

.
.
nBalance = nOpenBalanceAsAtSomePoint
scan for Movement.AccountCode = Clients.AccountCode  
    nBalance = nBalance + Movement.debit - Movement.Credit
endscan

return nBalance

This is of course just sketch of code
Movement is usually sorted by transaction date so you can extract precise balances as with any point in time. And can be extracted in different ways.


Now that result of this calculation is Account Balance and of course can go in red, where client overpaid or delivered you some goods in return. When you issue next Invoice You will not issue 'difference' but actual value of goods You sold which will again end up
in this movement file and form final balance.

If you use aproach with session objects, you can have actual 'balance server' where
you pass to this object account code and date range, while object will return back the balance. then you can show this on Invoice form as readonly field but no point storing it anywhere because it constantly changes.


Of course you can stage some simple movement table within Invoicing application but essentially
as application grows you will be doing more and more of what I described above and end up
with mix of functionalities which essentially should belong into two different but interconnected applications;
Accounting and Invoicing.

Or you set is from very start as two different apps and keep them both clean.


HTH

Sergio