3 Replies Latest reply on Mar 23, 2011 8:54 AM by philmodjunk

    Summary Field



      Summary Field


      HI all, I wish to calculate the outstanding invoice balance overdue over a period of time for each customer. It seems that it can be achieved via summary report over all invoices related to that customer. However, I wish to do a step further by comparing it with the credit limit allowed for each customer and prompt a alert next time when another invoice is to be input again. 

      Somehow, I can't figure out how to do calculation against figures under summary field. Any other functions ready in FM? Appreciate any thought/idea.


        • 1. Re: Summary Field

          How do you record payments on your invoices? In a field in the invoices table or a related payments table?

          How do you identify each customer so that you can find all outstanding invoices for a given customer? By Name or by CustomerID? (Names aren't unique so you don't want to deny service to one customer simply because a customer of the same name hasn't paid their bill.)

          • 2. Re: Summary Field

            Yes, in a field in a related payments table and each customer is identified by a customer ID

            • 3. Re: Summary Field

              I'm guessing that you have this already defined in Invoices:

              cTotalpayments: Sum ( PaymentTable::PaymentAmount ) //computes total payments for current invoice

              cAmountDue: InvoiceTotal - cTotalpayments //computes amount due on current invoice

              IF so, this calculation defined in your customer table would compute the total owed for any given customer:

              Sum ( Invoices::cAmountDue )

              Comparing that to a credit limit field in the same table would tell you who has gone over the limit and who has not.