13 Replies Latest reply on Jan 31, 2014 2:52 PM by KurtSnow

    Incorrect Summary

    KurtSnow

      Title

      Incorrect Summary

      Post

           Attached is a report that lists payments and invoices. It is a reconciliation report ... to see if the invoices match the payments sent to each company. Everything is correct except the balance field. Instead of giving me the balance per station, it is the overall balance of all payments to all stations. The amount is correct. The report is set-up as follows:

           Sub-summary by company

           Sub-summary by transaction type (Invoice, Payment)

           Body

           Sub-summary by transaction type (totals)

           Sub-summary by company (Balance)

            

        • 1. Re: Incorrect Summary
          KurtSnow

               Here is the attachment

          • 2. Re: Incorrect Summary
            philmodjunk

                 When you refer to the value of a summary field in a calculation, you get a result based on the grand totals, not sub totals located inside sub summary layout parts.

                 To get the sub totals so that you can use them in a calculation, use the getSummary function. The "break field" parameter should be the same field that you have specified in the sub summary layout part that shows the sub total you want to use in your calculation.

            • 3. Re: Incorrect Summary
              KurtSnow

                   I cannot seem to get it to work. This is what I created:

                   GetSummary ( amountPaymentSummary ; amountPayMinusInv )

                   Notes:

                     
              •           amountPaymentSummary = Total of amountPayment
              •      
              •           amountPayment = If ( transactionType="Payment" ; transactionAmount ; "" )
              •      
              •           amountPayMinusInv is the field that shows the calculation for the balance amount in the report.

                   I also created a GetSummary for the invoices. Then I created a new field: getSummaryPaymentsMinusInv (getSummaryPayments - getSummaryInvoices). Nothing shows up no matter where I place the fields.

              • 4. Re: Incorrect Summary
                philmodjunk

                     amountPayMinusInv may not be the correct field to specify as the break field.

                     What field did you select as the "when sorted by" field for your sub summary layout part where you can see the total you want to use in your calculation?

                     That's the "break field" to use in the getSummary function call.

                     And where exactly on your report are you using this value? (You may be in the wrong group of records to get the correct value via GetSummary)

                • 5. Re: Incorrect Summary
                  KurtSnow

                       I finally had time to work on this some more. I created Get Summary fields to obtain sub-totals for invoices and payments with the break field of transaction type. Those fields produce the correct result. However, the balance field returns the same number as the summary for payments. My formula for the balance is: GetSummary Invoices - GetSummary Payments.

                  • 6. Re: Incorrect Summary
                    philmodjunk

                         That is to be expected. Get summary evaluates from the context of the current record and the group of records with the same value in the "break" field that are contiguous with it. Thus GetSummary invoices will be zero as you are calculating this in a group of Payment records.

                         It looks like you need to use Get Summary with the Company field as your break field.

                    • 7. Re: Incorrect Summary
                      KurtSnow

                           With company as the break field I now get 0 for the summary amount for payments and invoices.

                      • 8. Re: Incorrect Summary
                        philmodjunk

                             Does your current sort order include that field?

                             I can see a "company" based sub summary layout part, but I don't know exactly what field is specified as the break field for it. Whatever field that is should be the field used with GetSummary and you must have your records sorted in an order that includes that field in the sort order before it will return a sub total.

                        • 9. Re: Incorrect Summary
                          KurtSnow

                               See attached. Fields are ...

                               1 = Company ; 2 = transaction type; 3. date 4. GetSummaryInvoices; 5 GetSummaryPayments; 6. GetSummaryInvoices-GetSummaryPayments

                               Sort order = 1. Company; 2. Transaction Type; 3. Date

                               I have tried using as the break field Company, Transaction Type and Date. Company and date return no data. Transaction Type returns the correct amounts, but the balance field (GetSummaryInvoices-GetSummaryPayments) is not correct.

                          • 10. Re: Incorrect Summary
                            philmodjunk

                                 But do you sort by a company name field or a company ID? Which do you specify for the upper most sub summary part? (Can't see that in your post )

                                 What version of FileMaker are you currenlty using? (I may choose to upload a demo file that you can compare to yours and don't want to use a file format that you can't open.)

                            • 11. Re: Incorrect Summary
                              KurtSnow

                                   Sort by a company name.

                                   The report is set-up as follows:

                                   Sub-summary by company

                                   Sub-summary by transaction type (Invoice, Payment)

                                   Body

                                   Sub-summary by transaction type (totals)

                                   Sub-summary by company (Balance)

                                   On Filemaker Pro 12

                              • 12. Re: Incorrect Summary
                                philmodjunk

                                     Sometimes I can't see the obvious. I tend to set up revenue, expense type systems with separate fields for income and expenses. Am I correct that you use the same field for both income and expenses?

                                     I think that's the issue here. If so, I suggest adding a calculation field such as:

                                     If ( TransactionType = "invoice" ; -1 * amountField ; amountField )

                                     A summary field that summarizes that field should return the desired balance.

                                • 13. Re: Incorrect Summary
                                  KurtSnow

                                       Success! Thank you. I should have mentioned that they were not separate fields.