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