Saturday, October 6, 2012

Winter 13 introduces State drop downs but needs data cleanse

It's been a while since I've posted anything new but that doesn't mean I haven't found the need to fix issues with Salesforce. Here is an example that I built about six months ago and then forgot about until I heard about the upcoming Winter 13 release and some sort of drop down lists for States and Provinces.  This is a Pilot feature that you need to sign up to get and, I believe, this will only be offered for Canada and the USA.

While I applaud SF's effort I believe this approach is flawed. First the issue goes beyond just the USA and Canada and secondly: data in these address fields can come from other sources than just the Salesforce user interface!  It is especially true if you import records from data.com or from web forms or marketing tools such as Marketo, Pardot, Hubspot to name a few. How will this new Pilot ensure that all values inserted follow the rules set out in the picklist? I think this feature will not not address this problem.  Therefore, you can't run reports based on the country or state fields!  How broken is that?

A while ago I found a way to fix this problem.  To me my fix seems versatile enough that it could be extended to more geographic regions and extended to other data fields that need to accept a range of values and yet be stored in a canonical form.

The technique makes use of a couple of SF Custom Settings Objects and a Trigger on the Account and Contact objects.

Trigger

First let's look at the trigger and then we will look at the data set.  I only show the trigger on the Account object but the one on the Contact object is very similar and easy to build from this code.

trigger AccountBeforeUpsert on Account (before insert, before update) {
...

Map dataMatches = DataMatch__c.getAll();
for(Account theAccount : trigger.new) {
  String target = theAccount.BillingCountry;
  if(target != null) {
    target = target.toLowerCase();
    DataMatch__c match = dataMatches.get(target); 
    if(match != null ) {
      DataIntegrity__c value = DataIntegrity__c.getValues(match.DataIntegrityKey__c);
      if(value != null) {
        theAccount.BillingCountry = value.Value__c;
      }
    }
  }
  target = theAccount.BillingState;
  if(target != null) {
    target = target.toLowerCase();
    match = dataMatches.get(target); 
    if(match != null ) {
      DataIntegrity__c value = DataIntegrity__c.getValues(match.DataIntegrityKey__c);
      if(value != null) {
        theAccount.BillingState = value.Value__c;
      }
    }
  }
}
...
}

As you can see the pattern is quite simple.
  1. Before the record is inserted or updated check the field you want to canonicalize.
  2. Convert the string to all lower case characters. Note this method probably will not work in some languages. I know that you must be careful with the locale when working in Java but I have not tried this method in Salesforce.  Here is a good test: switch to the locale for Turkey and convert any word with an upper case I to lower case i and back to upper case. If you get the same upper case I then it works.
  3. Look up the value in a set of data that has every possible combination of your field (I'll explain more in a minute).
  4. If found then use an associated key to look up the canonical (standard) form to insert into the record.
For example,  if the incoming country field contains any of the following variations the trigger will insert "U.S.A"
  • U.S.
  • United States
  • United States of America
  • US
  • USA
  • U.S.A
  • and more

Data Tables / Custom Settings

The trigger depends on two custom settings: DataMatch__c and DataIntegrity__c. Create a custom setting via Setup... Develop ... Custom Settings.   These are similar to Salesforce Objects but not quite the same.   The way I'm using these two objects they are similar to a Java HashMap or C# Dictionary.  Once you create a Custom Setting the trick is to press the Manage button to actually create the list.  But, once I got the basic structure the way I wanted it I used a data loader to bulk load the data from a CSV file (which I built by hand!  time consuming ... tedious ... but necessary).

   select Name, DataIntegrityKey__c from DataMatch__c

Here is some sample content of this object:

  "Name","DataIntegrityKey__c"
  "nu","Nunavit"
  "nun.","Nunavit"
  "colo.","Colorado"
  "colorado","Colorado"
  "conn.","Connecticut"
  "connecticut","Connecticut"
  "ct","Connecticut"
  "ct.","Connecticut"
  "d.c.","District of Columbia"
  "dc","District of Columbia"
  "de","Delaware"
  "ok","Oklahoma"
  "oklahoma","Oklahoma"
  "on","Ontario"
  "ont","Ontario"
  "ont.","Ontario"
  "or","Oregon"
  "or.","Oregon"
  "ore.","Oregon"
  "oreg.","Oregon"
...
Now, we could just insert the value in the second column but, suppose someone later decides they want to use abbreviations only? You'd then have a major task on you hands!  So, I introduce a second Object (table) that maps the key in the first table to the value we want in the record.

   select  Name,  Value__c from DataIntegrity__c

Here is some sample data from this second table:
  "Name","Value__c"
  "Alabama","Alabama"
  "Alaska","Alaska"
  "Alberta","Alberta"
  "American Samoa","American Samoa"
  "Arizona","Arizona"
  "Arkansas","Arkansas"
  "British Columbia","British Columbia"
  "California","California"
  "Canada","Canada"
  "Colorado","Colorado"
  "Connecticut","Connecticut"
  "Delaware","Delaware"
  "District of Columbia","District of Columbia"
  "Florida","Florida"
  "Georgia","Georgia"
...

First Time Preload

This method works great once it is installed. But what about all the existing data? No problem! Just do a data export of all accounts and then follow with a data load. (I believe you can just import the data as is without changing anything.) On load the trigger will fire for each record and will check the fields against the data tables. VoilĂ  you have data integrity and users can enter the data in a way that suits them. You can also include common mis-spellings in the Data Match table.

Monday, January 23, 2012

Email-To-Case custom email handler - Common Subjects

My users are quite happy with the drastic reduction in duplicate cases because my email to case handler is matching on subject lines and simply appending emails to existing cases. As mentioned above this test compares the incoming email subject line to Cases created in the last five days. Before comparison the subject line is stripped of the typical prefixes such as FW: and RE:

But, today they pointed out that emails from our web site are all getting dumped into the same case. We have a link like this on our web site:
email

The result is we get numerous emails with this same subject line; and my email to case handler puts them all into the same case. Not what we want!

What to do? Answer: have a list of commonly expected subject lines.

Where to put this list? In the code? No! Answer: Salesforce Custom Settings

I don't know about others but I found the documentation hard to understand:
http://www.salesforce.com/us/developer/docs/apexcode/index.htm
https://help.salesforce.com/apex/htviewhelpdoc?id=cs_add_data.htm&language=en

I found this blog more useful:
http://cloudnow.wordpress.com/2011/04/20/custom-settings-how-and-why/

But I'm still not sure about how best to use Custom Settings. Yet, I did get them to work for this common subject line problem.

As per documentation;

  1. create a new Custom Setting object called EmailToCase__c
  2. create a field in this object called CommonSubjects__c
  3. click Manage on the object 
  4. click New (this was the weird part. What do you do?) The edit screen has two inputs "Name" and "CommonSubjects".  Eventually I realized this is Name and Value.  I entered: Name =  WebContactUsForm   Value = CommonSubjects:    Inquiry from link on Carmanah Technologies web site 
  5. Repeat step 4 for any other common subjects.
The EmailToCase__c object now has a text field. To skip common subject lines we iterate over all EmailToCase__c objects and test the incoming subject line to one of the settings.

In the CaseEmailInBoundUtilities class' processInboundEmail method insert the following code just before searching for matching subject lines:
 // try to match subject
 String mainSubject = extractMainSubject(email.subject);
 if(mainSubject!=null) {
 Boolean proceedToTestForMatch = true;
 
 Map eToCaseSettings = EmailToCase__c.getAll();
 List subjectLines = new List();
 subjectLines.addAll(eToCaseSettings.keySet());
 for (String key : subjectLines) {
  EmailToCase__c eToC = eToCaseSettings.get(key);
  if(eToC.CommonSubjects__c.equals(mainSubject)) {
   System.debug('Matched common subject line. Name: ' +eToC.Name + ' Subject: '+ eToC.CommonSubjects__c );
   proceedToTestForMatch = false;
  }
 }  
 if(proceedToTestForMatch 
.... continue to rest of method that searches for Cases with matching subject line.




For the record here is the complete method
public Messaging.InboundEmailResult processInboundEmail(Messaging.InboundEmail email)
{
 Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
 result.success = true;
 this.inboundEmail = email;
 

 String caseNumber = extractRef(email.subject);
 if(caseNumber != null)
 {  
  if(TRACE)system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  extracted case number: "' + caseNumber + '"');
  this.theCase = locateByCaseNumberAsString(caseNumber);
  if(this.theCase == null) {
   // TODO email error message to SysAdmin
   system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case.  Could not find case number: "' + caseNumber + '"');
  }
 } else {
  // try to match subject
  String mainSubject = extractMainSubject(email.subject);
  if(mainSubject!=null) {
  Boolean proceedToTestForMatch = true;
  
  Map eToCaseSettings = EmailToCase__c.getAll();
        List subjectLines = new List();
        subjectLines.addAll(eToCaseSettings.keySet());
        for (String key : subjectLines) {
            EmailToCase__c eToC = eToCaseSettings.get(key);
            if(eToC.CommonSubjects__c.equals(mainSubject)) {
             System.debug('Matched common subject line. Name: ' +eToC.Name + ' Subject: '+ eToC.CommonSubjects__c );
             proceedToTestForMatch = false;
            }
        }  
  // Only try to match non-trivial subject lines. Otherwise too many different issues can me merged into one case.
  if(proceedToTestForMatch && mainSubject.length() > 15) {
   // Only match subjects on cases Created in the last 5 days. 
   Case[] matchingCases = [Select Id, CaseNumber, Subject, Description 
       from Case where Subject = :mainSubject
    and CreatedDate = LAST_N_DAYS:5];
   if(matchingCases.size() == 1) {
    this.theCase = matchingCases[0];
   } else 
   {
    system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case because we found '+matchingCases.size() + ' cases with the subject: "' + mainSubject + '"');
   }
  }
  }
 }
 if(this.theCase == null) {
  // else create a new Case
  this.theCase = new Case();
  theCase.SuppliedEmail = email.fromAddress;
  theCase.SuppliedName = email.fromName;
  theCase.Status = 'New';
  theCase.Priority = 'Low';
  theCase.OwnerId = this.defaultCaseOwnerId;
        theCase.Origin = 'Email';
        String extractedSubject = extractMainSubject(email.subject);        
  theCase.Subject = extractedSubject;
  theCase.Description = email.plainTextBody;
  
  Contact[] contacts = [SELECT Id, Name, AccountId, Email FROM Contact WHERE Email = :email.fromAddress];
  if(contacts.size() >0) 
  {
   Contact theContact = contacts[0];
   theCase.ContactId = theContact.Id;
   theCase.AccountId = theContact.AccountId;   
   if(contacts.size() > 1) {
    // Could-Should create a new Case here to get CS to resolve this....
    theCase.Description = 'Note: there is more than on Contact with this email address. Fix this. ' + theCase.Description;
   }
  }
  insertSObject(this.theCase);
 }

 createEmailMessage(theCase,email);
 handleAttachments(theCase, email);
 return result;
}


Monday, January 16, 2012

Email-To-Case custom email handler - updates

The handler is working really well. But with a few minor issues.

extractRef

  1. Update the code in extractRef to check for null.
  2. Allow for more variations of Case in subject line

patternString = '.*case\\s*[;:=#]?\\s*([0-9]+).*';


private String extractRef(String emailSubject)
{
 String itemRef = null;
// add test for null
 if(emailSubject == null)
  return null;
 String target = emailSubject.toLowerCase();
 String patternString;
 Pattern thePattern;
 Matcher matcher;
 
/*  Take the text between the period and ":ref"  For example in the ref [ ref:00D7JFzw.5007H3Rh8:ref ] extract 5007H3Rh8
 Take that text and remove the 5007. For example H3Rh8 
 Append H3Rh8 to https://na5.salesforce.com/5007000000  to produce https://na5.salesforce.com/5007000000H3Rh8.   This is your link to get to the case.
*/  
 patternString = '.*ref:(.{8}).(.{4})(.+):ref.*';
 thePattern = Pattern.compile(patternString);
 matcher = thePattern.matcher(emailSubject); // do not change to lower case for this test because Id's are case sensitive
  
 if (matcher.matches()) {
  String caseId = matcher.group(2) + '000000' + matcher.group(3);
  if(TRACE) system.debug(Logginglevel.ERROR,'extractRef "' + caseId + '"');    
  Case[] matchingCases = [Select CaseNumber from Case where Id = :caseId];
  if(matchingCases.size() == 1) {
   Case theCase = matchingCases[0];
   itemRef = theCase.CaseNumber;
  }    
 }  
 if(itemRef == null) {
  // extract the Case Number from the email Subject
  // Re: Test two numbers Case: 30088 and Case: 30089'
  // returns 30089, the last pattern matched
// Change the pattern to allow for more variations:
  patternString = '.*case\\s*[;:=#]?\\s*([0-9]+).*';
  thePattern = Pattern.compile(patternString);
  matcher = thePattern.matcher(target);
  
  if (matcher.matches()) {
   itemRef = matcher.group(1);
   if(TRACE) system.debug('Extracted case number ' + itemRef); 
  }
 }

 return itemRef; 
}

Thursday, January 5, 2012

Email-To-Case custom email handler - Part 2

Well the email handler is working well with only one problem. The idea to locate the Case based on subject line matching is good. But there are times when it is "too good" and two unrelated emails can be placed into the same case. Before discussing the fixes let's look at the code.
Refer to the posting Email-to-Case can create too many cases.


IN the method processInboundEmail look for the code that locates cases based on subject line. Here is the before:

...
else {
  // try to match subject
  String mainSubject = extractMainSubject(email.subject);
  Case[] matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject
   and CreatedDate = LAST_N_DAYS:5];
  if(matchingCases.size() == 1) {
   this.theCase = matchingCases[0];
  } else 
  {
   system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case because we found '+matchingCases.size() + ' cases with the subject: "' + mainSubject + '"');
  }
 }
....

This works well for normal issues that have some substantial subject line. But what about those simple topics like "Ordering", or "Inquiry" or even an empty subject line.

Quick fix
...
// try to match subject
String mainSubject = extractMainSubject(email.subject);
// Only try to match non-trivial subject lines. Otherwise too many different issues can me merged into one case.
if(mainSubject!=null && mainSubject.length() > 15) {
 // Only match subjects on cases Created in the last 5 days. 
 Case[] matchingCases = [Select Id, CaseNumber, Subject, Description 
  from Case where Subject = :mainSubject
  and CreatedDate = LAST_N_DAYS:5];
...

Better Fix
Someday or someone else might create a solution that refines the match to cases that have similar recipients on the email thread.

Monday, January 2, 2012

Email-To-Case custom email handler

See my previous post to explain why a custom Email-To-Case handler is required and for the complete code that provides the solution. In this posting I'll explain how it works.

First, here is the email handler class that invokes the email procesor:

global class CaseEmailInBoundHandler implements Messaging.InboundEmailHandler {

    global Messaging.InboundEmailResult handleInboundEmail(
       Messaging.InboundEmail email,
       Messaging.InboundEnvelope envelope) {

 CaseEmailInBoundUtilities handler = new CaseEmailInBoundUtilities();
        Messaging.InboundEmailResult result = handler.processInboundEmail(email);
        return result;        
    }
}

This class is kept very simple to put all the working code into a class that can be covered by unit tests. All Salesforce code needs to be covered by unit tests or it can not be deployed to the production server.

Unit Testing

I do not like the fact that all Salesforce Apex class are in the same namespace. This makes it impossible for developers to group classes into folders or packages. This is a common practice in all software languages and it is sorely missed in Apex development. To workaround this limitation I build larger classes and include all unit test methods in the class file; with the test methods placed at the bottom of the file.


Email Handler

The whole handler is contained in one class CaseEmailInBoundUtilities. The constructor locates the main queue where all new Cases are placed pending attention of the support staff. I store the main Case and incoming email message to access them in some error handling methods.

public with sharing class CaseEmailInBoundUtilities {

private Static Boolean TRACE = true;

protected Case theCase = null;
protected Messaging.InboundEmail inboundEmail;
protected String defaultCaseOwnerId;

public CaseEmailInBoundUtilities() {
 Group[] queues = [select Id, Name from Group where Name = 'Triage'];
 this.defaultCaseOwnerId = queues[0].Id;  
}
....

The main entry point is the public method processInboundEmail
public Messaging.InboundEmailResult processInboundEmail(Messaging.InboundEmail email)
{
// set up
 Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
 result.success = true;
 this.inboundEmail = email;
 
// locate Case Number in email subject line, if present locate the main Case
 String caseNumber = extractRef(email.subject);
 if(caseNumber != null)
 {  
  this.theCase = locateByCaseNumberAsString(caseNumber);
  if(this.theCase == null) {
   // TODO email error message to SysAdmin
  }
 } else {
// try to match subject
  String mainSubject = extractMainSubject(email.subject);
  Case[] matchingCases = [Select Id, CaseNumber, Subject, Description 
    from Case where Subject = :mainSubject and CreatedDate = LAST_N_DAYS:5];
  if(matchingCases.size() == 1) {
   this.theCase = matchingCases[0];
  } else 
  {
  // because there no one Case can be considered the primary case.
  // do nothing which will create a new Case 
  }
 }
 if(this.theCase == null) {
  // else create a new Case
  this.theCase = new Case();
  theCase.SuppliedEmail = email.fromAddress;
  theCase.SuppliedName = email.fromName;
  theCase.Status = 'New';
  theCase.Priority = 'Low';
  theCase.OwnerId = this.defaultCaseOwnerId;
        theCase.Origin = 'Email';
  theCase.Subject = email.Subject;
  theCase.Description = email.plainTextBody;
  
  Contact[] contacts = [SELECT Id, Name, AccountId, Email 
    FROM Contact WHERE Email = :email.fromAddress];
  if(contacts.size() >0) 
  {
   Contact theContact = contacts[0];
   theCase.ContactId = theContact.Id;
   theCase.AccountId = theContact.AccountId;   
   if(contacts.size() > 1) {
    // Could create a new Case here to get CS to resolve this....
    theCase.Description = 'Note: there is more than one Contact with this email address. Fix this. ' + theCase.Description;
   }
  }
  insertSObject(this.theCase);
 }

 createEmailMessage(theCase,email);
 handleAttachments(theCase, email);

 return result;
}

There are a number of key helper methods in the above that I'll explain next.

extractRef

The method extractRef will look into the email subject and either find the Saleforce reference Id (This is needed to support a transition from the SFDC default handler!) or the case number.

Go read the previous post to see the code. It contains some interesting stuff that locates the cryptic reference and deciphers it into a object reference ID that can be used to locate the Case.


extractMainSubject

The method extractMainSubject looks for the "main" subject line. It strips off any leading prefix such as FW or RE. This leaves the main original subject line that can be used to locate existing cases.

locateByCaseNumberAsString

The method locateByCaseNumberAsString overcomes the problem of locating the Case by number when there might be leading zeros. For more on this see my question on stackoverflow


insertSObject

The method insertSObject simply wraps the call to insert an object into the SF system. The code mainly handles errors with the error handling methods described in an earlier post.

createEmailMessage and handleAttachments

The last two methods do the main work of creating the EmailMessage object, attaching it to the Case and doing the same for all email attachments. See the code below.

private void createEmailMessage(Case theCase, Messaging.InboundEmail email) {
 String value;
 Integer maxlength;
 EmailMessage theEmail = new EmailMessage();
 theEmail.ParentId = theCase.Id;
 theEmail.Incoming = true;
 theEmail.Subject = limitLength(email.Subject, EmailMessage.Subject.getDescribe().getLength());
 theEmail.MessageDate = datetime.now();
 theEmail.HtmlBody = limitLength(email.htmlBody,EmailMessage.HtmlBody.getDescribe().getLength());  
 theEmail.TextBody = limitLength(email.plainTextBody,EmailMessage.TextBody.getDescribe().getLength());

 /* **** To */
 value = '';
 if(email.toAddresses != null) {
  Boolean seenOne= false;
  for(String to : email.toAddresses) {
   if(seenOne) {
    value += ';\n';
   }
   to  = extractAddress(to);
   system.debug('ToAddress: ' + to);
   value += to;
   seenOne = true;
  }
 }
 theEmail.ToAddress = limitLength(value,EmailMessage.ToAddress.getDescribe().getLength());
 
 /* **** From */
 theEmail.FromName = email.fromName;
 theEmail.FromAddress = email.fromAddress;
 
 /* **** CC */
 value = '';
 if(email.ccAddresses != null) {
  Boolean seenOne= false;
  for(String cc : email.ccAddresses) {
   if(seenOne) {
    value += ';\n';
   }
   cc  = extractAddress(cc);
   system.debug('CcAddress: ' + cc);
   value += cc;
   seenOne = true;
  }
 }
 theEmail.CcAddress = limitLength(value,EmailMessage.CcAddress.getDescribe().getLength()); 
 insertSObject(theEmail);
}


limitLength

The method limitLength is used to keep the value within the bounds, otherwise the insert will fail. The typical invocation uses the Salesforce schema methods to describe the system.

theEmail.TextBody = limitLength(email.plainTextBody,
  EmailMessage.TextBody.getDescribe().getLength());

Email-to-Case can create too many cases.

First, how does email-to-case work? Salesforce provides a built in email handler that will accept an email and create a Case. (It does other tasks such as saving attachments and linking the Case to the customer Contact record.) When the Service Representative emails the answer the response contains a reference ID that the Salesforce email handler can use to link the next email to the original Case.

For example, our customer writes an email with the subject "another question". They send the email to our support email address which forwards the email to our instance of Salesforce. The built-in handler creates a new Case. Our staff will respond using the Salesforce email tools and the customer gets their answer. The subject line of our email to the customer looks like this:

RE: another question [ ref:00D7JFzw.5007KcZYu:ref ]

When the customer writes back the email handler will decipher the reference and append the response to the original case. All this is great. The problem comes when that reference Id is not part of the incoming email because this creates a new Case. The problem is worse when many people are involved in the email problem and they all continue correspondence without including the reference. Our staff can spend many hours manually combining the resulting Cases.

My plan is to build my own email handler that can match email subject lines and combine the cases for my users.

This handler can also solve another problem for my users: get rid of those cryptic and long references. Who wants to see "[ ref:00D7JFzw.5007KcZYu:ref ]" in every subject line. What does this mean to our users? Why not link the email to the case based on Case Number?

RE: another problem Case: 0031452

Doesn't that look better? It is also easy to train our staff and regular customers to include the Case Number in any email to discuss a particular issue.

Here is class with code that works for us. I'm posting it without comment just to get this code out. Perhaps in a follow up post I'll explain the parts.


public with sharing class CaseEmailInBoundUtilities {

private Static Boolean TRACE = true;

protected Case theCase = null;
protected Messaging.InboundEmail inboundEmail;
protected String defaultCaseOwnerId;

public CaseEmailInBoundUtilities() {
 Group[] queues = [select Id, Name from Group where Name = 'Triage'];
 this.defaultCaseOwnerId = queues[0].Id;  
}

public Messaging.InboundEmailResult processInboundEmail(Messaging.InboundEmail email)
{
 Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
 result.success = true;
 this.inboundEmail = email;
 

 String caseNumber = extractRef(email.subject);
 if(caseNumber != null)
 {  
  if(TRACE)system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  extracted case number: "' + caseNumber + '"');
  this.theCase = locateByCaseNumberAsString(caseNumber);
  if(this.theCase == null) {
   // TODO email error message to SysAdmin
   system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case.  Could not find case number: "' + caseNumber + '"');
  }
 } else {
  // try to match subject
  String mainSubject = extractMainSubject(email.subject);
  Case[] matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject
   and CreatedDate = LAST_N_DAYS:5];
  if(matchingCases.size() == 1) {
   this.theCase = matchingCases[0];
  } else 
  {
   system.debug(Logginglevel.ERROR,'CaseEmailInBoundUtilities.  Create a new case because we found '+matchingCases.size() + ' cases with the subject: "' + mainSubject + '"');
  }
 }
 if(this.theCase == null) {
  // else create a new Case
  this.theCase = new Case();
  theCase.SuppliedEmail = email.fromAddress;
  theCase.SuppliedName = email.fromName;
  theCase.Status = 'New';
  theCase.Priority = 'Low';
  theCase.OwnerId = this.defaultCaseOwnerId;
        theCase.Origin = 'Email';
  theCase.Subject = email.Subject;
  theCase.Description = email.plainTextBody;
  
  Contact[] contacts = [SELECT Id, Name, AccountId, Email FROM Contact WHERE Email = :email.fromAddress];
  if(contacts.size() >0) 
  {
   Contact theContact = contacts[0];
   theCase.ContactId = theContact.Id;
   theCase.AccountId = theContact.AccountId;   
   if(contacts.size() > 1) {
    // Could-Should create a new Case here to get CS to resolve this....
    theCase.Description = 'Note: there is more than on Contact with this email address. Fix this. ' + theCase.Description;
   }
  }
  insertSObject(this.theCase);
 }

 createEmailMessage(theCase,email);
 handleAttachments(theCase, email);

  // send success notification. This can be disabled once we know the code is stable.
 // if(result.success)
 // {
 //  String successMessage = 'Successful processing of inbound email  \n'+ noteBody;
 //  TriggerErrorNotification.reportInfo(messagePrefix+ ' success ',successMessage);      
 // }
 return result;
}

// Save attachments, if any
private void handleAttachments(Case theCase, Messaging.InboundEmail email) {
 if(email.textAttachments!=null && email.textAttachments.size() >0) {
  for (Messaging.Inboundemail.TextAttachment tAttachment : email.textAttachments) {
    Attachment attachment = new Attachment();  
    attachment.Name = tAttachment.fileName;
    attachment.Body = Blob.valueOf(tAttachment.body);
    attachment.ParentId = theCase.Id;
    insertSObject(attachment);
  }
 }
 
 if(email.binaryAttachments!=null && email.binaryAttachments.size() >0) {
  for (Messaging.Inboundemail.BinaryAttachment bAttachment : email.binaryAttachments) {
    Attachment attachment = new Attachment();
    attachment.Name = bAttachment.fileName;
    attachment.Body = bAttachment.body;
    attachment.ParentId = theCase.Id;
    insertSObject(attachment);
  }
 } 
}

private void insertSObject(sObject obj) {
 try {insert obj;} catch (System.DmlException e) {handleError(e, 'Could not insert obj '+ obj);}
}

private String limitLength(String input, Integer maxLength)
{
 String results;
 if(input != null && input.length() > maxLength)
  results = input.substring(0,maxLength);
 else 
  results = input;
 return results;
}

private void createEmailMessage(Case theCase, Messaging.InboundEmail email) {
 String value;
 Integer maxlength;
 EmailMessage theEmail = new EmailMessage();
 theEmail.ParentId = theCase.Id;
 theEmail.Incoming = true;
 Schema.DescribeFieldResult F = EmailMessage.HtmlBody.getDescribe();
 //.HtmlBody.getDescribe();
 maxlength = F.getLength();
 theEmail.Subject = limitLength(email.Subject, EmailMessage.Subject.getDescribe().getLength());
 theEmail.MessageDate = datetime.now();
 theEmail.HtmlBody = limitLength(email.htmlBody,EmailMessage.HtmlBody.getDescribe().getLength());  
 theEmail.TextBody = limitLength(email.plainTextBody,EmailMessage.TextBody.getDescribe().getLength());

 /* **** To */
 value = '';
 if(email.toAddresses != null) {
  Boolean seenOne= false;
  for(String to : email.toAddresses) {
   if(seenOne) {
    value += ';\n';
   }
   to  = extractAddress(to);
   system.debug('ToAddress: ' + to);
   value += to;
   seenOne = true;
  }
 }
 theEmail.ToAddress = limitLength(value,EmailMessage.ToAddress.getDescribe().getLength());
 
 /* **** From */
 theEmail.FromName = email.fromName;
 theEmail.FromAddress = email.fromAddress;
 
 /* **** CC */
 value = '';
 if(email.ccAddresses != null) {
  Boolean seenOne= false;
  for(String cc : email.ccAddresses) {
   if(seenOne) {
    value += ';\n';
   }
   cc  = extractAddress(cc);
   system.debug('CcAddress: ' + cc);
   value += cc;
   seenOne = true;
  }
 }
 theEmail.CcAddress = limitLength(value,EmailMessage.CcAddress.getDescribe().getLength()); 
 insertSObject(theEmail);
}



private void handleError(System.DmlException e,  String message){
 String baseURL = URL.getSalesforceBaseUrl().toExternalForm() + '/';
 if(TRACE)system.debug(baseURL);
 String caseURL;  
 String msg = message + '\n';
 if(this.theCase != null)
 {
  caseURL = baseURL + theCase.Id;
  msg += '\n';
  msg += 'Originating Case Number: ' + theCase.CaseNumber + '  '+ caseURL+'\n';   
 }
 if(this.inboundEmail != null) {
  msg += '\nEmail:';
  msg += '  subject: ' + inboundEmail.Subject + '\n'; 
  msg += '  from: ' + inboundEmail.FromName + '\n'; 
  msg += '  address: ' + inboundEmail.FromAddress + '\n'; 
 }
 if(e != null) { // compose the DmlException message on one line to minimize the number of untested lines.  AFAIK easy to instantiate a DmlException in a unit test. 
  msg += '\n';
  msg += 'EXCEPTION:\n  Error: ' + e.getMessage() + '\n  Type: ' + e.getTypeName() + '\n  Line Number: ' + e.getLineNumber() + '\n  Trace:\n' + e.getStackTraceString() + '\n(end stack trace)\n';
 }

 Case errCase = new Case();
 errCase.OwnerId = this.defaultCaseOwnerId;
 errCase.Status = 'New';
 errCase.Priority = 'Low';
    errCase.Origin = 'Email';
 errCase.Subject = 'Error processing incoming email';
 errCase.Description = limitLength(msg,Case.Description.getDescribe().getLength());
 insert errCase;
 errCase = [Select Id, CaseNumber from Case where Id = :errCase.Id limit 1];  

 caseURL = baseURL + errCase.Id;
 msg += '\n\n';
 msg += 'Created new Case number ' + errCase.CaseNumber + ' for this error.  See: ' + caseURL +'\n'; 

 TriggerErrorNotification.reportError('CaseEmailInBoundUtilities', msg); 

}


/*
Given a case number such as 8144 find the exact case that use this number. Note that CaseNumber is a string field 
that may have any number of leading zeros. 
*/
private Case locateByCaseNumberAsString(String caseNumberStr){
 Integer target = Integer.valueOf(caseNumberStr);
 Case theResult = null;
 String caseNumber = '%' + String.valueOf(target);
 Case[] matchingCases = [Select Id, CaseNumber, Subject, Description from Case where CaseNumber like :caseNumber];
 for(Case aCase: matchingCases) {
  Integer cnum = Integer.valueOf(aCase.CaseNumber);
  if(cnum == target) {
  theResult = aCase;
  break;
  }
 }
 return theResult;
}

/*
Look for the case reference in the email subject line.  First search for a case reference using the
standard Salesforce method of creating that complicated and non-user-friendly reference.  Do this first
so it takes precedence.

But, also search for the case number itself. This is user-friendly!
*/
private String extractRef(String emailSubject)
{
 String itemRef = null;
 String target = emailSubject.toLowerCase();
 String patternString;
 Pattern thePattern;
 Matcher matcher;
 
/*  Take the text between the period and ":ref"  For example in the ref [ ref:00D7JFzw.5007H3Rh8:ref ] extract 5007H3Rh8
 Take that text and remove the 5007. For example H3Rh8 
 Append H3Rh8 to https://na5.salesforce.com/5007000000  to produce https://na5.salesforce.com/5007000000H3Rh8.   This is your link to get to the case.
*/  
 patternString = '.*ref:(.{8}).(.{4})(.+):ref.*';
 thePattern = Pattern.compile(patternString);
 matcher = thePattern.matcher(emailSubject); // do not change to lower case for this test because Id's are case sensitive
  
 if (matcher.matches()) {
  String caseId = matcher.group(2) + '000000' + matcher.group(3);
  if(TRACE) system.debug(Logginglevel.ERROR,'extractRef "' + caseId + '"');    
  Case[] matchingCases = [Select CaseNumber from Case where Id = :caseId];
  if(matchingCases.size() == 1) {
   Case theCase = matchingCases[0];
   itemRef = theCase.CaseNumber;
  }    
 }  
 if(itemRef == null) {
  // extract the Case Number from the email Subject
  // Re: Test two numbers Case: 30088 and Case: 30089'
  // returns 30089, the last pattern matched
  patternString = '.*case[;:=]?\\s*([0-9]+).*';
  thePattern = Pattern.compile(patternString);
  matcher = thePattern.matcher(target);
  
  if (matcher.matches()) {
   itemRef = matcher.group(1);
   if(TRACE) system.debug('Extracted case number ' + itemRef); 
  }
 }

 return itemRef; 
}

private String extractMainSubject(String emailSubject)
{
 if(emailSubject == null || emailSubject.length() < 3)
  return emailSubject;
 String[] prefixes = new String[] {'fw:','re:', 'automatic reply:', 'out of office autoreply:', 'out of office'};  
 String target = emailSubject.toLowerCase();
 for(String prefix: prefixes) {
  Integer index = target.indexOf(prefix); 
  if(index == 0 ){
   String mainSubject = emailSubject.substring(prefix.length(),emailSubject.length());
   return mainSubject.trim();
  }  
 }
 return emailSubject; 
}

private String extractAddress(String inAddress)
{
 String address;
 String patternString;
 Pattern thePattern;
 Matcher matcher;
 patternString = '.*<(.*)>.*';
 thePattern = Pattern.compile(patternString);
 matcher = thePattern.matcher(inAddress);
 if (matcher.matches()) {
  address = matcher.group(1);
  system.debug('Extracted address ' + address); 
 }
 else
 {
  address = inAddress;
  system.debug('Did not match angle-address ' + address);   
 }
 return address;
}
/* *****************************************************************
TEST METHODS
*/
 
 
static testmethod void testExtractAddress()
{
 system.debug('testExtractAddress ');   
 String expected = 'b.g@g.com';
 String target = 'Bryan <'+expected+'>';
 CaseEmailInBoundUtilities prutil = new CaseEmailInBoundUtilities();
 String results = prutil.extractAddress(target);
 System.assertEquals(expected,results);  
target = 'Helen W ';
expected = 'Helen.W@on.com';

 results = prutil.extractAddress(target);
 System.assertEquals(expected,results);  



} 
static testmethod void testExtractRef()
{
 Test.startTest();
 String emailSubject = 'Subject Case: 987 asdas asdasd ';
 CaseEmailInBoundUtilities prutil = new CaseEmailInBoundUtilities();
 String caseNumber = prutil.extractRef(emailSubject);
 
 system.assertEquals('987',caseNumber);
 
 emailSubject = 'Subject cAse=987 asdas asdasd ';
 caseNumber = prutil.extractRef(emailSubject);
 system.assertEquals('987',caseNumber);

 
 Case aCase = new Case();
 insert aCase;
 Case bCase = [Select Id, CaseNumber from Case where Id = :aCase.Id limit 1];
 
 String caseId = bCase.Id;
 String left = caseId.substring(0, 4);
 String right = caseId.substring(caseId.length()-8, caseId.length());
 right = right.substring(0,5);
system.debug(Logginglevel.ERROR,'testExtractRef case id: "' + caseId + '"');    
system.debug(Logginglevel.ERROR,'testExtractRef case n: "' + bCase.CaseNumber + '"');    
   
 emailSubject = 'For example in the ref [ ref:00D7JFzw.5007H3Rh8:ref ] ';
 emailSubject = 'For example in the ref [ ref:00D7JFzw.'+left+right+':ref ] ';
system.debug(Logginglevel.ERROR,'testExtractRef email subject: "' + emailSubject + '"');    

 caseNumber = prutil.extractRef(emailSubject);
 system.assertEquals(bCase.CaseNumber,caseNumber);
 Test.stopTest();
 
 //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 ] ")
 
// LEFT( Id, 4) 
//SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Id, R4, ""), L4, ""), "0", "") + R4  
 
}

 
static testmethod void testExtractMain()
{
 CaseEmailInBoundUtilities prutil = new CaseEmailInBoundUtilities();
 String emailSubject = 'Some Subject Case';
 String fwSubject = 'FW: ' + emailSubject;
 
 String mainSubject;
 
 mainSubject = prutil.extractMainSubject(fwSubject);
system.debug(Logginglevel.ERROR,'testExtractMain "' + mainSubject + '"'); 
 system.assertEquals(emailSubject,mainSubject);

 String reSubject = 'Re: ' + emailSubject;
 mainSubject = prutil.extractMainSubject(reSubject);
system.debug(Logginglevel.ERROR,'testExtractMain "' + mainSubject + '"'); 
 system.assertEquals(emailSubject,mainSubject);

}

static testMethod void testHandleError()
{
 CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
 // create a new email 
 Messaging.InboundEmail email;
 email = new Messaging.InboundEmail() ;
 email.subject = 'Test subject';
 email.plainTextBody = 'Test email';
 email.fromname = 'FirstName LastName';
 email.toAddresses = new String[] {'someaddress@email.com'};
 emailProcess.inboundEmail = email;
 
 emailProcess.handleError(null,'unit testing handle error'); 
}

static testMethod void testProcessInboundEmailGoodWithAttachment() { 
  Test.startTest();
  // create a new email 
  Messaging.InboundEmail email = new Messaging.InboundEmail() ;
  email.subject = 'Subject asdas asdasd ';
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};
  email.ccAddresses = new String[] {'one@email.com','two@email.com'};

  String csv = 'this is just a test';
  
  // add an attachment
  Messaging.InboundEmail.BinaryAttachment attachment = new Messaging.InboundEmail.BinaryAttachment();
  attachment.body = blob.valueOf(csv);
  attachment.fileName = 'data.csv';
  attachment.mimeTypeSubType = 'text/plain';
  
  email.binaryAttachments = new Messaging.inboundEmail.BinaryAttachment[] { attachment };
  
  Messaging.InboundEmail.TextAttachment tattachment = new Messaging.InboundEmail.TextAttachment();
  tattachment.body = csv;
  tattachment.fileName = 'data.csv';
  tattachment.mimeTypeSubType = 'text/plain';
  
  email.textAttachments = new Messaging.inboundEmail.TextAttachment[] { tattachment };
  
  // call the email service class and test it with the data in the testMethod
  CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
  Messaging.InboundEmailResult result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  Test.stopTest();
 } 
 
 static testMethod void testProcessInboundEmailWithMatchingSubject() {
 
  Test.startTest();
  CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
  Messaging.InboundEmail email;
  Messaging.InboundEmailResult result;
  Case[] matchingCases;
  EmailMessage[] matchingEmailMessages;
  Case theCase;
  
  String mainSubject = 'Subject asdas asdasd';

// Test Part 1
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = mainSubject;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject];
  System.assertEquals(1,matchingCases.size());
  theCase = matchingCases[0];
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(1,matchingEmailMessages.size());
  
// TEST Part 2
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = mainSubject;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject];
  // still only one Case because the system will append the second email to the case Case
  System.assertEquals(1,matchingCases.size());
  System.assertEquals(theCase.Id,matchingCases[0].Id);
  // Should be two emails on the case
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(2,matchingEmailMessages.size());
  
// TEST Part 3
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = 'Different subject line but include Case: ' + theCase.CaseNumber;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {'someaddress@email.com'};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :email.subject];
  // Should be no cases created with that subject 
  System.assertEquals(0,matchingCases.size());
  // Should be three emails on the case
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(3,matchingEmailMessages.size());  
  Test.stopTest();
 } 
 
 static testMethod void testProcessInboundEmailWithContacts() {
 
  Test.startTest();
  CaseEmailInBoundUtilities emailProcess = new CaseEmailInBoundUtilities();
  Messaging.InboundEmail email;
  Messaging.InboundEmailResult result;
  Case[] matchingCases;
  EmailMessage[] matchingEmailMessages;
  Case theCase;
  Contact theContact = new Contact();
  theContact.FirstName = 'FirstName';
  theContact.LastName = 'LastName';
  theContact.Email = 'someaddress@email.com';
  
  String mainSubject = 'Subject asdas asdasd';

// Test Part 1
  // create a new email 
  email = new Messaging.InboundEmail() ;
  email.subject = mainSubject;
  email.plainTextBody = 'Test email';
  email.fromname = 'FirstName LastName';
  email.toAddresses = new String[] {theContact.Email};

  // call the email service class and test it with the data in the testMethod
  result = emailProcess.processInboundEmail(email);
  System.assert(result.success == true);
  matchingCases = [Select Id, CaseNumber, Subject, Description from Case where Subject = :mainSubject];
  System.assertEquals(1,matchingCases.size());
  theCase = matchingCases[0];
  matchingEmailMessages = [Select Id from EmailMessage where ParentId = :theCase.Id];
  System.assertEquals(1,matchingEmailMessages.size());
 }  
}

Salesforce for Customer Support

I work for a company that designs and manufactures solar powered lighting products. My company has been using Salesforce for three years but I wasn't that involved with this software until about a year ago. After some company restructuring I was thrust into the position of Salesforce administrator. Our first objective was to improve our customer relations. At the time we had a poor reputation for handling issues. Not at all the fault of my co-workers but simply because we had no cohesive system that ensured all issues were given the proper attention.

I'm a software architect, designer and developer with experience managing a customer relation management system. So I knew that we needed to capture every email correspondence both incoming and outgoing in one central location. This allows a team of support staff to work together in a mutually supportive manner. It allows staff to cross-train each other and this lets people take holidays and to advance into new opportunities; without sacrificing consistency.

The central location also becomes a valuable source of solutions. Technical problems rarely happen just once. But it is very inefficient to solve them over and over again. With a central repository it should be possible to locate problems quickly. If you ask Salesforce about this they will promote Solutions or Knowledge Base. Either or both are likely good but they do not help with the initial research. Before you can say that one needs a Solution or KB Article one needs to know that a problem exists and is worth writing about. To get this initial information we need to be able to search how our staff (our experts) solved end-customers real problems.


Salesforce Email-to-Case

It was remarkably easy to set up a system to manage customer issues (Cases) with Email-to-Case. I'd like to write about the customization made last year but my current interest is the new extensions that I'll add over the next week or so.

Error handling

Here is simple error handling utility class. With this class it is easy to report errors to the system admin or any User that needs to be kept informed.

To set up for this class add two Boolean fields to your User object.
   Apex_Error_Email__c - set true to indicate this user should receive emails on error.
   Apex_Info_Email__c - set true to indicate this user should receive emails on significant events.

public with sharing class ErrorUtilities {
/* Send email with ERROR message to Users that have custom property Apex_Error_Email__c == true
* Input:  
*    String callerName,  text to identify which piece of code called the method.
*    String errorMessage, the main error message to send
*/
public static void reportError(String callerName, String errorMessage) {
 User[] contacts = [select Id, Name, Email from User
  where Apex_Error_Email__c = true AND IsActive = true];
 report(callerName, contacts,'Error message: ',errorMessage);
}

/* Send email with INFORMATION message to Users that have custom property Apex_Info_Email__c == true
* Input:  
*    String callerName,  text to identify which piece of code called the method.
*    String infoMessage, the main information message to send
*/
public static void reportInfo(String callerName, String infoMessage) {
 User[] contacts = [select Id, Name, Email from User
  where Apex_Info_Email__c = true AND IsActive = true];
 report(callerName, contacts,'Information message: ',infoMessage);
}
 
/*  Compose an error message using the optional Exception and optional reference ID to a Salesforce Object
* 
*/
public static void handleError(System.Exception e, String refId, String callerName, String errorMessage){
 String msg = errorMessage + '\n';
 if(refId != null) {
  String baseURL = URL.getSalesforceBaseUrl().toExternalForm() + '/';
  String refURL = baseURL + refId;
  msg += '\n\n';
  msg += 'See: ' + refURL +'\n'; 
 }
 if(e != null) { 
  msg += '\n';
  msg += 'EXCEPTION:\n  Error: ' + e.getMessage() + '\n';
  msg += '  Type: ' + e.getTypeName() + '\n';
  msg += '  Line Number: ' + e.getLineNumber() + '\n';
  msg += '  Trace:\n' + e.getStackTraceString() + '\n(end stack trace)\n';
 }
 ErrorUtilities.reportError(callerName, msg); 
}

private static void report(String callerName,  User[] contacts, String subjectPrefix,String errorMessage) {
 Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
 
 String[] sendTo = new String[]{};
 for(User aUser : contacts) {
  sendTo.add(aUser.Email);
 }
 
 mail.setToAddresses(sendTo);
 mail.setSubject(subjectPrefix + callerName);
 mail.setPlainTextBody('Message: \n' + errorMessage);
 Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
}

public static testmethod void testReportError()
{
 ErrorUtilities.reportError('ErrorUtilities.testReportError', 'This is just a test.');
 ErrorUtilities.reportInfo('ErrorUtilities.testReportError', 'This is just a test.');
 Account sampleAccount = [select Id from Account limit 1];
 ErrorUtilities.handleError(null,sampleAccount.Id,'ErrorUtilities.testReportError','unit testing handle error'); 
 
}
}