function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
cmolivercmoliver 

Trigger - access ThreadID in SOQL

Hi there --

I'm setting up a trigger to automatically generate an autoresponse e-mail when a case is opened through our website (which requires a secure login).

I have everything working as I want it, except that I can't seem to access the Case.ThreadID that we use for our e-mail templates from within the trigger. I've searched documentation, but can't find out how to refer to it in SOQL land. We need it for when customers reply to the auto-response - otherwise, a new case is created, which we want to avoid.

Help?


Here's the code:

trigger SendAutoResponse on Case (after insert) {

// Iterate over each sObject
for (Case a : Trigger.new) { }

String CaseId = [Select Id, ParentId, SuppliedName, CaseNumber, UID__c, AID__c, Description FROM Case WHERE Id IN :Trigger.new].Id;
String ContactID = [Select Id, ContactID FROM Case WHERE Id IN :Trigger.new].ContactID;


// Create a new single email message object
// that will send out a single email to the addresses in the To, CC & BCC list.
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

// Specify the address used when the recipients reply to the email.
mail.setReplyTo('us@ourdomain.com');
// Specify the name used as the display name.
mail.setSenderDisplayName('Our Support');

// Who do I send the e-mail to?
mail.setTargetObjectId(ContactID);

// Specify the text content of the email.
mail.setTemplateId('00X50000000phlB');
mail.setWhatID(CaseID);

// Send the email you have created.
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

// Saves as an acivity.
mail.setSaveAsActivity(true);

}



Many thanks -
Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

Martin,

 

Regrettably, your formula won't work for really old cases (those with less than five significant characters in their ID) or cases in the future (once ID values roll past five significant characters). Also, not all organization ID values have four sigificant ID characters (for example, my organization has only three significant characters, because it was created in 2005).

 

Instead, this would be a more appropriate formula:

 

{! 'ref:' &  LEFT($Organization.Id,4) & IF(MID($Organization.Id,5,1)='0', IF(MID($Organization.Id,6,1)='0', IF(MID($Organization.Id,7,1)='0', IF(MID($Organization.Id,8,1)='0', IF(MID($Organization.Id,9,1)='0', IF(MID($Organization.Id,10,1)='0', IF(MID($Organization.Id,11,1)='0', IF(MID($Organization.Id,12,1)='0', IF(MID($Organization.Id,13,1)='0', IF(MID($Organization.Id,14,1)='0', MID($Organization.Id,15,1),MID($Organization.Id,14,2)), MID($Organization.Id,13,3)), MID($Organization.Id,12,4)), MID($Organization.Id,11,5)), MID($Organization.Id,10,6)), MID($Organization.Id,9,7)), MID($Organization.Id,8,8)), MID($Organization.Id,7,9)), MID($Organization.Id,6,10)), MID($Organization.Id,5,11)) & '.' & LEFT(Id,4) & IF(MID(Id,5,1)='0', IF(MID(Id,6,1)='0', IF(MID(Id,7,1)='0', IF(MID(Id,8,1)='0', IF(MID(Id,9,1)='0', IF(MID(Id,10,1)='0', IF(MID(Id,11,1)='0', IF(MID(Id,12,1)='0', IF(MID(Id,13,1)='0', IF(MID(Id,14,1)='0', MID(Id,15,1),MID(Id,14,2)), MID(Id,13,3)), MID(Id,12,4)), MID(Id,11,5)), MID(Id,10,6)), MID(Id,9,7)), MID(Id,8,8)), MID(Id,7,9)), MID(Id,6,10)), MID(Id,5,11)) & ':ref' }

This particular version is 18-character agnostic, and will work for all organizations unless Force.com ever changes to a different ID scheme.

All Answers

mikefmikef
cmoliver:

a couple things here.

In your trigger you are querying for the contactId and the Case Id when they should be in the trigger.
Plus the queries you are doing are inside a for loop and will cause Apex exceptions if you bulk load Cases.

Try this;
Code:
String CaseId;
String ContactId:

for (Case a : Trigger.new) {
   CaseId = a.Id;
   ContactId = a.ContactId;

   //add your email sending code in the for loop.
}

 And what is the ThreadID?

cmolivercmoliver
Hi Mike -

Thanks for taking the time to read and respond.

The Thread ID is used on e-mails generated by the case to associate any further correspondence to the same case. It's identified in the e-mail templates as {!Case.Thread_Id}.

It's pretty pivotal to us so that replies to the autoresponse e-mail do not generate a new case since we have EmailToCase set up.

Many thanks -
mikefmikef
OK the {!Case.Thread_Id} is not a field, but a system generated text string.

To get it you have to search the email under that case for the string, and parse it out of the latest email.
cmolivercmoliver
Well, we're actually trying to include it as we send the first e-mail, so there's nothing yet to parse.

Any suggestions? If we set it up under an Autoresponder with a template, this Just Works.
sfdcfoxsfdcfox

Why not just create a template and then use that template in your code? That being said, the reference can be created in Apex as follows:

Code:
// c = the case.
String orgId = UserInfo.getOrganizationId();
String caseId = c.id;
String threadId = 'ref:'+orgId.substring(0,4)+
   orgId.substring(orgId.lastIndexOf('0')+1)+'.'+
   caseId.substring(0,4)+
   caseId.substring(caseId.lastIndexOf('0')+1)+':ref';


 

You can reuse the orgId for each threadId you wish to generate, of course, and you can easily generate the concatenated case ID values using the formula above, even in a loop. Of course, be bulk-safe and you'll have some code working!

NOTE: You're also using the Mass Emails for the organization by using this method. I recommend you use a workflow rule instead.

EDIT: I found the organization under UserInfo. How silly to think it would be under System.



Message Edited by sfdcfox on 06-10-2008 08:58 AM
cmolivercmoliver
Hi sfdcfox --

Thanks for helping!

I am using a template - I'm referencing the Thread ID in the template, but no value gets filled in. If I use the exact same template using an AutoResponse rule instead of a trigger, the Thread ID fills in with the appropriate value.

You see the frustration. :)
joshDDUPjoshDDUP
Did anyone post this to the idaeExchange? I'd like to get the Thread_ID via the API/SOQL for outbound email in "external code" (like PHP, RUBY etc...)
RpeeRpee

You can build this with a formula field: 

 

"ref:"&LEFT( $Organization.Id , 4 )&RIGHT( $Organization.Id , 4 )&"."&LEFT( Id , 4 )&RIGHT( Id , 5 )&":ref"

Martin_DeloitteMartin_Deloitte

If you are in a Visualforce page, then your Id might be composed of 18 characters, then the formula becomes:

{!'ref:'&LEFT($Organization.Id,4)&LEFT(RIGHT($Organization.Id,7),4)&'.'&LEFT(Id,4)&LEFT(RIGHT(Id,8),5)&':ref'}

 

If your id is 15 characters long (like  in formula fields for instance), the formula remains:

{!'ref:'&LEFT( $Organization.Id , 4 )&RIGHT( $Organization.Id , 4 )&'.'&LEFT( Id , 4 )&RIGHT( Id , 5 )&':ref'}

 

sfdcfoxsfdcfox

Martin,

 

Regrettably, your formula won't work for really old cases (those with less than five significant characters in their ID) or cases in the future (once ID values roll past five significant characters). Also, not all organization ID values have four sigificant ID characters (for example, my organization has only three significant characters, because it was created in 2005).

 

Instead, this would be a more appropriate formula:

 

{! 'ref:' &  LEFT($Organization.Id,4) & IF(MID($Organization.Id,5,1)='0', IF(MID($Organization.Id,6,1)='0', IF(MID($Organization.Id,7,1)='0', IF(MID($Organization.Id,8,1)='0', IF(MID($Organization.Id,9,1)='0', IF(MID($Organization.Id,10,1)='0', IF(MID($Organization.Id,11,1)='0', IF(MID($Organization.Id,12,1)='0', IF(MID($Organization.Id,13,1)='0', IF(MID($Organization.Id,14,1)='0', MID($Organization.Id,15,1),MID($Organization.Id,14,2)), MID($Organization.Id,13,3)), MID($Organization.Id,12,4)), MID($Organization.Id,11,5)), MID($Organization.Id,10,6)), MID($Organization.Id,9,7)), MID($Organization.Id,8,8)), MID($Organization.Id,7,9)), MID($Organization.Id,6,10)), MID($Organization.Id,5,11)) & '.' & LEFT(Id,4) & IF(MID(Id,5,1)='0', IF(MID(Id,6,1)='0', IF(MID(Id,7,1)='0', IF(MID(Id,8,1)='0', IF(MID(Id,9,1)='0', IF(MID(Id,10,1)='0', IF(MID(Id,11,1)='0', IF(MID(Id,12,1)='0', IF(MID(Id,13,1)='0', IF(MID(Id,14,1)='0', MID(Id,15,1),MID(Id,14,2)), MID(Id,13,3)), MID(Id,12,4)), MID(Id,11,5)), MID(Id,10,6)), MID(Id,9,7)), MID(Id,8,8)), MID(Id,7,9)), MID(Id,6,10)), MID(Id,5,11)) & ':ref' }

This particular version is 18-character agnostic, and will work for all organizations unless Force.com ever changes to a different ID scheme.

This was selected as the best answer
jpucketyjpuckety

 

Does anyone have this formula as Apex code?  

sfdcfoxsfdcfox

It wouldn't be a formula, it'd be a function:

 

public class orgThread {
public static string makeThreadId(Id caseId, Id orgId) {
  return
    'ref:'+
    (
      (((string)orgId).substring(0,((string)orgId).substring(3,4)=='0'?3:4))+
      (((string)orgId).substring(4+((string)orgId).substring(4).indexOfAnyBut('0'),15))
    )+'.'+(
      (((string)caseId).subString(0,((string)caseId).substring(3,4)=='0'?3:4))+
      (((string)caseId).substring(4+((string)caseId).substring(4).indexOfAnyBut('0'),15))
    )
    +':ref';
}
}

Usage:

 

String threadID = orgThread.makeThread(someCase.Id, UserInfo.getOrganizationId());

 

jpucketyjpuckety
That gets us close, but still not quite. That function is missing the underscores, so I added them. Even still it's still a little off.



12:05:38.754 (1754551000)|SLA_PROCESS_CASE|500e0000001mWgt
12:05:38.754 (1754819000)|USER_DEBUG|[59]|DEBUG|case: Case:{Description=Test Case, Subject=Email2CaseByServiceProviderTest, Id=500e0000001mWgtAAE}
12:05:38.754 (1754875000)|USER_DEBUG|[60]|DEBUG|caseId: 500e0000001mWgtAAE
12:05:38.754 (1754897000)|METHOD_ENTRY|[62]|01pi0000001w2Pu|Email2CaseByServiceProviderTest.makeThreadId(Case)
12:05:38.755 (1755026000)|USER_DEBUG|[290]|DEBUG|orgId: 00De0000001JvgXEAS
12:05:38.755 (1755056000)|USER_DEBUG|[294]|DEBUG|caseId: 500e0000001mWgtAAE
12:05:38.755 (1755199000)|USER_DEBUG|[302]|DEBUG|threadId: ref:_00De1JvgX._500e1mWgt:ref
12:05:38.755 (1755809000)|EXCEPTION_THROWN|[74]|System.AssertException: Assertion Failed: Expected: 500e0000001mWgtAAE, Actual: 500e1000000mWgtAAE




-Jpuckety
sfdcfoxsfdcfox
public class orgThread {
public static string makeThreadId(Id caseId, Id orgId) {
  return
    'ref:_'+
    (
      (((string)orgId).substring(0,((string)orgId).substring(3,4)=='0'?3:4))+
      (((string)orgId).substring(4+((string)orgId).substring(4).indexOfAnyBut('0')-1,15))
    )+'._'+(
      (((string)caseId).subString(0,((string)caseId).substring(3,4)=='0'?3:4))+
      (((string)caseId).substring(4+((string)caseId).substring(4).indexOfAnyBut('0')-1,15))
    )
    +':ref';
}
}

They now include an extra zero (probably as a compressed delimiter). Try the following code, it should work for all except the largest case ID values (which salesforce.com is nowhere near utilitizing).