Jump to content
Sign in to follow this  
Justin P.

Genrating a total of a total

Recommended Posts

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!


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


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.


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.


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?

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

By using this site, you agree to our Terms of Use.