+ Start a Discussion
KT@CiscoKT@Cisco 

Retrieving refId information for a case

We have a need to display refId information to the agent before he/she sends out an email for a given case.

 

I researched the SFDC schema and even queried the DB for various fields for each object - Case, EmailMessage, etc. but couldn't find a field corresponding to the refId value that gets inserted in each email.

 

Questions:

1. refId is an attribute of which object?

2. Is the refId information available programmatically/API? If so, which one?

 

Thanks in advance,

Komal

Best Answer chosen by Admin (Salesforce Developers) 
werewolfwerewolf

The refId is generated from the org ID and the case ID.  In email templates it's referred to as "Thread ID" but there's no actual field that corresponds to it unless you make one.  You can make a formula field as below to construct it (although I wrote that field before the introduction of REGEX, perhaps with REGEX it might be easier and shorter now):

 

 

LEFT( $Organization.Id , 4) & IF (MID ( $Organization.Id, 5, 1) <> "0", RIGHT($Organization.Id, 11), IF (MID ( $Organization.Id, 6, 1) <> "0", RIGHT($Organization.Id, 10), IF (MID ( $Organization.Id, 7, 1) <> "0", RIGHT($Organization.Id, 9), IF (MID ( $Organization.Id, 8, 1) <> "0", RIGHT($Organization.Id, 8), IF (MID ( $Organization.Id, 9, 1) <> "0", RIGHT($Organization.Id, 7), IF (MID ( $Organization.Id, 10, 1) <> "0", RIGHT($Organization.Id, 6), IF (MID ( $Organization.Id, 11, 1) <> "0", RIGHT($Organization.Id, 5), IF (MID ( $Organization.Id, 12, 1) <> "0", RIGHT($Organization.Id, 4), IF (MID ( $Organization.Id, 13, 1) <> "0", RIGHT($Organization.Id, 3), IF (MID ( $Organization.Id, 14, 1) <> "0", RIGHT($Organization.Id, 2), "") ) ) ) ) ) ) ) ) ) & "." & LEFT( Id, 4) & IF (MID ( Id, 5, 1) <> "0", RIGHT(Id, 11), IF (MID ( Id, 6, 1) <> "0", RIGHT(Id, 10), IF (MID ( Id, 7, 1) <> "0", RIGHT(Id, 9), IF (MID ( Id, 8, 1) <> "0", RIGHT(Id, 8), IF (MID ( Id, 9, 1) <> "0", RIGHT(Id, 7), IF (MID ( Id, 10, 1) <> "0", RIGHT(Id, 6), IF (MID ( Id, 11, 1) <> "0", RIGHT(Id, 5), IF (MID ( Id, 12, 1) <> "0", RIGHT(Id, 4), IF (MID ( Id, 13, 1) <> "0", RIGHT(Id, 3), IF (MID ( Id, 14, 1) <> "0", RIGHT(Id, 2), "") ) ) ) ) ) ) ) ) )

 

 

 

All Answers

werewolfwerewolf

The refId is generated from the org ID and the case ID.  In email templates it's referred to as "Thread ID" but there's no actual field that corresponds to it unless you make one.  You can make a formula field as below to construct it (although I wrote that field before the introduction of REGEX, perhaps with REGEX it might be easier and shorter now):

 

 

LEFT( $Organization.Id , 4) & IF (MID ( $Organization.Id, 5, 1) <> "0", RIGHT($Organization.Id, 11), IF (MID ( $Organization.Id, 6, 1) <> "0", RIGHT($Organization.Id, 10), IF (MID ( $Organization.Id, 7, 1) <> "0", RIGHT($Organization.Id, 9), IF (MID ( $Organization.Id, 8, 1) <> "0", RIGHT($Organization.Id, 8), IF (MID ( $Organization.Id, 9, 1) <> "0", RIGHT($Organization.Id, 7), IF (MID ( $Organization.Id, 10, 1) <> "0", RIGHT($Organization.Id, 6), IF (MID ( $Organization.Id, 11, 1) <> "0", RIGHT($Organization.Id, 5), IF (MID ( $Organization.Id, 12, 1) <> "0", RIGHT($Organization.Id, 4), IF (MID ( $Organization.Id, 13, 1) <> "0", RIGHT($Organization.Id, 3), IF (MID ( $Organization.Id, 14, 1) <> "0", RIGHT($Organization.Id, 2), "") ) ) ) ) ) ) ) ) ) & "." & LEFT( Id, 4) & IF (MID ( Id, 5, 1) <> "0", RIGHT(Id, 11), IF (MID ( Id, 6, 1) <> "0", RIGHT(Id, 10), IF (MID ( Id, 7, 1) <> "0", RIGHT(Id, 9), IF (MID ( Id, 8, 1) <> "0", RIGHT(Id, 8), IF (MID ( Id, 9, 1) <> "0", RIGHT(Id, 7), IF (MID ( Id, 10, 1) <> "0", RIGHT(Id, 6), IF (MID ( Id, 11, 1) <> "0", RIGHT(Id, 5), IF (MID ( Id, 12, 1) <> "0", RIGHT(Id, 4), IF (MID ( Id, 13, 1) <> "0", RIGHT(Id, 3), IF (MID ( Id, 14, 1) <> "0", RIGHT(Id, 2), "") ) ) ) ) ) ) ) ) )

 

 

 

This was selected as the best answer
KT@CiscoKT@Cisco

Thanks werewolf.

 

Using the information you provided regarding how the ref ID gets generated, I was able to construct a smaller formula to achieve the same results. Below is the formula which works for me. Just thought of sharing it for the benefit of others.

 

Create a FORMULA field in the Case Object with the following formula.

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

 

 

werewolfwerewolf

You are making an assumption there which is invalid, namely that the only significant characters in the case IDs are the rightmost 4 or 5 ones.  Your formula will fail if you happen to have a case created early in Salesforce.com's lifetime (where the rightmost 3 chars are the significant ones) or for cases created once the rightmost 6th char becomes significant, as it inevitably will.  My formula takes that into account.

 

Your orgId probably will not change either, but the same principle applies to $Organization.Id.

KT@CiscoKT@Cisco

Thanks werewolf. That is nice to know.

 

However, I was unable to use your formula directly since the formula field did not allow more than a certain number of characters in the formula.

 

Also, in our case, our instance was created last year and we do not have any older cases. I agree that relying on the said number of digits from the left or right may not be the best way to solve this but given the information I have at hand, I couldn't think of any other way to resolve it. Even if we use Regex and parse the values for Case and Organization IDs, we still would have to work one digit at a time and will be bounded by the total number of digits in each case. If Salesforce.com changes the mask for its Case and/or Organization fields in the underlying schema, neither solutions would work.

 

Thanks once again for your help and guidance.

Message Edited by KT@Cisco on 03-19-2010 02:11 PM
werewolfwerewolf
Indeed.  You can probably omit the org ID part and leave the case ID part -- that should leave a formula small enough to compile.