Payment of several unpaid invoices
Hello everyone,
I have something difficult (for me at least), but I'm not afraid of you, I trust you :-)
I have built a database of my finances. All good so far, but I'm thinking of taking it a bit further...
The scenario is unpaid invoices (and not only) and customers. I hope to give you an idea of what I mean:
Customer A, buys on credit, and owes e.g. 4 invoices of 10 euros each. The dates of the invoices are of course different.
The customer comes in today and gives me 35 euros. Instead of going through the amounts manually and paying the three invoices and crediting the fourth invoice with 5 euros, I want to try to do it with a button.
That is to pay the 3 invoices starting from the oldest one and credit the remaining amount to the oldest outstanding invoice (common practice in commercial applications).
The tables are: Customers, Invoices, Receipts with the corresponding relationships between them.
Watch out, it's like a test for the forum gurus!
Ideas?
10 replies
-
said:
The tables are: Customers, Invoices, Receipts with the corresponding relationships between them.Can you upload a test DB?
-
@Fred Here Is the test DB
-
Hi Nick
Your receipt is linked to the invoice. You need a many to many Allocations table that sits between your Invoices and Receipts tables. Change the Balance to be the sum of the Allocations Amount.
sum(Allocation.Amount)
Your button then loops through the invoices that still have a balance and creates an Allocation record and sets the amount to the minimum of the balance and the amount remaining to allocate.
This code will work from your receipt record. It's untested but gives you a flavour of the solution
let a := this; let b := Amount; for c in (Customer.Invoices[Balance>0] order 'Invoice No') do if b > 0 then let d := min(b,c.Balance); (create Allocation).( Invoice := c; Receipt := a; Amount := d ); b := b - d end; end
If you find you have overpayments from time to time you may wish to move this button out to the customer so that it also loops through each receipt that still has a balance.
-
Hi Nick
Sorry, rushed that a bit. It should be Change the Balance to be the TOTAL less the sum of the Allocations Amount
TOTAL - sum(Allocation.Amount)
-
and
Well, first of all thank you both.
I tried John's way and it works fine, it just doesn't work with overpayments (as John also mentioned). With some tweaks I'll make it work.
I haven't had a chance to try Fred's way yet.
Fred, that's why I did the dashboard, I thought it would be easier.Also, Fred, I linked the receipt to the customer because at first I ignored the invoice and entered the receipt as a credit to the customer (balance = sum(debit) - sum(credit)).
Thank you both for showing me the way to move.
-
said:
Also, Fred, I linked the receipt to the customer because at first I ignored the invoice and entered the receipt as a credit to the customerThis got me thinking and came up with this version. As you can see in the dashboard there are:
- a new dynamic multi choice field showing outstanding invoices
- a new number field called Amount Received
- two buttons, one to pay selected invoices and one to pay as many invoices entered in Amount Received
- a view element of related Receipt records
In addition, I created a table called PaidInvoices that is a child of Receipt and linked to Invoice as well. This way 1 receipt can pay many invoices, like recommended.
The buttons are very basic so you will have to add to it if it works like you want. You will also have to figure out how to deal with partial payments of invoices. I create the record, but how you track it is up to you.
You can see in the view element on the dashboard:
Id 14, is a payment of 30, so only 3 invoices were paid.
Id 17, 35 was paid, so 4 invoices were paid, but only a partial payment for the last one
Id 18, 20 was paid to the selected Invoices
-
Thank you very much Fred for your effort to give a working solution.
I now have 2 arrows in my quiver (along with John's solution).
Again, thank you both!
Content aside
- Status Answered
- 4 mths agoLast active
- 10Replies
- 90Views
-
3
Following