Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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 '';
    }
  }
share|improve this answer
    
Thanks, Jon. It doesn't look like this handles the RefIds that contain underscores. While we could branch out this logic out, the scary part is when don't know how/when these RefId formats tend to change, and the format they will follow (e.g., could there be two underscores in the future??). –  Mikey Aug 22 '12 at 14:56
    
Im not entirely sure where the pattern for RefIds is set but ours definitely doesn't use underscores [ ref:00DDCoMT.500DJbB2n:ref ]. My suggestion would be to strip the the '_' characters first –  Jon Hazan Aug 22 '12 at 15:07
    
Yeah. I think that's what we may have to do. It looks like the underscores first started appearing around ~4 million Cases. We'd just need to account for this, and pad with an additional 0. A little scary as we don't know the underlying algorithm. :) –  Mikey Aug 22 '12 at 15:43
up vote 2 down vote accepted

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.

share|improve this answer

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.
share|improve this answer
    
We will definitely have to look at the idea of simply padding the substring of the reference id. But I think you hit the nail on the head...the scary part is what happens when the Case ID continues to grow. The ThreadId is roughly Created by a combo of OrgId & CaseIds, but I'm not sure where the underscores come from. ThreadId = TRIM(" [ ref:" + LEFT( $Organization.Id, 4) + RIGHT($Organization.Id, 4) +"."+ LEFT( Id, 4) + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Id, RIGHT( Id, 4), ""), LEFT( Id, 4), ""), "0", "") + RIGHT( Id, 4) + ":ref ] ") –  Mikey Aug 20 '12 at 0:08
1  
@Mikey There appears to be more characters in your example Thread_Id than that formula allows for. It looks like the formula would need to be updated to take 5 characters in some cases. –  Daniel Ballinger Aug 20 '12 at 1:53
    
Yeah. I just don't happen to know where those thresholds lie. Stole that formula from previous StackOverflow post. –  Mikey Aug 20 '12 at 17:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.