1) In donors, define a calculation with the sum function to compute the total donations for that donor. On your report layout perform a find that puts "> 1000" in this field.
2) if this is a straight summary field, putting it in a footer, header or grand summary part should compute the total for the entire found set. If it doesn't, check it's position in the layout. Any chance it's a pixel or two too close to a body or sub-summary part? If even one pixel of this field touches or crosses the line separating the part, you may see it treated as a field on that layout part instead.
Thank Phil, you rock! #1 worked perfectly. There are a couple of good Filemaker tricks there.
For number two, I tried making a big footer and putting the summary field clearly in that part, but I seem to return the toal donations for the first donor on the list, rather the total for the found set. I'm using the summary field Donations::Total_Amount, which =Total(Donations::Amount). Maybe I should have this field in the donors table instead as with #1?
You shouldn't need to.
Simply defining a field of type summary, chosing the "Total of" option and specifying the amount field as the field being totaled should result in the field reporting the grand total in the footer or grand summary part and a sub-total when placed in subsummary parts.
Thanks Phil. I think I've done that. What seems to be happening is that the found set is in browse mode, and whichever individual donor is selected is the total that will be displayed in the footer.
No, I don't think so, something is going on that we are missing.
I just pulled up one of my summary reports , changed the trailing grand summary to Footer, returned to browse mode and clicked on different records. The summary field always returned the total of the entire found set--not the current record.
Here's a very simple demo file: http://www.4shared.com/file/QS79yY8G/SummaryExample.html
Look it over for differences. Note that placing the summary field in the footer, trailing grand summary and body all compute the total for the found set. Putting the same field in the subsummary part computes a subtotal for that group.
Thanks Phil. Looking at your file, the primary difference I see is that for each record (each donor) I am pulling a group of related records (individual donations), that are correctly totaled for the summary field in the body part. So in a sense that is acting as a found set for each donor. Then when I need to a grand total, I need to sum those subtotals. So that's a little bit different than in your example (the field appears the same in the body part and the footer, same as mine works now, but I need the behavior to be different in the body part and the footer.) Does that make sense? (It's getting a bit late here)
Hey, maybe I'm onto something here. My current summary field totals the "amount" field in the "donations" table.
If I base a layout on the donors table, and make a new summary field in "donors" which instead totals the "amount_sum" field (the new sum function field that you suggested, it also sums the "amount" field in the donations table) ... then I put that new summary field in a trailing grand summary part I get the correct grand total!