A READER ASKS: Duplicate Flagging Sans FileMaker Scripting

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

A READER ASKS
I have a simple database of email reminders -- web-submitted email addresses of people who want to receive email reminders for particular events.  I can easily filter the list of email addresses based on events, but how to filter for duplicates?

For example:  10 people signed up for a reminder for Event A, but someone submitted the form twice, so the portal for Event A displays 11 email-address records, whcih is technically correct, but I'd like to see only the 10 *unique* email address records.  Is there a clever way to do this?

I can write a script to go through all the email-address records and either mark or delete records when there are multiple Event A  records with matching email addresses, but I sense there's a more elegant way of doing it, through a relationship perhaps?

Any tips/insights greatly appreciated!

-------
DWAYNE RESPONDS
There are a number of ways to go about this and I’ll share with you the method I’d try first. I say this because there are many times when you start to code something and then you think of a more direct path.

FLAGGING DUPLICATES VIA A CALCULATED VALUE
Here is a link to an example file that shows how you can can setup a calculation field to detect duplicate records via a relationship setting.  (click here)

OMIT THE DUPLICATES VIA A SCRIPT
There are a number of script related avenues that you can use to sort a found set and omit duplicates. This may be the way to go and you can attach that method as a subscript in the overall script you use to send out the emails, export records or print reports.

DUPLICATES WITHIN A FOUND SET VIA A CALCULATED VALUE
This would get trickier. I did a quick search for custom functions on the popular briandunning.com site. The first search for “duplicate set” didn’t find any records with those key words. A search for duplicate didn’t find what I was looking for either. A search for found set found two functions that mention they can pull values from a found set.

You would probably have to script this but you could possibly setup a multiple line key field relationship that could flag duplicates within a found set. It may be a bit slow but I think it is possible.

Here is a link (click here) to a posting on multiple line key/match fields.