6 Replies Latest reply on Mar 20, 2013 6:38 AM by pilot_john

    Totals at the bottom of EACH page.

    pilot_john

      Title

      Totals at the bottom of EACH page.

      Post

           Hello,

               I have a pilot logbook table & layout in my filemaker pro 11 database. I'm using Mac OS 10.7.  When I print this layout, I want a total for each column at the bottom of each page. I actually want 2 rows in the footer section. I want one that shows the totals for the current page, another one that is a running total that shows the the total of all previous pages plus the current page. If I could firgure out one, I'm sure i'd figure out the other but right now I can not get either one to work. I already have a summary field for each column that I want totaled. and when I put it in the footer it shows the totals for all records in the found set, which makes it show the same number at the bottom of every page. I need it to recalculate a new number for each page.

               As you can see in the screenshot, it shows 23 landings when there are only 4 landings on that page. (The day landings is the field that I keep working with. I figure there's no sense in changing every summary field until I can get one of them to work. )

               I have tried adding a field to the body that's not in the printable area that is an unstored calculation field with the calculation: Get ( PageNumber )     and then under the options for one of my Summary fields, I selected "Running Total", and then selected "Restart summary for each sorted group", and then selected When Sorted By: "Page Number".  (Where "Page Number" is the name of the calculation field that gets the page number.) And then I sorted the records by Page Number and clicked Preview but that didn't change anything, each page still only shows the total of all records.

           Please help,

           Thanks.

      Screen_Shot_2013-03-19_at_4.02.42_PM.png

        • 1. Re: Totals at the bottom of EACH page.
          philmodjunk

               What you need is a sub summary layout part that appears at the bottom of each page. Summary fields placed inside the sub summary part will show a total just for that page. The same field in a trailing grand summary can show you the total for the entire report.

               To get a sub summary layout part to appear at the bottom of each page, you'll need to add a field that increments with each page and then you use that field as the "when sorted by" field for the sub summary layout part. To ensure a clean page break, you can specify that a page break occur after every 1 occurrence.

               Here's how you can load your page number field with a value that incements with each subsequent page.

               You can:

               find your records
               Sort them into the desired order that you want for your report
               Use replace Field contenst to apply the page number values
               Sort again by the page number field plus the desired order from step 2 above.

               Here's a calculation that Replace Field Contents can use to apply a page number to each record:

               Ceiling ( Get ( RecordNumber ) / RecordsPerPage )

               if you will have 35 records on each page, you'd use 35 in place of "recordsPerPage".

               I think you can get your second set of summary totals if you define a second set of summary fields and define them to produce running totals.

          • 2. Re: Totals at the bottom of EACH page.
            pilot_john

                 To get a page number, why can't I use the Get ( PageNumber ) calculation?

                 In preview mode, the body of the layout shows the correct page number, using that calculation in an unstored field.

                 But when I move fields out of the footer and into the new subsummary part that you suggested, the fields won't display in preview mode. Why is that?

                 I haven't yet followed your directions exactly, but I am about to go try that, I will repost if it suceeds.

                 How can I upload a copy of the database? (I have already made a copy on my desktop and stripped it of sensitive information.)

                 Thanks for your help.

                  

            • 3. Re: Totals at the bottom of EACH page.
              pilot_john

                   Ok I did exactly as you said, and it worked like a charm !   Thanks, so much.  

                   I'm not 100 % sure why I had to do it that way though. I have so much to learn about filemaker.

              • 4. Re: Totals at the bottom of EACH page.
                pilot_john

                     New question, but related to the above. ( So should I start a new thread? )

                     I finally got all of that to work just perfect and layed out and formated the way I wanted it. Now I thought I would go in and add a third line in-between the page totals and the running totals. This line would show the difference between the two. If I could do that, then the report would look a lot more like a typical logbook. It's standard that every printed pilot logbook has 3 lines at the bottom, one for current page totals, totals forwarded from previous page, and one for totals to date.

                     Now I don't see how to use a summary field to do this nor am I getting a calculation field to work for this. I apperciate any advice.

                     Thanks.

                • 5. Re: Totals at the bottom of EACH page.
                  philmodjunk

                       Get ( PageNumber ) returns the value of the page number, but only while in preview mode and may not be accurate for every record because you have to allow some space for the sub summary part that won't be part of the preview because it relies on values you haven't yet assigned to the records in your report.

                       When you refer to a summary field in a calculation, you get the same result that you see when you put the summary field in a trailing grand summary. To use the summary field to get a sub total like you see in the sub summary part, use the getSummary function. The "break" field you specify in this function should be the "when sorted by" field you specify for the sub summary part.

                  • 6. Re: Totals at the bottom of EACH page.
                    pilot_john

                         and again, that worked perfectly. Thanks you so much for helping.