Beverly Voth, ExecuteSQL, Level: Intermediate, Version: FM 12 or later

Aggregates (Summary Fields) in Filtered Portals

The question on one or more of the FileMaker forums was asked and answered. Q: How do you get the Count() of the related records in a filtered portal? A: (paraphrased) duplicate the filtered portal and make it one row in height. Place the related summary field, “Count of…”, in the single row filtered portal. Voila! your count is now filter-specific. (The instructions for creating the filter for a portal and summary field are below.)

This recent topic led me to consider what other aggregate fields could be used with the filtered portal. And what about that Go To Related Record script step? Does it only show the related FILTERED records or all related records? The demo (bvoth_aggregates_in_portals.fmp12) and article have been created to answer these questions.

I started using filtered portals in cross-tab reports shortly after they were introduced. These are generally ways to show something very specific in each portal with sorts and filters and usually one row only. I had not explored the use of aggregates in these cross-tab reports until now.

Kudos to those before me that may have discovered these answers and tricks, too.

Continue reading “Aggregates (Summary Fields) in Filtered Portals”

Level: Any, Version: FM 8 or later

Portal Sorting, part 3

Today we’re going to look at a couple more approaches to dynamic sorting, from opposite ends of the complexity spectrum. The simple one, portal sorting, circa 2002, is something I built in the FM 5.5 era. It uses a “smoke and mirrors” approach to achieve its objective, and apart from converting to .fp7 format, and consolidating into a single file, I’ve left it as is.

Behind the scenes this file has eight relationships from the parent table to the child; all based on the same keys, but with different sort orders. Continue reading “Portal Sorting, part 3”

Level: Beginner, Version: FM 8 or later

Portal Sorting, part 1

Experienced FileMaker developers will likely already be familiar with the following portal sorting trick, but I’ve worked with enough developers recently who aren’t (two, to be precise), that I figure it’s worth sharing here.

Problem: how do you sort a portal on a field that doesn’t live in the table the portal is based on? Take, for example, a simple sales database with this structure:

We want to view sales history for a particular product in a portal, like so: Continue reading “Portal Sorting, part 1”