Tag Archives | Report

Magic Key, part 1

Posted on July 31, 2011 by

Today we’re going to look at a technique called Magic Key, which allows you to flexibly create and populate related records. I believe that the name originates with European developer Ugo Di Luca, and we’ll get to the details in a moment, but first let’s look at a scenario where it might come in handy. Have you ever built or inherited a system with data stored in text fields formatted as check box sets?

There are pros and cons to this approach, and one of the cons is that data stored in this manner can be a pain to report on. But I have good news: Magic Key can help alleviate that pain Continue Reading →

Reorder Based on Summary Field

Posted on December 18, 2010 by

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.

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 →

Ranking Entries in a Summary Report

Posted on November 27, 2010 by

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.

Now it turns out that ranking the sales people isn’t quite as easy as you might expect. You might be tempted to try this:

But it doesn’t work, because @@ indicates the actual record number, and since we’re summarizing multiple records, we end up with:

…which is no good at all. So, we’re going to have to remove the kid gloves, roll up our sleeves, and show FileMaker who’s boss. And believe it or not, to make this happen, we’re going to use the flag_unique field that we discussed last time. Let’s take a look at our raw sales data. Here are just a few records, sorted by salesperson.

If only we could produce a running total of flag_unique… hmm… well, why not, let’s give it a try. What happens if we define a summary field, s_running_count_of_flag unique, like this?

Now what does our data look like? You know what? That’s going to work.

And here’s what our report looks like.

Okay, that’s all well and good, but what if we want to group first by zone, and then rank the salespeople within each zone? We can design a new report, summarized first by zone, and then by salesperson…

… and we can sort it first by zone and then by salesperson, but will our salesperson ranking do what we want?

Not yet. Susan should be #1, not #7; we need the ranking to restart for each zone. Fortunately, there is a way, and it doesn’t require defining a new field… we can just tweak the one we already have. Let’s revisit the definition of s_running_count_of_flag unique, and this time we’re going to check the “restart summary for each sorted group” box.

When that box has been checked, we then need to specify zone as the sort field (this is also sometimes referred to as the “break” field).

And while we’re mucking about, there’s something else about this new report that could use some tweaking. Take a look at the Zone column… we don’t really need to see the zone name repeated over and over again, do we? Let’s go into layout mode and apply some conditional formatting to the zone field.

What we’re saying is: only show the zone name if we’re on the first salesperson record of a given group; otherwise make the text color white (i.e., invisible). And here’s our final report, sorted by zone and by salesperson, with rankings correctly restarting for each zone.

Darn that looks nice, if I do say so myself. Believe it or not, there are some other cool tricks we can do with flag_unique, but we’re going to save those for another day.

s_running_count_of_flag uniques_running_count_of_flag unique

Identifying Unique Records

Posted on November 26, 2010 by

[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…


…but how can I show a count of my sales people?”

5-28-2013 11-59-28 AMThere are various ways to solve this problem, and it’s a problem worth solving, because once you have this technique under your belt, you will find plenty of uses for it, above and beyond simply counting group members. I’m going to focus on what I believe is the simplest solution. I first saw this solution in Ray Cologon’s FileMaker 10 Bible, and more recently was reminded of it in a posting by Mikhail Edoshin on the FM Experts list.

One of the reasons I like this solution, is that it doesn’t rely on knowing anything at all about the found set of records. It works with any found set, and as long as that found set is sorted, it works. And at the risk of stating the obvious, the real challenge here is to identify the unique records, right? Because once they’ve been indentified, or as many developers like to say, “flagged”, counting them will be child’s play. We’ll just point a summary field at the flag field, and voila, problem solved.

So, we’re going to define new field, “flag_unique”, which will show a 1 for the first instance of a given saleperson; otherwise it will show nothing.

Incidentally, some developers feel strongly that there should be a zero rather than nothing for the “false” case. Generally speaking if I only care about the “true” case, I use nothing (i.e., "") as the false case. First, it makes it easier to see the data I care about, and second, when it comes time to aggregate these values, I can use a summary count field to accomplish this. Otherwise (if I were using zeros and ones), I would have to use a summary total field. Like many things in the FileMaker world, it ultimately comes down to personal preference.

Here is the definition of flag_unique, which is an unstored calculation field with a number result:

Let ( x = GetNthRecord ( salesperson ; Get(RecordNumber) - 1 ) ;

If ( salesperson <> x ; 1 ; "" )

)   //   end let

In a nutshell, this calc compares the salesperson in the current record with the  corresponding value in the previous record, and returns a 1 if they are different. This works because we’ve sorted on the salesperson field.

The only remaining task is to define a summary field as the count of flag_unique, and we can now determine how many sales people there are in our found set.

And above, highlighted in a shade of green a certain generation of reader will associate with Mr. Yuk, is our salesperson count. We can also use the salesperson count to help determine the number at the very bottom of the report: the average total per salesperson.

Common sense says it’s…

total sales / number of salespeopole

…and since we’ve already got summary fields containing both those values, we can simply define a calculated number field, average_total_per_salesperson as:

s_amount / s_running_count_of_flag_unique

Incidentally, the values in yellow a) require a bit of work to generate and b) build on the technique we’ve been discussing. They will be addressed in a subsequent posting.

I said earlier that this report was sorted by salesperson, yet when you look at the above example, you may notice that the report has been “re-ordered” by descending total sales. On the off chance you’re not familiar with how this is done, here are the sort settings.


For simplicity’s sake, this example has used the salesperson’s first name. In a real world situation, you might want to see the data sorted by the salesperson’s last name and first name,  and in a large organization, you might even have more than one salesperson with the same name, so you’d want to sort by:

name_last
name_first
id_salesperson

…and the definition of flag_unique would instead be:

Let ( x = GetNthRecord ( id_salesperson ; Get(RecordNumber) - 1 ) ;

If ( id_salesperson <> x ; 1 ; "" )

)   //   end let

Of course in the case of multiple salespeople with the same name, you’d also want to include some other information in your report to differentiate between them.

Note: the material discussed in this post applies to FileMaker 8 and later. However, the demo file requires FM 10 or later, since it is designed to display summary reports in browse mode.