1) Setting up a full management of payments, if you accept partial payments or payments that apply to more than one invoice, requires additional tables and relationships to set up a many to many relationship between payments and Invoices. A join table that serves to link payments to Invoices would include a field that records the portion of the total payment to apply to that invoice.
2) I'm not sure that I understand your question. Do you mean that if you have a lot of records in the portal for items purchased, you cannot print out an invoice with more than one page in order to show all the items purchased?
Dear Mr/Miss PhilModJunk,
Thanks for your reply. For question:
1. What do you mean of "Join Table"? How to do it?(Database?)
2.Yes, how to do it?
A Join table makes it possible to set up and work with a "many to many" relationship. In the following example, Invoice_Payment is the Join table.
Start with these relationships:
Payments::__pkPaymentID = Invoice_Payment::_fkPaymentID
Invoices::__pkInvoiceID = Invoice_Payment::_fkInvoiceID
You can place a portal to Invoice_Payment on the Payments layout to list and select Invoices records for each given Payments record. Fields from Invoices can be included in the Portal to show additional info about each selected Invoices record and the _fkInvoiceID field can be set up with a value list for selecting Invoices records by their ID field. A field in Invoice_Payment can record the payment portion from Payment that is applied to that specific invoice.
You receive a check for $2000 for a customer that has 3 unpaid invoices of $1500, $200 and $1000. You record one record in Payment to record the payment amount, check number and date received and Select the ID of the customer that sent you the payment so that a value list in the portal to Invoice_payment will list only unpaid invoices for that customer. You select each invoice in turn, creating a new record in Invoice_Payment for each with amounts in the portion field in Invoice_Payment of $1500, for the first, $200 for the second and the balance, $300 for the last. This pays off the first two invoices and a calculation field in Invoices can show an unpaid balance of $700 dollars on the third invoice.
And this is a process that can be automated with a script such that you record a payment, select the customer and then perform a script script that starts with the oldest unpaid invoice for that customer and pays off each in turn until the total payment amount is fully distributed.
Dear Mr/Ms. PhilModJunk,
Before follow these steps, should i create a payment module ? then start follow your suggestion?
In the invoice module create a payment table then with these relationship?
I believe that the steps that I previously posted are the first part of setting up a Payment Module as it describes the tables and relationships that would form the foundation of such a "module". But "module" is a bit vague so what I think of when I read that word may not be what you are thinking.
There is definitely no need to set up such in a separate file. I would keep them all in the same file unless you have a very specific reason to use a separate file for it.