5 Replies Latest reply on Mar 26, 2014 12:11 PM by philmodjunk

    Invoice settlement



      Invoice settlement


           Dear Sir/Madam,

           We would like to ask the following questions :-

           1) Regarding invoice module, how do we create the matter of settlements (e.g deposit and final payment of individual invoice) ?

           2) Regarding the layout (e.g quotation or invoice), data can not be automatically go to the next page if the total items are listed more than one page (pls see the below) and how "Total Page : 2" is shown ?

           Many thanks for your help.

           Edward Wong




        • 1. Re: Invoice settlement

               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?

          • 2. Re: Invoice settlement

                 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?

                 Many Thanks

                 Edward Wong




            • 3. Re: Invoice settlement

                   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.

              • 4. Re: Invoice settlement

                     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?


                     Many Thanks

                     Edward Wong

                • 5. Re: Invoice settlement

                       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.