calculted field: total of filtered child records

[Fensterbaby]

I have a parent table, call it Orders that has related records in lineItems. LineItems contains records for many orders, related to Orders by order_ID.

I want a calculated field in Orders that is a running total of all related records in lineItems.

I don't know how to do this sort of filtered calculation.

thnks

[Søren Dyhr]

There is a good basis for making a need to know/nice to know distinction here - because what you wish to doesn't scale particular well.

There is a metaphorical difference between a spreadsheet and database I wish to stress here. Such figures might be cool as live updating calc's but in the database realm is it usually done by running a script and turn a special layout your data into a special form.

There is one more thing to consider, how would this summing up deal with canceled orders, ideally should a summary report be made where the data is most atomicly presented ... the Itemlines file. But if a cancelation only are done via a boolean field in Orders will the summary be inconsistent.

--sd

[Fensterbaby]

Thanks. I get what you're saying and appreciate the comments on spreadsheet vs database conceptual and structural distinctions.

Think you're right about keeping summary data at a lower level,the lineItems level, rather then trying to store it too high up the food chain.

Thnks

[comment]

If you have a summary field in the child table (LineItems), you can place it on a layout of the parent (Orders) to get a sub-summary for each parent record. Not sure what "running" means in this context, unless you place the same summary field in a portal to the child table.

Alternatively, a calculation field in the parent table =

Sum ( Child::Value)

can provide a similar result.