6 Replies Latest reply on Feb 10, 2012 12:40 PM by techt

    SubSummary field in footer for record total

    techt

      I'm trying to place a sub-summary calc field into the footer, but the wrong amount is displayed. This is for a invoice report that has a summary page where the total bill is calculated, followed but x-pages where the invoice is broken down category, with a category total for each page. When the calc field is in a trailing subsummary part, the number is calculated correctly. When it's in the footer, it shows the total for the entire bill. This is expected behavior, but the client wants the category totals in the footer, not trailing the line items in the report.

       

      Short of rebuilding a rather complicated invoicing script (100s of lines) to break out and recalc for each category, I'm hoping somewhere out there someone has had something similar that might be a much quicker and more elegant answer for a subsummary calc in the footer.


      Thanks in advance,

       

      Tim

        • 1. Re: SubSummary field in footer for record total
          AlanStirling

          Hi Tim

           

          This is a nasty problem, as it's difficult to set category totals to display the correct result in the footer.

           

          But I have provided you below with a way of doing this, but it may not be possible to use it in your particular situation.

           

          Method:  Whilst testing different ways to resolve this, I realised that fields displayed in the footer come from the last record displayed on the page.

           

          So by adding an additional number field (in this case 'Value') to each record and displaying this field only in the footer, all I then needed to do was to write a script to go backwards through all the records to be printed, watching for a change in category and then in the last record of the category (as it changes when going backwards), put in the total for that category.

           

          This is prepared by using a Running Total, set to restart on change of category. So my script loads the running total on the last record of the category into my Value field, which then displays in the footer.

           

          There are still a few issues - if the category is long enough to go over the page, there will not be a total on any intermediate pages, only at the end.

           

          So please try this out and see if it does what you have requested.

           

          Best wishes - Alan Stirling, London UK

          • 2. Re: SubSummary field in footer for record total
            comment

            techt wrote:

             

            I'm trying to place a sub-summary calc field into the footer

             

            Have a look at the GetSummary() function.

            • 3. Re: SubSummary field in footer for record total
              RayCologon

              techt wrote:

              Short of rebuilding a rather complicated invoicing script (100s of lines) to break out and recalc for each category...

               

              Hi Tim,

               

              There's a relatively straightforward way to place sub-summary data into the footer part, with no scripts required (other than to sort the records, perhaps).

               

              As Michael has hinted, the GetSummary( ) function will allow you to return sub-summary values within a calculation. With that and a bit of work with conditional formatting, you'll be able to get group subtotals to appear in the footer of the last page in each group, and (if required), a grand total to also appear in the footer of the last page.

               

              Attached is an example that uses the above techniques.

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              http://www.nightwingenterprises.com

              ------------------------------------------------

              • 5. Re: SubSummary field in footer for record total
                techt

                Thanks Michael!

                • 6. Re: SubSummary field in footer for record total
                  techt

                  Hello Ray! The sample was wonderful. Michael's GetSummary() suggestion was spot on, but your detail and efforts really drove it home. Funny how you can work with FMP for years and still not touch things often enough to remember how to use them. I hope I can return the favor some day. Have a great weekend!