I’VE NEVER HAD ANYONE ASK FOR THIS BEFORE, but there’s always a first time. Still, this is a pretty interesting example of solving a problem multiple ways and finding the fastest way to do so, something Kevin Franks does very well:
Recently there was a question about cleaning up a found set on one of the FileMaker discussion forums. When a question of this nature arises, it’s typically some variation on “How can I remove [or delete] duplicate entries?” But this was the opposite: For a given found set of customers, how can I omit those whose Zip codes only appear once in the found set?
In other words, keep the records whose Zips appear multiple times and banish the others.
Note that the challenge was starting from a subset of records. If the challenge had been for all records in the table, one could simply search on ! (find duplicate values) in the Zip code field. However, this trick won’t work when starting from a found set rather than all records. And constrain won’t help here because it doesn’t play nicely with the ! operator.
Off the top of my head, I suggested…
Sort by Zip code, then loop through the found set from top to bottom… using GetNthRecord() test the current record’s Zip code against the previous record and also against the next record. If both tests are negative, omit, otherwise go to next record (and of course exit after last).
Franks then proceeds to show four ways to anti-dedupe, of course. Check it out and be sure to download the example file. The comments are interesting, too.
FM 13: Anti-deduping, part 1 | FileMakerHacks.