Check out getsummary(field;xxxx) That might work on a selection of sorted records.
1 of 1 people found this helpful
is there a clean way way to get get the sum of a calculation per record without creating a field with that calculation in that table?
Why do you wish to avoid adding that calculation field? Just seeking a more complete picture here.
That "line item cost" calculation is a very common thing to put into such a table.
ExecuteSQL ( "
SELECT Sum ( qty * pricePer ) FROM child
ForeignKey = ? ;
"" ; "" ; Parent::PrimaryKey )
But I'm not sure that the above use of Sum ( Expression ) is valid syntax in ExecuteSQL
I am looking to improve my performance over WAN by reducing unstored calculations.
my invoice records in this case have cost summary fields that are populated with a script when the invoice layout is close or a cost analysis popover is opened . this seriously improved my performance.
what I would really like this to do is not just do the qty * costPer from the same record but actually pull the price from a related record such as child_inventory::costPrice
ExecuteSQL might just be the ticket.
What you describe is a stored calculation as both fields are in the same record. Thus, this should not greatly affect performance. And displaying the line item cost is very frequently needed anyway as the user fills in an order.
Note sure if this is applicable:
Using a script to put the cost/price of an item pulled from the pricing table into the record while using calculated fields to do the math is OK.
Price = set by script pulling the amount from appropriate table.
Qty = Manually set or set by script
Total = calculated field Price x Qty
Since these are item records related to parent invoice record
the invoice record would have
Subtotal = Sum(items:Total)
Tax = scripted by amount from tax amount table
Total = Calculation of subtotal + tax
I always used scripts rather than calculated fields since these numbers should not change due to things like damaged or deleted item records. If the script totals were different from what is now displayed, then something went wrong.
A verification field using calculations would be used to compare the scripted totals with itself.
Scripts and calculation fields aren't the only options. An auto-entered calculation would work perfectly here.