Don't think there is a way to set up a sub summary for that, you'll need to use a different approach. (Sub summaries are tied to the groups into which you've sorted your records and this "recap" of the different totals doesn't fit the sorted groups need for the totals broken down by location and newletter.)
Do you have a related table where you have one record for each newsletter? If so, we can set up something that will list your totals in a portal.
Yes! Thanks for you response.
I played around with a portal and was able to get the correct newsletter titles to display with a portal using this relationship:
Just now sure how to sum the distribution amounts from there considering the considering the distribution date. Thoughts?
It's a matter of matching a record in this newsletter table to your records in the distribution table by newsletter. This may look weird if you are not familiar with using more than one table occurrence of the same table in FileMaker relationships.
I really can't decode what you posted here:
So I will simplify the terminology and you'll have to substitute your names for mine.
You have two tables, Newsletters and Distributions with table occurrences of the same name. (Table occurrences are the "boxes" found in manage | database | Relationships.) Your layout shown above is based on distributions and we can use a portal to list all records in Newsletters:
Distribution::anyfield X Newsletters::anyfield
I will also assume that you have a field named NewsletterID that exists in both tables, a serial number in Newsletters to match to individual records in Distribution.
We can then make a new occurrence of distribution to place "down stream" in these relationships to use to get our totals for each newsletter to put in the portal:
Newseletters::NewsLetterID = DistributionByNewsletter::NewsLetterID
You can now place a summary field from DistributionByNewsLetter inside the rows of the portal to NewsLetters to show the totals distributed broken down by newsletter. You can use a portal filter here to limit the totals to distribution records in a specified date range.
Thanks again for taking the time to reply.
I understand everything above except how to get the portal filter to work. The portal is displaying Newsletter records, yes? How can i "use a portal filter... to limit the totals to distributions records in a specified date range."? How can I limit a summary field using a portal filter?
The portal displays the Newsletters just fine, without the filter, it shows the sum of all records, as expected. I add the filter and the portal displays no Newsletters.
The more I think this one through, the more I find that a portal filter won't work where adding some fields to a relationship that filter the data will.
In distribution, you presumably have a date field recording the date that newsletter was distributed. I'll call it "distributionDate".
Add two global date fields, gDate1, gDate2. Define them in the NewsLetters Table.
Make this your relationship:
Newsletters::NewsLetterID = DistributionByNewsletter::NewsLetterID AND
Newsletters::gDate1 < DistributionByNewsletter::DistributionDate AND
Newsletters::gDate2 > DistributionByNewsLetter::DistributionDate
Now you can use a count function in Newsletters or a summary field from DistributionByNewsLetter to count the number of records for each Newsletter distributed within the specified date range.
How can I limit a summary field using a portal filter?
While the need to summarize values one relationship removed from the portal's table keeps it from working here, you can add a one row portal to a layout with the same portal filters as the main portal and then a summary field from the portal's table will be limited by the portal filter. (A calculation field using Sum or Count is not limited in this manner.)
This is great and makes total sense to me.... thank you. However, the report is actually more complicated than my original description. It's my fault for not thinking it was necessary. There is actually a sub-summary above Location called Neighborhood. I'd like to see this portal "summary" and the end of each Neighborhood - listing the totals of each publication distributed in the locations in the neighborhood - and then a grand summary again at the end. The above would work for the grand summary at the end; Is it even possible to have a portal like this for each neighborhood given I need to set values in global fields in Newsletters? I imagine I'd have to also have Newsletters::gNeighborhood, but that's, obviously, only going to work for one neighborhood? Thoughts? If I have to settle for only have the portal at the end, so be it.
Thanks again for your time!
It's rather awkward with redundant data, but if you replaced your Newsletters table with a table where each record was one newsletter for one neighborhood, your relationships could be:
Distribution::Neighborhood = NewslettersByNeighborhood::Neighborhood
NewslettersByNeighborhood::Neigborhood = DistributionByNewsletter::Neighborhood AND
NewslettersByNeighborhood::NewsLetterID = DistributionByNewsletter::NewsLetterID AND
NewslettersByNeighborhood::gDate1 < DistributionByNewsletter::DistributionDate AND
NewslettersByNeighborhood::gDate2 > DistributionByNewsLetter::DistributionDate