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?