7 Replies Latest reply on Nov 2, 2011 3:13 PM by philmodjunk

    Adding paid or unpaid status to an invoice ....

    ChrisCollins

      Title

      Adding paid or unpaid status to an invoice ....

      Post

      HI all,

      New to the forum, so thnak you in advance for any assistnace.

      We have a simple invoice setup that has inventory and clients/people with accounts which drive to the invoice options. This is where we are stuck, we need to add a paid or unpaid field and have it calculate automatically once an amount is inserted and then have that push to a AR per person or client so when we print out an AR we know where we stand ?

      Any suggectins (in layman's terms if possible, please)

       

      Thanks again,

      Chris

      Screen_Shot_2011-11-01_at_11.48.06_AM.png

        • 1. Re: Adding paid or unpaid status to an invoice ....
          philmodjunk

          And "AR" is layman's terms? Wink

          I assume that stands for "accounts receivable". We may need to know how you've current set up your AR, but let's start simple to get the ball rolling:

          Where and how will you record a payment against this invoice? This can be simple, one payment to one invoice or you might get a payment that partially pays an invoice or which pays off several invoices with a single payment. Do your current business practices require that flexibility?

          Implementing all of the above requires adding a Payment table (ledger) and uses yet another table, a "join" table to link a payment to however many unpaid invoices for that customer that are needed to document which invoices are paid.

          • 2. Re: Adding paid or unpaid status to an invoice ....
            ChrisCollins

            LOL!

            Where and how will you record a payment against this invoice? - This is exactley what i am trying to setup so i can imput the paid amount wether it is the total or partial, thought ?

            • 3. Re: Adding paid or unpaid status to an invoice ....
              philmodjunk

              I believe my thoughts are in the last two paragraphs of my last post. If it is possible that you will receive partial payments and multiple invoice payments--as is almost always the case unless customers are required to always pay in full at time goods or services are received, you'll need two more tables and the following relationship:

              Invoices----<Invoice_Payment>-----Payments

              Let's say a customer owes you $500 on one unpaid invoice with INvoice ID 1001.

              You receive a payment of $200 dollars on one occaison and $300 to pay it in full on a second date.

              You'd create two records in the Payments table (this is your payments ledger) entering the date, amount, payment method and customer ID with Payment ID's of 2002 and 2234. You'd then create two records in Invoice_Payment entering data into three fields, InvoiceID, PaymentID and Amount. One record would have PaymentID = 2002, InvoiceID = 1001 and Amount = 200. The second would have 2234, 1001 and 300.

              In your invoices table, a calculation field: Sum ( Invoice_Payment::Amount ) will compute the total payments applied to the current invoice. When this equals your Invoice total, the invoice is paid.

              Let's say another customer owes $400 on invoices 1101, 1121 with amounts of $300 and $100 respectively. He mails you a check for $400 dollars.

              You'd create a single payment record to log the receipt of $400 dollars. Let's say it's PaymentID is 2003. You'd then create another two records in Invoice_payment, with INvoiceID = 1101, PaymentID = 2003, and Amount = 300. The second would be 1121, 2003, 100.

              Do you see how the three tables work together?

              From here it's a matter of setting up portals, scripts and drop down lists to make it easy to log a payment and then apply the payment to the correct invoice(s).

              Here's a demo file on many to many relationships (what we have here between payments and invoices) that may help you get started:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              • 4. Re: Adding paid or unpaid status to an invoice ....
                ChrisCollins

                What you have described above is exactly what we are looking for.

                For the download above what am i supposed to be downloading, there seems to be quite a few options, exscuse my ignorance ...

                • 5. Re: Adding paid or unpaid status to an invoice ....
                  philmodjunk

                  Yes, they bombard you with tons of ads and some look like download links. One of several reason why I have stopped recommending this site for sharing files.

                  There should be a blue "download now" button. Click it. This starts a 20 second timer while they throw more ads at you. Then the download link appears and you can click it to download the file.

                  • 6. Re: Adding paid or unpaid status to an invoice ....
                    ChrisCollins

                    That is pretty cool, but how long do think it will take to put something like this together (more elaborate obviously)

                    Would you be willing to be paid to assist us or even build it ? or could you just help build us a template drawn up and we could handle the rest, at least i think we can, your thoughts ?

                    • 7. Re: Adding paid or unpaid status to an invoice ....
                      philmodjunk

                      <-----click this avatar icon so you can send me a private message and we'll take that discussion off line.