So I have the following table of Discounts:

Discount Name | Discount Category | Discount Amount |
---|---|---|

Customer Loyalty | Promotional | $32.50 |

Staff Meals | Promotional | $48.00 |

Kitchen Problem | QSA | $16.00 |

Alumni Discount | Promotional | $8.25 |

Dissatisfied Customer | QSA | $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...

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 …

)