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...

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...

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...

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.)