Sub-summarys within portals pt II

Welcome to Pt II of the sub-summary portal tricks post. As it continues straight from the first post it may be worth skimming through that post.

Summarising the data

The user is asked, when they create a new line, if they want to create a line item, a sub summary or a grand total. The type is important as it controls the conditional formatting.

Grand totals are easy enough to work out using a self join on the parent key. In this case _kf_quote.

Sub totals are a little more problematic. The solution is to use a range based on the sort field we are currently in back to the last sub summary.

The calc used to find the sort value of the previous sub summary (or 0 if there are no previous sub summarys) is shown below. The crux of the calc is to list all keys of sub summary records in sort order. By finding the position of the previous key then using that to get the sort value we can then find the starting range of records.

The sub summary is therefore the sum of records across the join using range criteria.

Damian Kelly

Damian has been a database developer with Linear Blue since 2007. During this time he has developed complex FileMaker solutions in a broad range of industries from Finance and Investment to Manufacturing to New Media Promotion. Damian graduated in Production Engineering from the University of Hertfordshire in 1996, attained his Masters of Engineering from Kingston University in 1997 and MBA from Heriot Watt in 2006. He holds certification in FileMaker versions 7 through 12 and in Oracle MySQL 5.

More Posts - Website

1 thought on “Sub-summarys within portals pt II”

Leave a Reply