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

    Total for a field

    RedzwanLatif

      Title

      Total for a field

      Post

      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
          philmodjunk

          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
            RedzwanLatif

            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
              RedzwanLatif

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

              • 4. Re: Total for a field
                philmodjunk

                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
                  RedzwanLatif

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