count number of sub-summary breaks
I'm experienced with DBMS and SQL, but new to FMP 10 (Mac OSX 10.5). I want to learn the best FMP idiom for counting sub-summary breaks. For an easily visualized example, consider a national sales database with the need to list sales, summarize by state (the break) and then to produce a count of unique states. The trick is that the report may be for a selection of sales - e.g. sales > $200, or sales of a particular product.
I have seen people on this forum, and elsewhere, give 3 different solutions for the count of states. Only one of which is half-way reasonable, although still awkward. These 3 methods are: scripted loop to count states, summing a unique state flag via self join, and summing the reciprocal of the count within each group (state). If you know what I'm talking about, you can just tell everyone now the cool fourth technique I overlooked.
I trust nothing more need be said about scripted looping.
For those who don't immediately see the problem with the self-join, consider the case of using selected data. The join won't always have a match, in the selected data, to the top of the join list. So the "unique" flag will not be set for any sale in one or more state groups. There are some ways to handle this for some selections - for instance by sorting the join - but that's not universally useful. I suppose you could create a custom join field(s), but this would require knowing all the ways in which the data could be selected. Even if you could magically copy the last search parameters into a custom join, that wouldn't cover random "Omit Record"s. Am I overlooking something that would allow the use of a self-join to flag one, and only one, sale in each state?
So that leaves the reciprocal count technique. This puts a data field on each sales record equal to the reciprocal of the count of sales in the group (state). The sum of that field for each state is always 1, and the sum over the complete data selection is the number of states. It works and it's fast. But it seems like just such a kludge. Isn't there a more elegant way? A relational way?