7 Replies Latest reply on Feb 6, 2012 3:32 PM by philmodjunk

    Grand summarizing multiple items

    jda76

      Title

      Grand summarizing multiple items

      Post

      I searched the forum and couldn't find anything related to this... sorry if it's a repeat.

       

      I want to summarize the amounts for each newsletter ordered at the bottom of the report, like so:

       

      location1              amount

      Newsletter #1           10

      Newsletter #2           20

      Newsletter #3            5

       

      Location2             amount

      Newsletter #1           5

      Newsletter #3           8

      _______________________

      Newsletter #1 Total:  15

      Newsletter #2 Total:  20

      Newsletter #3 Total:  13

       

       

      I have no problem doing the whole report except for the last three lines.  Just not sure how to set up the sub-summary to make this happen.  Using FMP11 Advanced on Mac.  Any guidance would be greatly appreciated.... thanks!

        • 1. Re: Grand summarizing multiple items
          philmodjunk

          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.

          • 2. Re: Grand summarizing multiple items
            jda76

            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:

            distribution_distribution(by date)_NEWSLETTER.newsletterID

             

            Just now sure how to sum the distribution amounts from there considering the considering the distribution date.  Thoughts?

            Thanks! 

            • 3. Re: Grand summarizing multiple items
              philmodjunk

              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:

              distribution_distribution(by date)_NEWSLETTER.newsletterID

              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.

              • 4. Re: Grand summarizing multiple items
                jda76

                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.

                Thanks again!

                 

                • 5. Re: Grand summarizing multiple items
                  philmodjunk

                  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.)

                  • 6. Re: Grand summarizing multiple items
                    jda76

                    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!

                    • 7. Re: Grand summarizing multiple items
                      philmodjunk

                      That added detail does appear to be the straw that broke the camel's back here. Frown

                      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