Invoices - carrying over unpaid amounts

[Fran_g]

Not sure if this is the correct forum for this ...

 

I am currently developing a file to record class enrolments and producing the resulting student invoices for a small unit providing various classes. Students can undertake one or more classes and enrol for a term of these classes.

Students can make a one-time payment to settle their invoice or can make part payments during the term. Many students re-enrol for another term of their classes following the end of the current one.

There is a table for each of the following: Students, Classes, Invoices, Payments, ClassesLinkedToInvoices with the structure below

Students ---< Invoices ---< Link >--- Classes
                                  |
                                  |____ Payments

The file works as intended in creating the student-class links, the necessary invoices, and in duplicating these details in new invoice records for the next term, showing the same classes the students have re-enrolled on.

However, some students may not have paid the full amount to settle their current invoices so this money has to be carried over to the new term and added to their new invoices.

This is where I am having difficulty, in thinking through how to:

1. carry over these unpaid amounts from some of the student invoices of one term to show on their invoices for the next term;

2. add these amounts  to the new invoices; and

3. delete [or mark in some way as being carried over] from the old/previous invoices.

Any ideas or suggestions on ways to accomplish this will be much appreciated.

[comment]

Perhaps this discussion may be of some help:

http://fmforums.com/forum/topic/76398-allocating-payments-to-invoices-questions/

[LaRetta]

1. carry over these unpaid amounts from some of the student invoices of one term to show on their invoices for the next term;

2. add these amounts  to the new invoices; and

3. delete [or mark in some way as being carried over] from the old/previous invoices.

 

I am unsure on your diagram but it looks like you apply the payment to the Invoice (from payments table?).  Instead, if payments are LINES (in your example 'links' usually called the LineItems ) then it is simple matter when the time comes to add another lineitem which is the balance due on that invoice as another PAYMENT, and the line Type or GL code would be TRANSFER and referencing the next invoice.  Then a new lineitem is added to the new invoice as a TRANSFER, referencing the original invoice.

 

This then zeros out the first invoice, removes it from aging on the old invoice and begins aging the amount on the new invoice, and provides an appropriate audit history on both invoices so everyone knows exactly what transpired. If partial payments are LINES on the invoice, this is very easy to handle and your monthly reporting is accurate.  

 

It IS okay to use a separate table for payments but that adds unnecessary structure and reporting complexity.