0

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

null
    • Fred
    • 1 mth ago
    • Reported - view
      • Nick
      • 1 mth ago
      • Reported - view

       My current database is a beast! Give me a day or two to prepare a test DB.

      Thank you

    • Nick
    • 1 mth ago
    • Reported - view

    @Fred Here Is the test DB

    • John_Halls
    • 1 mth ago
    • Reported - view

    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.

      • Fred
      • 1 mth ago
      • Reported - view

      Just some notes:

      1) think about doing this from your dashboard instead of in Receipts.

      2) Receipts don't really need a reference field to Customers, just Invoices. You have the link to Customer through Invoices. It seems like Customers was added because of wanting to solve this issue. So if you do #1 then you won't have the need for Customers in Receipts.

      I've uploaded a sample DB.

      It is very basic and doesn't account for all possibilities, like outstanding invoices, but shows one way of doing it.

    • John_Halls
    • 1 mth ago
    • Reported - view

    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)
      • Nick
      • 1 mth ago
      • Reported - view

       Hi John,

      thank you very much. I will try your coded I let you know.

    • Nick
    • 1 mth ago
    • Reported - view

     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. 

    • Fred
    • 1 mth ago
    • Reported - view
     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 customer

    This got me thinking and came up with this version. As you can see in the dashboard there are:

    1. a new dynamic multi choice field showing outstanding invoices
    2. a new number field called Amount Received
    3. two buttons, one to pay selected invoices and one to pay as many invoices entered in Amount Received
    4. 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

    • Nick
    • 1 mth ago
    • Reported - view

    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
  • 1 mth agoLast active
  • 10Replies
  • 86Views
  • 3 Following