6 Replies Latest reply on Dec 11, 2016 4:19 PM by MauriceG

    Summing up different values from same field of multiple records

    MauriceG

      Hi,

      I have a list view layout (based on Assignment table) where each record has, amongst others, a Date field (displaying date fees for assignment were received), a Fees Field (displaying the amount of fees received for an assignment), a Monthly Total field (total fees received during the current month) and a Year Total field (total fees received during the current year).

      Fees field is an amount field

      Monthly Total field is a summary field: Total of Fees field

      Year Total field is a summary field: Total of Fees field

      As you will guess, this doesn’t produce the results I’m looking for. What I want to do is this:

      1. If fees in the first record of the current month is $20, then Monthly Total field should show $20. Then if the second record of the month has fees of $25, Monthly Total field should show $45, etc. Then, at the beginning of the following month, the last record of the current month must continue to show the total for the current month and the first record for the following month should start computing again from zero.
      2. I want the Year Total field to behave the same way.

       

      Any help would be much appreciated.

       

      Thanks.

        • 1. Re: Summing up different values from same field of multiple records
          philmodjunk

          Make them running total fields and choose the option that restarts the total when sorted (grouped) by month.

           

          Here's a calculation field (date result type) that you can use to do this sort/grouping:

           

          date field - day ( date field ) + 1

           

          This computes the date for the first day of the month for every date in the month.

           

          A similar method can be used for the yearly running totals.

          • 2. Re: Summing up different values from same field of multiple records
            MauriceG

            Thanks very much.

            I'm not entirely clear however on what to do. Do I need to create that new calculation field, or turn my Monthly Total field into a calculation field?

            • 3. Re: Summing up different values from same field of multiple records
              erolst

              You need one (new) calculation field, result type date (which you specify in the Edit Calculation dialog), for the calculation that PMJ suggested. This is merely for grouping purposes. You need to sort primarily on that field (but can also secondarily sort on the date field).

               

              If you want to display a header à la 'December', you can simply place that field or your existing date field into a leading sub-summary part and format it to only display the month. (Proceed in a similar fashion to display the year in the header.)

               

              You need to modify your existing summary fields** with the option 'running', and 'restart' on the new calculation.

               

              Then modify your sub-summary part (or create one) to use that new calc field as the break field. You need to use a trailing sub-summary part for a correct display.

               

              If you want to see the carry-over in the header of the following month, create another calc field as

               

              Let (

                thisRecord = Get ( RecordNumber )  ;

                If (

                  thisRecord <> 1 and theNewCalculationField <> GetNthRecord ( theNewCalculationField ; thisRecord - 1 ) ;

                  GetNthRecord ( theSummaryField ; thisRecord - 1 )

                )

              )

               

              make sure to set it to unstored, and place it in the leading sub-summary.

               

              * Actually, you only need a single summary field Total of: for the same field, not two; a summary field will display different results depending on the layout part you place it in.

               

              In a trailing grand summary, it will display the total for the entire found set (in your case, the year); in a sub-summary part, it will display the total for that group, in your case, the individual months.

              • 4. Re: Summing up different values from same field of multiple records
                MauriceG

                Thanks to you erolst, and also to philmodjunk.

                 

                 

                I created the new calc field and modified my existing summary fields the way you said, and my calculations now all work the way I want them to work. But I’m having a hard time creating a summary report. I read the FileMaker Training Series chapter on summary reports, and tried and tried again, but I can’t get it to work. There is always something missing or not in the right spot.

                 

                What I want this report to display is this:

                Year

                     Month

                          Date + Amount of Fees

                          Date + Amount of Fees

                     Monthy Total

                     Yearly Total

                Year

                     Month

                          Date + Amount of Fees

                          Date + Amount of Fees

                     Monthy Total

                     Yearly Total

                etc.

                Total for all years

                 

                Can you clarify for me in which part of the summary report each of these elements should go, and using what fields?

                 

                Thanks.

                • 5. Re: Summing up different values from same field of multiple records
                  erolst

                  I think this is easier to show than to tell.

                   

                  Give me a minute, and I prepare a sample file for you ...

                   

                  EDIT: Here you go.

                   

                  Note that if you want to have a running total with restart on two different sort fields, then you need two differently defined summary fields for your value field.

                   

                  To see this in action, go to the Report layout and make sure to launch the sort script; all the logic in the summary fields and the layout part definition doesn't do you any good if the sort order is not correct.

                  1 of 1 people found this helpful
                  • 6. Re: Summing up different values from same field of multiple records
                    MauriceG

                    Thank you so much. My report now works. The script was clearly the biggest issue.

                     

                    I have another question, but it is not directly related to this report, so I'll start a new thread.

                     

                    Thanks again and have a good day.