Unique Records Revisited, part 1

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…

  1. Sort the records on a given field.
  2. 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.

One thought on “Unique Records Revisited, part 1

  1. malcolm

    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.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s