[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?”
There 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.