3 Replies Latest reply on Apr 22, 2010 7:02 AM by wyrd9

    getting data from subsummaries of a Summary field



      getting data from subsummaries of a Summary field


      I am using FileMaker Pro 10 on Mac OS 10.5.8.   I am pretty new to FileMaker (love it!) and have been learning from books and online resources.  I’ve gotten pretty far with the database (resource scheduling and invoicing) I am trying to develop for a small microscopy laboratory.  So far, it is a single-user (me) database and is not shared in any way.   I have yet to try scripting.


      _My Situation_ concerns 2 related tables:  Appointments and Invoices.   I am not using a Line Items table.   For one Invoice, I am filtering (via global fields) Appointments by a date range (Appointment Date) and Customer ID and displaying them on the Invoice layout in a portal.  I am doing this so that I can assign an Invoice ID to the appropriate Appointments (via Replace Field Contents). (Is there a better way?)


      To generate printable invoices, I am running an Appointments report with a summary field that sorts by Customer ID (and some other things).  I am using the report because the portal cannot break across pages, and I have up to about 65 Appointments in some Invoices and also so that I can summarize by subcategories.  This worked well for the first billing period, BUT..


      _The problem_  For internal reasons, we do not charge for a <$100 balance in a quarter, but that balance needs to be forwarded to the next quarter and added to the Total for that quarter.  Also, in the future I may track payments.  I am currently using a Summary field for the Balance for each Customer is actually a subtotal of the Summary field, which cannot be indexed.  Am I going about this all wrong?


      Advance thanks!

        • 1. Re: getting data from subsummaries of a Summary field

          Ok...after several weeks (can only work on it in free time) of struggling with extracting the summary data into an indexed field I can use for other calculations, I tried an auto-enter Calculation (rather than a Lookup or GetSummary or Calculation) field in the Invoices table of the Summary field in the Appointments table.  It seems to work.


          However, I am still stumped as to how to get and preserve the past and current quarter totals so I can forward them.  Perhaps I must use a Payments table?


          • 2. Re: getting data from subsummaries of a Summary field

            You may want to look into the getsummary calculation.  This will get the summary value based upon the value of a break field.  You can filter values through a relationship; the calculation does not necessarily have to be in the same table unlike the summary field.


            I hope that helps.

            • 3. Re: getting data from subsummaries of a Summary field

              Thanks for your response.  I did try getsummary in a Calculation field, but was unable to index it.  I think the solution I found yesterday (a Number field using auto-enter Calculation [=Appointments::Summary]) is working surprisingly well.  However, I still don't know how to save a balance from one quarter to be added into the total of the next quarter.  Do I need a recursive relationship?  If so, what is this?


              Thanks again and in advance!