AnsweredAssumed Answered

getting data from subsummaries of a Summary field

Question asked by wyrd9 on Apr 21, 2010
Latest reply on Apr 22, 2010 by wyrd9

Title

getting data from subsummaries of a Summary field

Post

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!

Outcomes