Update payments to invoices (1 Viewer)

[jdcallejas]

Hello,
I am creating a small database to keep track of invoices and payments for my small Internet Service Providing business. I now got into something that I need some help from you.
I have created invoices, products, clients, and one I call payments. I want to be able to create a code that will look up the clients invoices and apply payments from the payment database by filtering that clients id.

Lets say that the client has 5 invoices with a total of $100 each, but he only pays $375, when I apply the payment I want it to go through the recordset of invoices look at the total due on the invoice and apply whats is due and mark as paid. Then deduct from the original payment and continue with the next invoice, once it gets to the point that there is not enough money left on the payment to cover the invoice, just apply what's left from the payment and leave the new amount due for next time the client makes a payment.

I think I need to do a for next loop, but I am not up to date on how to do this.

I have uploaded the database, I have created a yes/no field on tblFacturas "Invoices" and on the tblPagos "Payments" so it will only filter unpaid invoices and only use new payments once the payment amount has been totally used.

If there is another way I greatly appreciate the fix.

Thanks,

Daniel

 

[Cronk]

This is not a trivial exercise to achieve.

You need two tables to do what you want. A table of payments (tblPayments) and a table of allocations (eg tblPaymentAllocations)
tblPayments
tblPaymentsID
CustomerID
PaidDate
ReceiptNo
Amount
MethodOfPaymentID (lookup: Card. EFTPOS, Cash etc)

tblPaymentAllocations
PaymentAllocationID
PaymentID
InvoiceID
Amount

The algorithm for the allocation of a total payment amount would be to get a recordset of invoices with an outstanding balance, arranged by some order (probably age).

Then a loop allocating amounts to each of the invoices in order and subtracting the amount allocated from the amount paid until there is something less than the amount due under an invoice.

What will you do if the amount paid is more than than the total outstanding? So you would probably have to have a tblCredits which should have amounts someway be allocated to new invoices.

What you want is achievable - I have something similar working in one client app but as I wrote, it is not a trivial exercise.

 

[gemma-the-husky]

cronk suggested tables are correct, I think.

you can't automate the allocation (unless the payment clears all invoices, say)

you need a form to display the outstanding invoices to allow the user to select how the payment should be allocated.

proper accounting is not too hard, but needs decent skills. I reckon a decent accounts receivable system, including account management and some basic aged debt management is probably a solid week's work for a good developer.