Reporting with two tables
Hello. I'm developing my first database using FileMaker Pro 10 on Mac OS 10.6.4. I am the only user, and it is currently in use (thousands of records). The relevant tables are related as follows:
Accounts --< Users--< Appointments >--< Invoices and Appointments >--Resources
The Appointments and Invoices tables are related in that many to many way in this table occurrence group (along with an unseen relationship with another Invoices TO) in order to forward a balance from earlier invoices on the same account. However, there are many appointments to one invoice.
I use one layout (Assign Invoices; table=Invoices) to assign invoice #s to each account for a given period (quarterly), and a report based on the Appointments table to display an itemized list of appointments for the numbered invoices for that period to export and print. This may not be the most efficient way to do it, but it's all I could come up with. The portal on the Assign Invoices layout is insufficient in cases where there are many appointments (75 or so) and doesn't display them as nicely. Now that I have forwarded the balances from one quarter to the next, I am running into the following problem:
Because the report is from the context of the Appointments table, only invoices that have appointments associated with them show up. This excludes invoices that have a balance due but no appointments for the period of the found set. If I run the report from the context of the Invoices table, I see all invoices, but of course only the first Appointment is listed in cases where there are appointments. Is there a way to display all invoices with a balance due and all appointments? (Please be gentle; I haven't scripted yet.) ;P
Please let me know if clarification is needed.