2 Replies Latest reply on Jun 20, 2017 3:18 PM by philmodjunk

    Help: Need Calculation for Summary of a field with a specific category...

    TimRogers_1

      So I have the following table of Discounts:

       

      Discount NameDiscount CategoryDiscount Amount
      Customer LoyaltyPromotional$32.50
      Staff MealsPromotional$48.00
      Kitchen ProblemQSA$16.00
      Alumni DiscountPromotional$8.25
      Dissatisfied CustomerQSA$18.50

       

      What kind of formula will work to see the totals of the discount categories?

       

      ex. I need to see Total Promotional = $88.75, and Total QSA = $34.50, etc....

       

      I can do a portal for each category, filtered by each category, but that only really displays the number, I need a number I can use in further calculations.

       

      Thank you in advance for any help on this...

        • 1. Re: Help: Need Calculation for Summary of a field with a specific category...
          erolst

          TimRogers_1 wrote:

          I can do a portal for each category, filtered by each category, but that only really displays the number, I need a number I can use in further calculations.

          If you ...

           

          - create a summary field “Total of”: Discount Amount in the Discount table

          - put this field into a filtered portal (the field can be invisible; or create a one-line portal with the same filter just for the field; this is nice for display purposes, where having the result in every portal row would be distracting)

          - give the field an object name (e.g. "total_promo", or "total_QSA")

           

          ... you can get the result for the respective filtered set using GetLayoutObjectAttribute ( "total_promo" ; "content" ), or GetLayoutObjectAttribute ( "total_QSA" ; "content" )

           

          If you need the result inside a script, use e.g.

           

          ExecuteSQL ( "

            SELECT SUM ( \"Discount Amount\" )

            FROM Discounts

            WHERE \"Discount Category\" = ?

            " ; "" ; "" ; "Promotional" // or "QSA", or a field or variable reference …

          )

          • 2. Re: Help: Need Calculation for Summary of a field with a specific category...
            philmodjunk

            Or you can just use that summary field in a summary report.

             

            Set up a list view layout based on your table. Add a sub summary layout part "when sorted by" your category field. Put this summary field and the category field into this layout part. Delete the body layout part.

             

            Do a find for the records you want in your report and sort them by category. (subtotals will not appear until records are sorted by category.)