2 Replies Latest reply on Aug 8, 2014 8:00 AM by ABuchtel

    Trouble with Summary Field



      Trouble with Summary Field


           Good morning! I have three Tables, “Events”, “Bills” and “Payments”. 


           For Every Event there are typically two Bills, sometimes more. The first Bill is typically the “Deposit” required. The second Bill is typically the “Balance Due”. Sometimes there are Additional Charges. Typically a client will pay a “Bill” in full with one Payment, but there are times when multiple payments are made towards one “Bill”. 


           The Bills Table has the fields: 

           NAME                   TYPE

           BillID                     Text (Auto-Enter Serial)

           fk_EventID            Text

           DueAmount           Number

           DueDate                Date

           c_SumBills             Summary  = Total of DueAmount

           c_MonthDue Calc   = If ( Month (DueDate) < 10 ; Year (DueDate) & "0" & Month (DueDate) ; Year (DueDate) & Month (DueDate) )


           The Payments Table has the fields: 

           NAME                   TYPE

           PayID                    Text (Auto-Enter Serial)

           fk_BillID                Text

           fk_EventID             Text

           PaidAmount          Number

           PaidDate                Date

           c_SumPayments     Summary  = Total of PaidAmount

           c_MonthPaid Calc    = If ( Month (DueDate) < 10 ; Year (DueDate) & "0" & Month (DueDate) ; Year (DueDate) & Month (DueDate) )


           The Bills and Payments Tables are Joined (=) by the BillID field.


           I have a report, based on the Bills table, that shows, for each Event:

           Amount Billed        Amount Paid

           c_SumBills            c_SumPayments


           The report is sorted by c_MonthDue then DueDate, so there’s a sub-total after every month.


           The Body of the Report works perfectly. 


           In the Sub-Summary, the c_SumBills displays the Total Amount Billed for all listed Events. But, the c_SumPayments displays *only* the last Amount Paid (as displayed in the Body). 


           And in the Trailing Grand Summary  c_SumBills displays the Total Amount Billed for all listed Events in all months, but c_SumPayments is entirely blank. 


           If anyone can point out where I’ve gone astray, I would greatly appreciate it. Thank you!

        • 1. Re: Trouble with Summary Field

               The two summary fields have different evaluation contexts. This makes no difference in the body where it's one event to a row of data, but makes a big difference in other parts of your Bills based report.

               A summary field from a related table (payments) will compute a total based on what records in the related table (payments) are linked to the layout's current record (Bills). The total computed will not in any way be influenced by the found set of records, sub summary or other layout part locations on your Bills based layout.

               A summary field from the layout's table (Bills) on the other hand, will show a sub total in the sub summary layout part rather than the expected grand total as you see in your grand summary layout parts.

               I suggest using a calculation field in Bills that uses the Sum Function to total up payments for that event. Then add a summary field to bills that totals this calculation field. This will then give you two summary fields with identical evaluation contexts.

          • 2. Re: Trouble with Summary Field

                 Thank you! Works perfectly!