5 Replies Latest reply on Jul 8, 2013 2:25 PM by barenose

    Running balance

    barenose

      I'm trying to create a script that will calculate using a field that holds a balance. For example, the record holder overpaid an invoice and thus has a credit. The next time I invoice that record holder I want that credit to be deducted from the new invoice total. I think I have it figured out, but would like to get feedback on soultions that may be more elegant than mine.

       

      Thanks in advance, folks.

       

      All the best,

       

      JB


        • 1. Re: Running balance
          gkhaselev

          you didnt post your solution JB! or at least the script.

          • 2. Re: Running balance
            mikebeargie

            In standard mission-critical databases that I've dealt with. The payments area always stored separate from the invoices, allowing for multiple payments (or credits) to be applied to an invoice, or to the client independent of the invoices.

             

            So you could easily make a running total by having three calc fields through the relation from companies:

             

            Field 1 = Sum ( invoices::total )

            Field 2 = Sum ( payments::amount )

            Field 3 (running total) = Field 1 - Field 2

             

            This is the simplest form. There is also an easier way to calculate this in one field using ExecuteSQL in Filemaker 12:

             

            Running total =

             

            Let ( [

               id = company::id;

                invoices = ExecuteSQL("SELECT SUM(total) FROM invoices WHERE companyid = ?" ; "" ; "" ; id );

               payments = ExecuteSQL("SELECT SUM(amount) FROM payments WHERE companyid = ?" ; "" ; "" ; id );

            ];

                invoices - payments

            )

            1 of 1 people found this helpful
            • 3. Re: Running balance
              barenose

              Mike,

               

              Thanks, this is my plan of action. I'm going to test thisto see if it works the way I wasnt it to.

               

              My file has two tables: Register (Invoices) and LineItems. The LineItems record payments made to the Balance (Register). What I want to do is have the +/- amount be deducted from the next invoice. I'll check back in when I've tested this.

               

              All the best,

               

              JB

              • 4. Re: Running balance
                mikebeargie

                That wording could be confusing. Most developers I think would consider "LineItems" as line items of an invoice. Using nomenclature that is descriptive of the records functions usually works best for me (why I noted "payments" in my samples above).

                 

                Depending on how your invoicing generation scripts work, it should be easy to use the ExecuteSQL calculation to add a "balance forward" value to an invoice when it is created.

                • 5. Re: Running balance
                  barenose

                  Hello gkhaslev,

                   

                  Here is what I did (FileMaker 11):

                   

                  Field 1 = TotalInvoices

                  Field 2 = TotalPayments

                  Field 3 (TotalOS) = TotalInvoices - TotalPayments

                   

                  The invoicing script compares the current amoutn due and calculates the O/S balance from TotalOS.