Optimal way to get Case.Id from Case.Thread_Id

Question

We've implemented a custom Apex Email Service to extend the standard Email-to-Case functionality, and one area we're struggling to do efficiently is figure out the CaseId when all that is known is the ThreadId:

E.g., [ ref:_00DC0PxQg._500C0KoOZS:ref ]

Currently, we are querying the EmailMessage object for subject's containing this same ThreadId, but that seems horribly inefficient. In fact Salesforce Support has reached out to us, stating:

While doing routine checkup,our R&D team has informed that running a SOQL that is consuming significant database resources resulting in slow response times from a user perspective.

However, after a number of emails, we are still without a solution (Ms. Cleo is MIA).

Inefficient Existing Code We'd Like to Improve:

private Id getCaseIdFromEmailSubject(String subject) {
  String queryWhere = '%' + this.emailThreadTaskReferenceId(subject) + '%';       
  List<EmailMessage> emails = [SELECT ParentId, Subject FROM EmailMessage WHERE Subject like :queryWhere];
  if(emails.size() > 0){
    //trap for IDs that are the same case insensitive, but not case sensitive.
    for (EmailMessage em : emails){
      //.equals() method is case sensitive whereas == is not case sensitive
      if (this.emailThreadTaskReferenceId(subject).equals(this.emailThreadTaskReferenceId(em.Subject))){
        return em.ParentId;
      }              
    }      
  }
  return null;
}

Are there any exposed system methods to determine a CaseId from a Thread_Id? I'd be curious to know how the standard Email-to-Case functionality achieves this, and if there is anything we can leverage.

I understand there is a newer field, Case.Thread_Id for use in email-templates, but this doesn't look to be available in SOQL, for our Email Service to be able to leverage it.

One way would be to add a field on the Case to store the Thread_Id, but we'd like to avoid needing to dirty the data model, and wasting an ExternalId (since I'd presume formula fields wouldn't add a whole lot of value, since they cannot be indexed and our Case table is ~ 4 million rows.)

There is a post on StackOverflow about this, but as it's a year old, I'm hoping maybe things have changed. The regex in the solution isn't reliable, as some cases are preceded with an underscore, while others are not (in an org with ~4 million cases). We'd obviously like a solution that doesn't involve maintaining logic branches in code as our org continues to scale. :)

http://stackoverflow.com/questions/6826843/salesforce-api-how-to-identify-a-case-from-an-email-reference-code-ref

tl;dr:

We know the CaseThreadId = [ ref:_00DC0PxQg._500C0KoOZS:ref ]
We need to know CaseId = 500C000000KoOZS

Answer 1

These are the helper functions I created to extract the Case Id from a Thread Id.

1) Break up the Thread Id to get the second part: (This can be done far better and neater but works for what I need)

  public static String ParseThreadId (String text){

      String[] result = text.split('ref\\:[0-9a-zA-Z]+\\.');

      if(result.size() >= 2){
        result = result[1].split('\\:ref');

        if(!result.isEmpty()){
          return result[0];
        }
      }  

      return '';  
  }

2) Pass the result of the previous function into this one to expand out the Case Id. When stored in a Thread Id all the '0' padding characters are removed. To get the Case Id you need to expand it back out:

  public static String ThreadToCaseId (String thread){

    if(thread.length() >= 5){ //If the string isnt at least 5 long it cant be a thread id
        String caseid = thread.substring(0,4);

        for(Integer  i = thread.length(); i < 15; i++){
          caseid += '0';
        }

        caseid += thread.substring(4);

      return caseid;
    }else{
      return '';
    }
  }

Answer 2

Just looking at this from the perspective that you have the CaseThreadId string

ref:_00DC0PxQg._500C0KoOZS:ref

and you want to resolve this to a Case ID:

500C000000KoOZS.

The CaseThreadId in the example you provided appears to contain an abbreviated CaseId in the form:

500C0KoOZS.

You could safely trim off the Case keyprefix of 500.

Ignoring the 'C' in the example for the time being, you are now left with a base-62 encoded number that you could pad out to get the original Id.

What isn't clear is:

• why there is some sort of large offset in the Case Id (the 'C' immediately following the key prefix)?
• what happens when the Case ID gets significantly larger? It may be the Id is just being truncated rather than shortened. So it would be impossible to uniquely reconstruct the Case Id.

Answer 3

Per the Spring '13, Release Notes this is being added to the standard library. A new Static method, getCaseIdFromEmailThreadId, has been added to the Cases class.

public class GetCaseIdController {

  public static void getCaseIdSample() {
    // Get email thread ID
    String emailThreadId = '_00Dxx1gEW._500xxYktg';
    // Call Apex method to retrieve case ID from email thread ID
    ID caseId = Cases.getCaseIdFromEmailThreadId(emailThreadId);
  }
}

The emailThreadId argument should have the following format: _00Dxx1gEW._500xxYktg. Other formats, such as ref:_00Dxx1gEW._500xxYktl:ref and [ref:_00Dxx1gEW._500xxYktl:ref], are invalid.