Level: Intermediate, Version: FM 12 or later

Successfully Find Duplicate Values Within A Set Of Records

The need to isolate duplicates within an existing found set has often been a source of frustration for developers. A couple weeks ago we examined a technique by Ralph Learmont demonstrating that, contrary to popular belief and experience, the ! (find duplicates) operator can, under certain conditions, be coerced into isolating duplicate values within a found set.

4-24-2015 3-24-21 PM

Here’s Ralph’s original statement of the problem:

There’s a quirk in the way Filemaker deals with duplicate records. This makes it difficult to find duplicate records WITHIN a found set of records. If you try to constrain the Find to the current set of records, you might discover extra spurious records appearing. These records have “partner-duplicates” outside of the found set. These unwanted spurious records will appear as single occurrences when you inspect a sorted column. Technically they are duplicates. It’s just that their partners lie “outside”, hidden in the omitted slab of records.

And why it’s a cause for concern:

I think it’s important to explain why finding duplicates in a found set is something that often appears to work… and it won’t be apparent there actually IS AN ISSUE unless one goes to the trouble of actually sorting results and checking for those “single-occurrences”.

While the technique was blazingly fast, it seemed to me there was something a bit voodoo-like about Ralph’s previous demo, as well as my attempt at simplification. Apparently Ralph wasn’t satisfied with it either, because he has provided a new demo (Find Duplicates in Found Set) showcasing a more straight-forward technique, and kindly agreed to share some of his thoughts about it as well. Continue reading “Successfully Find Duplicate Values Within A Set Of Records”

Level: Intermediate

FM 13: Anti-deduping, part 3

Today we have two great examples of using demos from this site as a starting point and making significant improvements: one from Malcolm Fitzgerald in response to FM 13: Anti-deduping, part 1, and one from Ralph Learmont in response to FM 13: Anti-deduping, part 2.

As you may recall the challenge was to retain duplicates and omit unique entries from within an existing found set, as opposed to starting from all records… otherwise we could have just searched on ! (find all duplicates), but since the ! operator does not play nicely with constrain, it was apparently not an option for this particular challenge.

Or so I thought. These gentlemen convincingly prove otherwise — and like all great techniques, what you’re about to see has the potential to be useful in a variety of situations, not just the narrow confines of this particular challenge.

Malcolm Fitzgerald – Example #1

4-25-2015 5-06-32 PM

Continue reading “FM 13: Anti-deduping, part 3”