2 Replies Latest reply on Nov 2, 2013 1:03 PM by braxton

    Summary Field Question



      Summary Field Question


           I have a list of customers.  They are all committed to pay for 12 months of service.  Some pay monthly, every two months, every 3 months, and even one that paid 12 months in advance.

           I have a related table to the client table called PaymentAccounting that shows each payment made by a client and how many months that payment represents - QuantityMonths.  I create a record for this table through a portal for each client in the client table.

           Regarding the QuantityMonths field that shows how many months a payment represents, I would like to see its total for each client.  For example, the 3rd payment of a client that pays two months at a time would have a QuantityMonthsTotal field value of 6.  How can I do this and have it show in the PaymentAccounting table.  Any help is much appreciated.  Thanks.

        • 1. Re: Summary Field Question

               There are several ways to get a summary total or other aggregate value from a set of related records. A summary field in PaymentAccounting can be placed on the Client layout to show the total of all records related to that client. Placed on the PaymentAccounting layout, it will show the same total if your current found set is the same. (May need to perform a find for the correct set of records.) You can also set it up as a running total summary field if you want to see how each record changes the total.

               From the Client layout, you can also use a Calculation field in Client that uses the sum function or a summary field in a filtered portal to show a total of only the related records that meet the criteria specified in the portal filter.

          • 2. Re: Summary Field Question

                 Thank you Phil.  I'm using two of your solutions.  I had a summary field in PaymentAccounting, but I didn't want to filter to get the number.  I have FM 10 so no portal filtering.  Making an aggregate field in the Client table with the sum function of the QuantityMonths from the PaymentAccounting table works like a charm.  I can place that in the rows of the portal.   I can also place it in the rows of the list of all clients' payments in the PaymentAccounting table.

                 I'm also taking the QuantityMonths summary total from the PaymentAccounting table and placing it in the Clients table to show the total months paid by that client.

                 Thanks again.