Tag: Summary Field

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

Aggregates (Summary Fields) in Filtered Portals

beverlyvoth

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: Intermediate, Version: FM 11 or later

Summary Report in a Filtered Portal

Kevin Frank

Have you ever wished there were an easy way to summarize the contents of one portal in another portal? Perhaps something along the lines of the “Summary” portal below?

I’ve touched on the possibility of using a portal to produce (or assist in the production of) an on-screen summary report a couple times in the past…

…but today we’re going to look at a fresh approach, and if you wish, you can follow along in today’s demo file, fm11-summary-report-in-a-portal, which uses a filtered portal and therefore requires FileMaker 11 or later. Continue reading “Summary Report in a Filtered Portal” →

Level: Intermediate, Version: FM 8 or later

Fast Summaries

Kevin Frank

Editor’s note: for additional thoughts on this subject see Fast Summaries Revisited and Fast Summaries Re-revisited.

Have you ever wished you could pull the values from a summary report, and use them in a script or a calculation? Back in the FileMaker 6 days, Mikhail Edoshin introduced a technique to do just that.

He called the technique Fast Summaries, and I came to employ it extensively in my own solutions. Continue reading “Fast Summaries” →

Level: Beginner, Version: FM 8 or later

Unique Records Revisited, part 1

Kevin Frank

A while back I posted a technique to identify and count unique records. I recently discovered that while the technique is 100% reliable in terms of identifying unique records, under certain circumstances it will fail to count them correctly, due to what I believe is a bug in the way summary fields work. You can easily reproduce the bug by following the instructions in this demo file: broken summary field

Fortunately there are various ways to skin this particular cat, and today we’re going to look at one of them (demo file: broken summary field workaround).

The method for indentifying unique records hasn’t changed. Continue reading “Unique Records Revisited, part 1” →

Level: Intermediate, Version: FM 11 or later

Filtered Relations, part 2

Kevin Frank

Today we’re going to take another look at the challenge we encountered in part 1 of this series. The challenge is: given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?

One thing is certain: assuming a normalized data model (with the date in the Invoices table), some sort of trick will be required. In part 1, we leveraged the FileMaker relational model to solve this problem, by adding additional table occurrences to the Relationships Graph, as well as some calculated fields in the products table. Continue reading “Filtered Relations, part 2” →

Level: Intermediate, Version: FM 8 or later

Reorder Based on Summary Field

Kevin Frank

One of the useful things you can do with a FileMaker summary report is reorder it based on the contents of a summary field. Since this is a bit abstract, especially if you’ve never done it before, let’s look at a concrete example.

Demo file: 2010-12-18-salespeople-by-state

Your organization has sales reps in various US states. In fact, many of the states have multiple reps. Here’s a simple table showing some of the sales reps and the state they’re associated with:

You’d like to generate a report showing the number of reps in each state, so your first step is Continue reading “Reorder Based on Summary Field” →

General, Level: Intermediate, Version: FM 10 or later

Ranking Entries in a Summary Report

Kevin Frank

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

Yesterday we looked at a simple method to flag unique entries in a found set. This time, we’re going to look at an additional use for this technique, using the same data set and demo file as last time.

As you may recall, we have a simple table of sales data, and previously we produced a summary report sorted by salesperson, but reordered by total sales, so that the top performing salespeople appeared at the top of the report.

Continue reading “Ranking Entries in a Summary Report” →

General, Level: Intermediate, Version: FM 10 or later

Identifying Unique Records

Kevin Frank

[Update 15 May 2011: see this posting for additional information about this technique.]

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

A question that comes up regularly on various FileMaker forums is some variation on “I have a table of sales data for my organization. For a given found set within that table, it’s easy to produce a report grouped by salesperson showing number of sales, total sales amount, etc…

…and at the bottom of the report, I can easily display grand totals for number of sales and total sales amount…


Continue reading “Identifying Unique Records” →