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
Sylvie SerpletSylvie Serplet 

Trigger do not update custom account field on EmailMessage when in a Case

Hi all,
I have created custom Account fields on several of my objects.
When an email is created on these objects, the Account field need to be automatically populated. To do so, I have created an APEX trigger which is working perfectly fine for 5 of my objects (I give only 2 examples in my code below) but it does not work for Case. I cannot understand why. Any thought?
trigger UpdateAccountonEmail on EmailMessage (before insert, before update) {
    
    for(EmailMessage em : trigger.new){
        
        if(em.RelatedToId != null){ 
            
            List<Account> accList = [select id, name from Account where id =: em.RelatedToId]; 
            List<Opportunity> oppList = [select id, name, AccountId from Opportunity where id =: em.RelatedToId];        
            List<Case> casList = [select id, CaseNumber, AccountId from Case where id =: em.RelatedToId];
            
         // Account field is populated when an email is created on Opportunity  
            ​if(oppList.size() > 0){
                for(Opportunity o : oppList){
                    em.Account__c = o.AccountId;                
                }
            }
          // Account field is populated when an email is created on Account
            else if(accList.size() > 0){
                for(Account a : accList){
                    em.Account__c = a.Id;
                }
            }
       // Account field is not populated when an email is created on Case       
            else if(casList.size() > 0){
                for(Case ca : casList){
                    em.Account__c = ca.AccountId;                
                }
            }
            else{
                em.Account__c = null; 
            }            
        }
    }
}

I have also noted a very strange behaviour, when I create an email into a case sometimes it stays as an email (test email case 3 below), sometimes it creates a task (Email: test email case 2 below) with the type email. How to avoid or streamline this?

User-added image

Thank you for your help.
Sylvie

 
Best Answer chosen by Sylvie Serplet
Dmitry OfitserovDmitry Ofitserov
Hi Sylvie,
First of all I will recommend to take out all the SOQL queries out of the FOR loop because thus you will get Too Many SOQL Queries 101 exception once the trigger gets more then 20 messages.
As far as I remember the case ID is assosiated with email message through ParentId variable, so please try the following code and let me know if it worked for you.
trigger UpdateAccountonEmail on EmailMessage (before insert, before update) {

    Set<String> relatedToIDs = new Set<String>();
    Set<String> caseIDs = new Set<String>()l
    for(EmailMessage em : trigger.new){
      if(em.RelatedToId != null){ 
        relatedToIDs.add(em.RelatedToId);
      }
      caseIDs.add(em.ParentId);
    }

    List<Account> accList = [select id, name from Account where id IN: relatedToIDs]; 
    List<Opportunity> oppList = [select id, name, AccountId from Opportunity where id IN: relatedToIDs];        
    List<Case> casList = [select id, CaseNumber, AccountId from Case where id IN: caseIDs];
        
    for(EmailMessage em : trigger.new){ 
               
         // Account field is populated when an email is created on Opportunity  
            ​if(oppList.size() > 0){
                for(Opportunity o : oppList){
                    em.Account__c = o.AccountId;                
                }
            }
          // Account field is populated when an email is created on Account
            else if(accList.size() > 0){
                for(Account a : accList){
                    em.Account__c = a.Id;
                }
            }
       // Account field is not populated when an email is created on Case       
            else if(casList.size() > 0){
                for(Case ca : casList){
                    em.Account__c = ca.AccountId;                
                }
            }
            else{
                em.Account__c = null; 
            }  
    }
}
Regards,
Dmitry
 

All Answers

Dmitry OfitserovDmitry Ofitserov
Hi Sylvie,
First of all I will recommend to take out all the SOQL queries out of the FOR loop because thus you will get Too Many SOQL Queries 101 exception once the trigger gets more then 20 messages.
As far as I remember the case ID is assosiated with email message through ParentId variable, so please try the following code and let me know if it worked for you.
trigger UpdateAccountonEmail on EmailMessage (before insert, before update) {

    Set<String> relatedToIDs = new Set<String>();
    Set<String> caseIDs = new Set<String>()l
    for(EmailMessage em : trigger.new){
      if(em.RelatedToId != null){ 
        relatedToIDs.add(em.RelatedToId);
      }
      caseIDs.add(em.ParentId);
    }

    List<Account> accList = [select id, name from Account where id IN: relatedToIDs]; 
    List<Opportunity> oppList = [select id, name, AccountId from Opportunity where id IN: relatedToIDs];        
    List<Case> casList = [select id, CaseNumber, AccountId from Case where id IN: caseIDs];
        
    for(EmailMessage em : trigger.new){ 
               
         // Account field is populated when an email is created on Opportunity  
            ​if(oppList.size() > 0){
                for(Opportunity o : oppList){
                    em.Account__c = o.AccountId;                
                }
            }
          // Account field is populated when an email is created on Account
            else if(accList.size() > 0){
                for(Account a : accList){
                    em.Account__c = a.Id;
                }
            }
       // Account field is not populated when an email is created on Case       
            else if(casList.size() > 0){
                for(Case ca : casList){
                    em.Account__c = ca.AccountId;                
                }
            }
            else{
                em.Account__c = null; 
            }  
    }
}
Regards,
Dmitry
 
This was selected as the best answer
Sylvie SerpletSylvie Serplet
Hi Dmitry,

Thank you for your reply and advice to restructure the Trigger.
Unfortunatly the Account field still does not populate in the email message when created from the Case.
Moreover I cannot even add the Account manually. When I try to save it, it just disappeared!
User-added image

User-added image
Any idea?
Thank you.
Sylvie
Alain CabonAlain Cabon
Hello Sylvie,

Dmitri's trigger should work (I have had the same idea and his trigger seems perfect) but did you set up Enchanced Email and Email-to-Case (first checks to do)?

From Setup, enter Enhanced Email in the Quick Find box, then select Enhanced Email.
  1. Click Enable.
  2. Update the Email Message page layout to:
  3. Add the Related To field.
Then, your users can see which records are related to an email.
Remove the Parent Case field from the Email Message page layout. This field is generally blank unless you use Email-to-Case and an email is associated with a case.

https://help.salesforce.com/articleView?id=enable_enhanced_email.htm&type=5
 
Sylvie SerpletSylvie Serplet
Thank you both. It works now.
Regards,
Sylvie