5 Replies Latest reply on May 9, 2012 9:40 AM by RedzwanLatif

    Total for a field



      Total for a field


      Hi. I have one parent record with many child records. I need to count the sum of a field in child records.For example, I have a customer record. Then, the customer have many payments record. There's a field named Amount paid in the child records. Now, I have to count the sum of the field 'amount paid' of a customer How can I do this? I've created the relationship. All are going well except for this. Is there any calculation for this?

      Thanks in advance :D

        • 1. Re: Total for a field

          Sum ( paymentsTable::amount )

          can be defined as a calculation field in the customer table.

          A summary field that computes the total of Amount can also be defined in the payments table and if you place this field on your customer table, it will display the portal total.

          • 2. Re: Total for a field

            It doesn't work. It still give the same value of a child record. It does not sum all the child records.

            • 3. Re: Total for a field

              I tried again it worked. Thanks PhilModJunk :D. Really appreciate your help :D

              • 4. Re: Total for a field

                I use this method all the time.

                If you have this relationship:

                Customers::CustomerID = Payments::CustomerID

                Then defining a calculation field in customers as: Sum ( Payments::Amount ) will return the sum of all related payments records. If you define the field in Payments, it will do what you are describing in your last post.

                And if you define a summary field in Payments as the total of Amount, you can add this field from payments to your customers layout to show the portal total. I usually use the first method as it will update better if I am editing the amount field inside the portal--the summary field needs a trigger controlled script before it will update smoothly.

                If your relationship is different from what I have above, please post what relationship you do have.

                • 5. Re: Total for a field

                  The relationship is same as you stated above. I defined the calculation field in customers table and it's worked.