Sum won't work with filtered portals as the sum function references the portal's relationship and this completely bypasses any filter expression you set up on the portal.
Define a summary field in the portal table that sums the desired field. Place a one row portal with invisible boundaries just below the portal you need a total for and place this new summary field in that portal and give this new portal the same filter expression as the portal just above it.
For your grand total of all the portals, you can probably use the sum function as this would be the same as computing the sum of one unfiltered portal to all your line items.
Thanks for that Phil, I've done what you said, the summary field (called OptionSummaryTotal) and a small portal is bang on, works perfectly filtered by QuoteRef and OptionNumber.
I've tried SUM-ing OptionSummaryTotal up to give a NetTotal but it doesn't seem to like it. Any suggestions?
As a work around for now I've created a copy of the small portal to OptionSummaryTotal from each of the options and removed the filter for OptionNumber so the filter is now just by QuoteRef. It seems to work OK for whole figures (ie. whole pence) but if you have a lineitem which ends up with a fraction of a penny, the three OptionSummaryTotal portals might read £0.31, £0.62, £1.20 but the NetTotal portal is £2.12 and not £2.13 because option one is actually £0.305 and another £1.195 and has been rounded up as we want currency to 2 decimal places.
Obviously it's a simple rounding problem because both sets of totals are looking back to the lineitems at different stages, rather than the NetTotal adding up the Subs, but it doesn't look right on the printout and a customer is bound to start questioning the extra penny they've been charged! Is the easiest thing to go right back to the components/stock table and have it so that our RetailPrice (worked out from TradePrice*Markup) is duplicated automatically into another field which rounds up to whole pence? And then use that whole number field from then on? Also might end up with the same problem with VAT and Gross at some point...
I'm about to start searching the forum reference the rounding, just thought I would put it on here in case you had a quick fix.
Thanks in advance.
Think I've sorted the rounding. I've made the LineSubtotal from this:
Quantity * RetailPrice
To this :
Round ( Quantity * RetailPrice ; 2 )
And that seems to work so far. Presume I do the same with any VATAmount fields...