Need help with calculation and/or summary

[Tdot33]

I have a layout that is for invoices with several calculations. The layout is in categories, each category has a quantity and a dollar total based on that quantity and a grand total of the quantity of all the categories and all the dollar totals. I also have a layout that is a grand yearly total (summary) of all the invoices in the first layout.

What I would like to do is find a way to create a new layout that separates the invoices by month so that I can get a total for each category AND the grand total for each month (We have to hand in monthly reports and I cannot figure out how to do this without creating a new database for each month).

As you can probably tell, I am quite new to Filemaker and have not yet learned sophisicated calculations. I called Filemaker directly to help me with this query, but they wanted to charge me $45. The person I spoke to suggested sub-summaries, but I do not know how to get the sub-summary to calculate the total for each category for each month, only for the grand total (and even then it doesn't work) and on top of it, I cannot get the sub-summary to appear in Browse mode.

Please help me with this problem. This is for my job and I am on a deadline and I am pulling my hair out trying to figure this out!!!

Thanks!!!

FileMaker Version: 6

Platform: Windows XP

[QuinTech]

Tdot, you may be doing it correctly without even knowing it. Sub-summaries do not work in browse mode, only in preview mode.

Make a sub-summary when sorted by month. Into this sub-summary, drop the fields for Month and Grand Total (this should be a summary field = sum of Dollar Total). When you sort your records by month and go to preview mode, you will see a breakdown of totals by month.

Now go back to layout mode. Above the sub-summary by month but below the header, add a sub-summary by category. Drop the fields Category and (again) Grand Total into this layout part. In your footer, put (once again) Grand Total. This layout should have no body. Now sort records by Month and then Category (in that order), go to preview mode, and you should see the report you're looking for.

Let me know if i glossed over anything.

J

[Tdot33]

I have done exactly what you told me and when I go into preview mode, my page is blank. I don't know what I am doing wrong....

[Tdot33]

Actually, I got it. I wasn't putting the sub-summary on the invoice layout, I was making a separate layout for it. Now the only thing I want to get rid of is when I go into Preview mode I have to bypass all the invoices (and after every invoice is the subsummaries, but they are all blank) before I can get to the page that has the summaries with the info I need. What am I doing wrong here? Or is this just the way it is?

Thanks for your help, I really appreciate it.

[QuinTech]

If i understand your request correctly, you want a report to print that shows every invoice for March, then a summary of those invoices; then every invoice for April, then a summary of those invoices; then a summary of all invoices for all months.

If this is what you want, i think you have your design down correctly. I'm not sure why the sub-summaries are blank; try putting some boilerplate text into each sub-summary ("this is the sub-summary by Month") to see if the sub-summaries are being skipped or if they're just printing with no data. I'll hazard a guess (based on the fact that you say the sub-summary prints after EVERY invoice) that your sort order is inappropriate. Make sure the sort order shows only Month then Category; otherwise, you'll get weird results, especially if you put Invoice Number first in the sort order.

If you just want a summary of the invoices (without printing individual invoices), then you should create a separate layout with exactly three parts: sub-summary by Category, sub-summary by Month, and footer.

J

[QuinTech]

Just to emphasize, sort order is critical for sub-summaries.