Genrating a total of a total

[Justin P.]

I feel like there's a much better way of doing this on order to generate what seems like a simple report, but cannot work out how... always struggled with report summaries and subsummaries!

SCHEMA

Table A = clients

Table B = invoices

B is joined to A by two foreignKeys for the purpose of this relationship

clients::ID = invoices::ID_client

clients::REPORT.ON.YEAR (global) = invoice::year

REPORT

I then have a report using table A CLIENTS.

(1) Requests a report year

(2) Finds all active clients

-- whether they have an invoice or not, thus why we need to do this from table A occurrence)

(3) Shows all one row for each active clients, and their invoice total for the report year specified.

-- invoices::%totalAmount is a summary field Total of invoice amounts)

(4) Shows total invoice amounts in a subsummary section (grouped by a category) and grand total in a footer.

CHALLENGE

My issue is with some subsummary and grand total in the footer.

In order to show the TOTAL VALUE of all invoices across all clients for specified year in the report, I could not just use %total, that would only show the top record in each category.

Instead I had to create these two fields in table A CLIENTS

{a} clients::invoiceAmountByYear_thisClient (calculation) = Sum ( invoices::amount)

Purely to feed the next field...

{b} clients::subtotal_InvoicesByYear (summary) = Total of invoiceAmountByYear_thisClient

Then I use {b} on the report for subsummary and total.

ISSUE:

To be clear, the approach above works, but of course subtotal_InvoicesByYear is a summary of a unstored calculation field, so you get a long period of summarizing every time you run it.

Additionally, just seems I am repeating myself, in that invoices::%totalAmount and clients::invoiceAmountByYear_thisClient are basically the same thing.

Seemed simple when I started, what am I missing Filemaker Gurus?

[comment]

If you would generate your report from the Invoices table, then the one summary field would serve both purposes. Since you want to include clients that do not have any invoices to report on, you must do it the way you have described.

[Justin P.]

Thanks. I hate it when I'm right, but it's this ugly. :(

[comment]

Are you sure it's the grand total summary that slows things down?