Email2Case - Create new case on replies after X amount of days

Question

Use Case:

I currently have a workflow to re-open tickets after they're closed, but I've found this is causing some reporting anomolies as some customers are reopening cases that can be weeks old. What I want is to keep the current workflow but only if it's been less than a week. If it's been over a week, I want a new case created.

I found this post, https://success.salesforce.com/answers?id=90630000000hq2mAAA. But I don't feel I should have to pay $1500 for something that is a basic function in every other helpdesk software. We moved from Desk.com which had this functionality baked in.

Can I do this with Flow or Process Builder? If it requires an apex trigger, are there any exmaples that you know of?

Answer 1

I can tell you how we do this.

We have a custom emailHandler that processes emails as they come in. Our email handler checks to see if the email relates to a closed case and re-opens it. It would be trivial to update it to look at the date and create a new case if needed. This is however, a completely APEX solution. If you do not have an existing implementation, you will have to make yours from scratch. A quick Google search will show you how to implement a custom email handler: http://sforcehacks.blogspot.com/2012/01/email-to-case-custom-email-handler.html

Alternatively, if you want to try going the route of using workflows, you can start by creating a few fields on case:

Date Closed

I thought this field was standard but I just checked and I couldn't access it in a formula (might be wrong) so start by creating this field.

Then, create a workflow that populates this field when the "isClosed" is changed from false to true.

You may also want to create a workflow to clear this field if isClosed changes from true back to false.

Regardless of the solution you choose, you will need a field that tracks this date.

Week Old

This should be a formula field:

• Type: Checkbox
• Date_Closed__c < (Now() - 7)

Note, This seems to work in my tests but I'm not sure if the 7 is days, minutes, hours, etc. You may have to do some math to convert this from days to another measurement to ensure it's accuracy

Once you have those fields, all you have to do is check if week old is not true in your workflow. If it is true, your workflow shouldn't fire.

Trigger

You can do this in a trigger as the standard email handler inserts the EmailMessage object and associates it to the case. So your trigger would be on the EmailMessage object. If you create the new fields I mentioned, you can easily check those fields to determine what action to take. In terms of what that actually looks like in code:

trigger EmailMessageTriggerExample on EmailMessage (before insert) {
    if (trigger.isAfter && trigger.isInsert) {
        Set<Id> cases = new Set<Id>();

        for (EmailMessage em : trigger.New) {
            cases.add(em.ParentId);
        }

        for (Case c : [SELECT Week_Old__c, Status FROM Case WHERE Id IN :cases]) {
            if (!c.Week_Old__c) {
                // Update existing case
            } else {
                // Create new case
            }
        }
    }
}