How to recognize duplicate records and other criteria within a Data Extension

Question

Currently the AMPscript based landing page features an indication that shows how many individuals are signed up for an event. This number is arrived at simply by ROWCOUNT to see how many fields in the extension match a unique ID for the event. I'd like to have that rowcount screen out duplicate records to display only unique records (by virtue of a matching email address). We are using a data extension which tracks email subscribers for an event listing. We're ok with the system allowing a duplicate record as that eases development and testing and is not critical at this time. I think at some point we will screen for duplicates as they upload but for now, just a record count that notes not to count rows if the email matches one already counted. This will allow reviewers to know that (for example) of the 140 registrants 128 are unique (the others appear to be duplicates) for instances where perhaps a screen/data lag had someone click on a register page twice or more. I think knowing this will also help review of lists to screen for other details and create other sub-rowcounts more accurately.

Answer 1

There is not an AMPscript function that will do this currently. Similar functionality could be created with SSJS using arrays. You could store all of the data in an array, remove duplicates, and then display the lengths. I do not recommend this approach.

One easy option would be to make EmailAddress and EventID your primary keys in the event registration data extension. This means that your primary key is the combination of these 2 values, which means that one email address can be signed up for multiple events, but not the same event twice. Then, instead of using an Insert function to add people to the data extension you would use an Upsert function to update the row if someone re-registers.