Reorder Based on Summary Field

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 to define a summary field to count them (incidentally, feel free to follow along in the accompanying demo file). Let’s call it s_count, and since summary fields of type “count” can only count non-empty fields, make sure to point it at a field in your salesperson table that is guaranteed not to be empty, e.g., the primary key, which in this case is named “id”. (You don’t want to point it at the first or last name, because if that field were ever left blank, that salesperson would not be counted.)

Next, create a report layout based on the salesperson table, like so:

Now go into browse mode (or preview mode if you’re in version 9 or earlier), sort by state, and you’ve got yourself one fine report. Except… this report is sorted alphabetically by state, and you realize that you’d prefer to see the states sorted by the number of salespeople per state, in descending order (i.e., from largest to smallest).

At the risk of pointing out the obvious, this is your current sort criteria:

But what about this “reorder based on summary field” feature? Since s_count is a summary field, you can reorder your report by this field — effectively sorting it again, without losing the state grouping. To do so,  you’ll need to modify your sort criteria.

And here’s what your report looks like now. Reminder: this is a report of salespeople, summarized (which means sorted) by state, and reordered by the count of salespeople per state. Since you specified descending order, states with the greatest number of salespeople appear first. Still, something’s not quite right, is it? If there were a different number of salespeople in every state, you would be perfectly happy, but given that there are five states with 10 salespeople, why on earth should Pennsylvania come first? And why is California fifth? You’d like each group of states to sort in ascending alphabetical order. But… but…

The unpleasant truth slowly dawns. You didn’t apply the Descending order to the summary field did you? In fact, such a thing is not possible. You can only apply criteria such as Ascending or Descending to fields in the sort order, and summary fields are excluded from the sort order — they simply aren’t eligible (note that s_count is greyed out in the above Sort Records dialog).

So when you “reorder” a summary report, you have to apply the sort criterion to a field in the sort order — in this case state. But it’s a Catch-22 situation, isn’t it? You must sort by state, but you must specify Descending to reorder the count properly. FileMaker is doing what you told it to do, but the result is not acceptable.

At this point it’s a good idea to step outside for a few minutes, take a walk, and get some fresh air. To make FileMaker do what you want, you’re going to have to trick it somehow. Fortunately, it’s not affectionately known as “WorkAround Pro” for nothing. There are many ways to pull this off, and as you walk along, one of them is about to occur to you. This is why you went outside: to give yourself some space for brainstorming, and one of the most important things you can do when brainstorming is to ask what if questions. For example…

What if you could sort by state in ascending order, but see the largest salespeople counts at the top? Hmmm… what if you used a negative summary count behind the scenes? After all, -10 comes before -9 in an ascending sort. Time to head back inside and see if that will work.

Back at the computer you wonder if your idea was so bright after all. Question: How the heck do you return a negative result with a summary count field? Answer: you don’t — but don’t worry, you can achieve the same objective by pointing a summary total field at a field containing -1.

To make this work, you need to define two new fields in your salespeople table, negative_one and s_total_negative, like this:

…and make sure that s_total_negative is a summary total, not a summary count field.

No changes will be necessary on the report layout, because you’re still going to display s_count, but you will need to tweak your sort and reorder criteria to take advantage of these new fields.

And by golly, you’ve succeeded in tricking FileMaker into doing your bidding. That walk really paid off.

Well, that about wraps it up. What’s that? You need to count the number of states because all 50 might not be present in the current found set?

You can read how to do that here:
Identifying Unique Records