Invoices/receipts Update for Students

[adyeboah]

Hi Bytes Community,

1)I have a table(student Registration) related to table(Invoice -many side).
2) The Student Registration table is related to table(Student Payments - many side).
3) Receipt details are captured in a table (Student Payments) I.e junction table.
4) Payments table is related to Student Payments table(many side)
Question:
After issuing an invoice to a student with invoiceID say 1, you capture payments over a 1 month period;then issue the next invoice with say InvoiceID 7.

How do you carry forward the closing balance on invoice 1 to invoice 7 to enable continuous invoicing in real time?

Note that students fees are updated every month with Date Received, Amount Received, plus description.

For example, a Student is issued with an invoice amount of 100 euros on 28 July 2017 and pays 80 euros;How do you carry the balance of 20 euros for invoice 1 to invoice 7 as an Opening Balance in a report?


I hope this information will help.

Thanks

[PhilOfWalton]

The method I use is to hold a Balance field in the student table. This is calculated by adding the invoice total and subtracting the payment to the existing Balance.
If the balance <>0, then that is shown as Balance Brought Forward on each invoice

Phil

[adyeboah]

Thanks Phil but it didn't quiet solve my problem.

Let me give a more detailed example;

If the invoice Total is 100 euros invoiced on 28th July,the student pays 80 euros, the Balance B/F is 20 euros.

If Current invoice Charge is 100 euros, then Total Due is 20 plus 100 euros which is 120.

Question:

How do you display the total due in the next invoice due on 28th February 2017?

[PhilOfWalton]

So let's set out a hypothetical situation

Date    Invoice  Payment  Balance   B/F  Invoice  Total
28 May    100               100      0     100     100
1 June              80       20    (20)
28 June   100               120     20     100     120
1 July              80       40    (40)
28 July   100                       40     100     140
1 Aug               120      20    (20)
 

The balance is obviously the running total of the invoices minus the payments, and can be calculated rather than storing it, but I tend to store it so that whenever I look at a Student record, I can immediately see the balance without having to do the calculation

Phil

[adyeboah]

Thanks Phil for the logical explanation.