1 Reply Latest reply on Aug 17, 2010 12:05 AM by MikhailEdoshin

    GetSummary, right function



      GetSummary, right function


      I was thinking of using Filemaker to read a daily report from posting invoices in our AP System.  The report shows any variances of posting invoices against the purchase orders on a line item basis.  Keyers have a little "wiggle" room ($5 per invoice).  

      Currently we print the report and manually review it for variances that exceed that.  Starting October 1, the volume of invoices processed with increase by 50%, and then January 1, that number will double.  The report is currently printing about 900 lines of PO/invoice detail.  This is a daily review before posting in our accounting system.  

      I have the report parsed out.  I have a key that is the voucher number+invoice number.  Some invoices have 15-20 lines of detail.  Doing this with an electronic review.  I created a subtotal part to sum the variances per invoices.  However, I don't want to print the whole report, I only want to print those invoices for which the absolute value of the variance exceeds $5.  

      I thought I could create a GetSummary field and do a search on that.  But that did not work.  Am I using the wrong function?

        • 1. Re: GetSummary, right function

          Yes, GetSummary won't work. Summaries are calculations that operate on a given found set, so they cannot be used to establish a found set in the first place. 

          You need to use aggregate functions, Sum() in your case. Assuming there's a relationship between invoices and orders, you could add a calculation to invoices like:

          5 < Abs( Sum( Invoice Line::Total ) - Sum( Order Line::Total ) )

          Or, actually, I suspect you already have the fields that calculate invoice and purchase order totals, so you can reuse them:

          5 < Abs( Invoice::Total - Order::Total )

          This will evaluate to 1 (True) when the discrepancy is more than $5 and to 0 (False) otherwise. Search by this field you'll get the invoices you need.