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