How do I catch duplicate cases created by Email-to-Case on demand?

Question

I am trying to implement a simple Email-to-Case (On Demand) workflow for my organization. One of the concerns of our Operations Managers is that our clients tend to send their "urgent" issues to several of our different e-mail addresses that they seem to have learned over the years, because they figure whoever sees it first will take care of them. I know, I know, it's our fault in the first place for giving them multiple points of contact. Our Ops Team somehow has 14 different e-mail addresses clients could potentially contact them from, which is silly, but what's done is done. I digress...

Naturally, the problem is that each of the addresses will be connected to the On-Demand Email-to-Case, and I have confirmed that when you send a single email to several of these addresses, it creates multiple cases with their own Case and Thread IDs. I figured "no problem, I'll just write a trigger to delete the duplicate cases", but as usual, nothing is easy with Salesforce. Here's what I intend my logic to be:

When the case is created, pull all EmailMessages that have the same Subject as the Case (which should be the same string as the Subject of the email) and were recieved in the last 30 seconds.
If it finds more than 1, delete this case.

This is the code that is supposed to accomplish that:

trigger DeleteCaseIfDuplicate on Case (after insert) {
    for (Case c : Trigger.new) {
        if (c.Origin == 'Email') {
            DateTime createdminus = c.CreatedDate.AddSeconds(-30);
            List<EmailMessage> messages = new List<EmailMessage>();
            messages = [SELECT Id, ParentId, RelatedToId, Subject, FromAddress FROM EmailMessage WHERE (Subject = :c.Subject AND MessageDate > :createdminus)];
            if (messages.size() > 1) {
                delete c;
            }
        }
    }
}

It seems super simple, which is what I was going for, but when I try to trigger this code, I get an 'Attempt to de-reference a null object' error. Normally, I could figure this out, but it's telling me it's encountering an error on line 0, so I don't even know where to start looking. I have tried both triggering before and after insert.

Perhaps I'm betraying my inexperience here, but what's wrong with this code? Can I intercept the EmailMessage itself before it creates a Case and tell it to delete itself? Is there a better way to do this? Are there best practices here and if so, what are they?

Reverb Reverb · Accepted Answer · 2021-02-02T19:32:15

@adam

I tried the script on my sandbox with email to case enabled. I modified it on some places and works for me -> emailed two times a email with the same subject and the other case is deleted.

  trigger DeleteCaseIfDuplicate on Case (after insert) {
            for (Case c : Trigger.new) {
    // added check c.origin is not null
                if (c.Origin <> null && c.Origin == 'Email') {
                    DateTime createdminus = c.CreatedDate.AddSeconds(-30);
                    List<EmailMessage> messages = new List<EmailMessage>();
                    messages = [SELECT Id, ParentId, RelatedToId, Subject, FromAddress FROM EmailMessage WHERE (Subject = :c.Subject AND MessageDate > :createdminus)];
                        // if you use > 1, two subject duplicates are allowed
                        if (messages.size() > 0) {
                        delete c;
                    }
                }
            }
     }

How do I catch duplicate cases created by Email-to-Case on demand?

1 Answers