function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Benjamin LouisBenjamin Louis 

Trigger - Count string occurrence in outgoing case emails.

Hi Guys,

I would kindly like to ask for your help. I am a beginner Apex student, but I am stuck with a trigger :(.
I created a custom field on the case SObject which should serve as a counter.
The trigger should count the string occurences in only outgoing emails and return the # value to this field.
Basically what I want is there are negative words and positive ones (counter should be like positive - negative found words)

This is how far I got:

trigger CaseEmailScore on Case (after insert, after update) {
  
    // Word criteria table,List
    List<String> positiveWords = new List<String>();
    positiveWords.add('Dear');
    positiveWords.add('Kind');
    positiveWords.add('Dreamforce');
    positiveWords.add('Kind regards,');
    positiveWords.add('Best regards,');
    
    List<String> negativeWords = new List<String>();
    negativeWords.add('wtf');
    negativeWords.add('I\'m');
    negativeWords.add('stupid');
    
    for (Case myCase : Trigger.new) {
       
    // Create list of positive emails which are within cases
    List<EmailMessage> caseEmails = [ SELECT  Id,
                                                                               HtmlBody,
                                                                               TextBody,
                                                                               FromAddress,
                                                                               ParentId
                                                               FROM   EmailMessage
                                                            WHERE  ParentId != null];
                                                               //AND  TextBody LIKE :positiveWords]; -> ERROR: TextBody and Html can not be filtered in query call    
        }
    }                                        
    
    //Counting calculations possible .countMatches()


I would really appreciate if you could help, so I can understand and continue learning :).



 
Best Answer chosen by Benjamin Louis
Jesper Klang.Jesper Klang.
Hi again,

I made a new version that includes a fix for the NullPointerException, and wherre the word look-up is not case-sensitive anymore. Included a test class while I was on it as well.

Trigger: 
trigger CaseEmailOutboundIncentive on EmailMessage (after insert, after update) {
    List<String> positiveWords = new List<String>{'Dear', 'Kind', 'Dreamforce', 'Kind regards', 'Best regards'};
    public List<String> negativeWords = new List<String>{'wtf', 'I\'m', 'stupid'};
    List<Id> caseIDs = new List<Id>();
    List<Case> casesToUpdate = new List<Case>();
    Integer countPositives;
    Integer countNegatives;
    // Get the Case IDs for referencing in the SOQL query
    for (EmailMessage getCase :Trigger.New){
        if (!getCase.Incoming) {
            caseIDs.add(getCase.ParentId);
        }
    }
    // Get Case and all related triggered email messages
    List<Case> casesWEmails = [SELECT Positive_Email_Words__c, Negative_Email_Words__c, (SELECT TextBody, ParentId FROM EmailMessages WHERE Incoming = False AND Id IN :Trigger.New) FROM Case WHERE Id IN :caseIDs];
    System.debug('casesWEmails: ' + casesWEmails);
    // Loop through all Cases first
    for (Case c : casesWEmails) {
        // Prevent null errors by replacing null with 0. This is because you can't add integers to a null value
        if (c.Positive_Email_Words__c == null){
            c.Positive_Email_Words__c = 0;
        }
        if (c.Negative_Email_Words__c == null){
            c.Negative_Email_Words__c = 0;
        }
        // reset variables to zero
        countPositives = 0;
        countNegatives = 0;
        // Loop through all related email that's triggered
        for (EmailMessage email :c.EmailMessages){
            // Loop trough all positive words to see if they are in the email
            for (String pW :positiveWords){
                if (email.TextBody.containsIgnoreCase(pW)){
                    countPositives++;
                }
            }
            // Loop trough all negative words to see if they are in the email
            for (String nW :negativeWords){
                if (email.TextBody.containsIgnoreCase(nW)){
                    countNegatives++;
                }
            }
        }
        // Add to a list of Cases to be updated. Here it adds the new values on top of the once that may already have been on the Case.
        System.debug('countPositives: ' + countPositives + ' countNegatives: ' + countNegatives + ' c.Positive_Email_Words__c: ' + c.Positive_Email_Words__c + ' c.Negative_Email_Words__c: ' + c.Negative_Email_Words__c);
        casesToUpdate.add(new Case(Id = c.Id, Positive_Email_Words__c = c.Positive_Email_Words__c + countPositives, Negative_Email_Words__c = c.Negative_Email_Words__c + countNegatives));
    }
    // Update Cases
    update casesToUpdate;
}
Test class (100% coverage) 
@isTest
public class CaseEmailOutboundIncentiveTest {
    @isTest
    public static void TestWithNoPosAndNegOnCase(){
        Case c = new Case(Subject = 'Test', Origin = 'Web');
        Insert c;
        // Create 1 Case with 3 EmailMessages
        List<EmailMessage> eMessages = new List<EmailMessage>();
        for (Integer i = 0; i<3; i++) {
            // OBS! Make sure the body contains 1 Positive word and 2 Negative words
            eMessages.add(new EmailMessage(ParentId = c.Id, Subject = 'Test ' + i, TextBody = 'Dear WTF Stupid', ToAddress = 'jesper.klang@outlook.com', Incoming = False, FromAddress = 'jesper.klang@st1.se', FromName = 'Jesper'));
        }

        Test.startTest();
        Insert eMessages;
        Test.stopTest();

        Case getCase = [SELECT Positive_Email_Words__c, Negative_Email_Words__c FROM Case Where Id = :c.Id];
        // 1 Positive word * 3 Emails = 3
        System.assertEquals(3, getCase.Positive_Email_Words__c);
        // 2 Negative words * 3 Emails = 6
        System.assertEquals(6, getCase.Negative_Email_Words__c);
    }
    @isTest
    public static void TestWithPosAndNegOnCase(){
        Case c = new Case(Subject = 'Test', Origin = 'Web', Positive_Email_Words__c = 2, Negative_Email_Words__c = 1);
        Insert c;
        // Create 1 Case with 3 EmailMessages
        List<EmailMessage> eMessages = new List<EmailMessage>();
        for (Integer i = 0; i<3; i++) {
            // OBS! Make sure the body contains 1 Positive word and 2 Negative words
            eMessages.add(new EmailMessage(ParentId = c.Id, Subject = 'Test ' + i, TextBody = 'Dear WTF Stupid', ToAddress = 'jesper.klang@outlook.com', Incoming = False, FromAddress = 'jesper.klang@st1.se', FromName = 'Jesper'));
        }
        
        Test.startTest();
        Insert eMessages;
        Test.stopTest();

        Case getCase = [SELECT Positive_Email_Words__c, Negative_Email_Words__c FROM Case Where Id = :c.Id];
        // 1 Positive word * 3 Emails + 2 that was already on the Case = 5
        System.assertEquals(5, getCase.Positive_Email_Words__c);
        // 2 Negative words * 3 Emails + 1 that was already on the Case = 7
        System.assertEquals(7, getCase.Negative_Email_Words__c);
    }
}

 

All Answers

Jesper Klang.Jesper Klang.
Hi Mark,

I would do it with EmailMessage as the triggered object instead of Case. This way we will only calculate on the actually triggered emails rather than process all the related emails on the Case that is triggered. Also since the Case isn't updated when a email is sent (without anything custom automation), this way it is always updated as soon as a new email is sent from a Case.

Please test it out and don't forget to change the question to Solved and select Best Answer if this would solve your issue.

Pssst. Replace Positive_Email_Words__c and Negative_Email_Words__c to the fields you created on Case already. 
trigger CaseEmailScoreNegPosTrig on EmailMessage (after insert, after update) {
    List<String> positiveWords = new List<String>{'Dear', 'Kind', 'Dreamforce', 'Kind regards', 'Best regards'};
    List<String> negativeWords = new List<String>{'wtf', 'I\'m', 'stupid'};
    List<Id> caseIDs = new List<Id>();
    List<Case> casesToUpdate = new List<Case>();
    Integer countPositives;
    Integer countNegatives;
    // Get the Case IDs for referencing in the SOQL query
    for (EmailMessage getCase :Trigger.New){
        if (!getCase.Incoming) {
            caseIDs.add(getCase.ParentId);
        }
    }
    // Get Case and all related triggered email messages
    List<Case> casesWEmails = [SELECT Positive_Email_Words__c, Negative_Email_Words__c, (SELECT TextBody, ParentId FROM EmailMessages WHERE Incoming = False AND Id IN :Trigger.New) FROM Case WHERE Id IN :caseIDs];
    System.debug('casesWEmails: ' + casesWEmails);
    // Loop through all Cases first
    for (Case c : casesWEmails) {
        // reset variables to zero
        countPositives = 0;
        countNegatives = 0;
        // Loop through all related email that's triggered
        for (EmailMessage email :c.EmailMessages){
            // Loop trough all positive words to see if they are in the email
            for (String pW :positiveWords){
                if (email.TextBody.contains(pW)){
                    countPositives++;
                }
            }
            // Loop trough all negative words to see if they are in the email
            for (String nW :negativeWords){
                if (email.TextBody.contains(nW)){
                    countNegatives++;
                }
            }
        }
        // Add to a list of Cases to be updated. Here it adds the new values on top of the once that may already have been on the Case.
        System.debug('countPositives: ' + countPositives + ' countNegatives: ' + countNegatives + ' c.Positive_Email_Words__c: ' + c.Positive_Email_Words__c + ' c.Negative_Email_Words__c: ' + c.Negative_Email_Words__c);
        casesToUpdate.add(new Case(Id = c.Id, Positive_Email_Words__c = c.Positive_Email_Words__c + countPositives, Negative_Email_Words__c = c.Negative_Email_Words__c + countNegatives));
    }
    // Update Cases
    update casesToUpdate;
}

 
Benjamin LouisBenjamin Louis
Hi Jesper,

Awesome code, I still need to learn a few things I guess :P! I can see the counting works perfectly, but I get a NullPointerException on line 39, 
can you please help, how to get rid of it? I think it's cause the c.Positive_Email_Words__c is actually null, and can not add to a null value.

Thank you so much!
Benjamin LouisBenjamin Louis
Hi Jesper,

Nevermind, I solved the NullPointerException , set default value of Positive_Email_Words and Negative_Email_Words to 0 :).
Thanks again :)!

 
Jesper Klang.Jesper Klang.
Great to hear Mark, sounds like a good solution! I'm learning too, this error can be an annoying one ;)
Jesper Klang.Jesper Klang.
Hi again,

I made a new version that includes a fix for the NullPointerException, and wherre the word look-up is not case-sensitive anymore. Included a test class while I was on it as well.

Trigger: 
trigger CaseEmailOutboundIncentive on EmailMessage (after insert, after update) {
    List<String> positiveWords = new List<String>{'Dear', 'Kind', 'Dreamforce', 'Kind regards', 'Best regards'};
    public List<String> negativeWords = new List<String>{'wtf', 'I\'m', 'stupid'};
    List<Id> caseIDs = new List<Id>();
    List<Case> casesToUpdate = new List<Case>();
    Integer countPositives;
    Integer countNegatives;
    // Get the Case IDs for referencing in the SOQL query
    for (EmailMessage getCase :Trigger.New){
        if (!getCase.Incoming) {
            caseIDs.add(getCase.ParentId);
        }
    }
    // Get Case and all related triggered email messages
    List<Case> casesWEmails = [SELECT Positive_Email_Words__c, Negative_Email_Words__c, (SELECT TextBody, ParentId FROM EmailMessages WHERE Incoming = False AND Id IN :Trigger.New) FROM Case WHERE Id IN :caseIDs];
    System.debug('casesWEmails: ' + casesWEmails);
    // Loop through all Cases first
    for (Case c : casesWEmails) {
        // Prevent null errors by replacing null with 0. This is because you can't add integers to a null value
        if (c.Positive_Email_Words__c == null){
            c.Positive_Email_Words__c = 0;
        }
        if (c.Negative_Email_Words__c == null){
            c.Negative_Email_Words__c = 0;
        }
        // reset variables to zero
        countPositives = 0;
        countNegatives = 0;
        // Loop through all related email that's triggered
        for (EmailMessage email :c.EmailMessages){
            // Loop trough all positive words to see if they are in the email
            for (String pW :positiveWords){
                if (email.TextBody.containsIgnoreCase(pW)){
                    countPositives++;
                }
            }
            // Loop trough all negative words to see if they are in the email
            for (String nW :negativeWords){
                if (email.TextBody.containsIgnoreCase(nW)){
                    countNegatives++;
                }
            }
        }
        // Add to a list of Cases to be updated. Here it adds the new values on top of the once that may already have been on the Case.
        System.debug('countPositives: ' + countPositives + ' countNegatives: ' + countNegatives + ' c.Positive_Email_Words__c: ' + c.Positive_Email_Words__c + ' c.Negative_Email_Words__c: ' + c.Negative_Email_Words__c);
        casesToUpdate.add(new Case(Id = c.Id, Positive_Email_Words__c = c.Positive_Email_Words__c + countPositives, Negative_Email_Words__c = c.Negative_Email_Words__c + countNegatives));
    }
    // Update Cases
    update casesToUpdate;
}
Test class (100% coverage) 
@isTest
public class CaseEmailOutboundIncentiveTest {
    @isTest
    public static void TestWithNoPosAndNegOnCase(){
        Case c = new Case(Subject = 'Test', Origin = 'Web');
        Insert c;
        // Create 1 Case with 3 EmailMessages
        List<EmailMessage> eMessages = new List<EmailMessage>();
        for (Integer i = 0; i<3; i++) {
            // OBS! Make sure the body contains 1 Positive word and 2 Negative words
            eMessages.add(new EmailMessage(ParentId = c.Id, Subject = 'Test ' + i, TextBody = 'Dear WTF Stupid', ToAddress = 'jesper.klang@outlook.com', Incoming = False, FromAddress = 'jesper.klang@st1.se', FromName = 'Jesper'));
        }

        Test.startTest();
        Insert eMessages;
        Test.stopTest();

        Case getCase = [SELECT Positive_Email_Words__c, Negative_Email_Words__c FROM Case Where Id = :c.Id];
        // 1 Positive word * 3 Emails = 3
        System.assertEquals(3, getCase.Positive_Email_Words__c);
        // 2 Negative words * 3 Emails = 6
        System.assertEquals(6, getCase.Negative_Email_Words__c);
    }
    @isTest
    public static void TestWithPosAndNegOnCase(){
        Case c = new Case(Subject = 'Test', Origin = 'Web', Positive_Email_Words__c = 2, Negative_Email_Words__c = 1);
        Insert c;
        // Create 1 Case with 3 EmailMessages
        List<EmailMessage> eMessages = new List<EmailMessage>();
        for (Integer i = 0; i<3; i++) {
            // OBS! Make sure the body contains 1 Positive word and 2 Negative words
            eMessages.add(new EmailMessage(ParentId = c.Id, Subject = 'Test ' + i, TextBody = 'Dear WTF Stupid', ToAddress = 'jesper.klang@outlook.com', Incoming = False, FromAddress = 'jesper.klang@st1.se', FromName = 'Jesper'));
        }
        
        Test.startTest();
        Insert eMessages;
        Test.stopTest();

        Case getCase = [SELECT Positive_Email_Words__c, Negative_Email_Words__c FROM Case Where Id = :c.Id];
        // 1 Positive word * 3 Emails + 2 that was already on the Case = 5
        System.assertEquals(5, getCase.Positive_Email_Words__c);
        // 2 Negative words * 3 Emails + 1 that was already on the Case = 7
        System.assertEquals(7, getCase.Negative_Email_Words__c);
    }
}

 
This was selected as the best answer
Benjamin LouisBenjamin Louis
Hi Jesper,

I guess it can't get any better :) , really appreciate your help, test class is also working perfectly!
Thank you so much!