A while back I posted a technique to identify and count unique records. I recently discovered that while the technique is 100% reliable in terms of identifying unique records, under certain circumstances it will fail to count them correctly, due to what I believe is a bug in the way summary fields work. You can easily reproduce the bug by following the instructions in this demo file: broken summary field
Fortunately there are various ways to skin this particular cat, and today we’re going to look at one of them (demo file: broken summary field workaround).
The method for indentifying unique records hasn’t changed. You can read about it in detail here, but to briefly recap, you…
- Sort the records on a given field.
- For each record, use GetNthRecord to compare that field with the same field in the preceding record… if they don’t match, then display a 1 in the flagUnique field.
So what has changed in the work around? Just a couple things… first off, the sort order now includes a “constant” field (a calculated number 1).
The other change is the addition of a unstored calculated number field, countUniqueWorkaround, defined thus:
GetSummary ( countUnique ; constant )
We haven’t gotten rid of the original summary field (countUnique), but now it operates behind the scenes. It seems strange that a calculation using GetSummary is more reliable than the underlying summary field it is based on, but in the end it’s the result that counts, and as long as the sort order is correct, this technique will reliably count unique entries.
Next time we’ll look at a completely different method to count unique entries for a given found set — one that does not require that the records be sorted.
Apparently a calculation using GetSummary is more reliable than the summary field itself! I'm guessing it's because it forces the summary field to wake up and smell the coffee.
To see the summary field break down it is only necessary to unsort any record set and then to sort it again.
Unsorting the record set triggers a re-evalutation of the summary field.
Changing the record set triggers a re-evalutation of the summary field. If the record set is sorted FM11 will respect the sort order, so the count unique formula is performed “correctly”
That seems to be the key to this problem. Unsorting the record set does trigger a re-evaluation Sorting the record set does not.