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
MLamb2005MLamb2005 

Issues with Trigger querying unrelated tables by Email

I'm writing a trigger to keep a persistent record of every Contact that has every opted out in a custom object called Opt Out Records. That way, even when they are deleted, and then imported again, they'll be marked "Opted Out" if they are in the Opt Out Records table. And any Contact that is marked Opted Out will have their email address written into the Opt Out Records table. We're keeping track of Opt Opts with a custom field called Communication Status.

 

So, what I have below works to an extent. It takes a batch of Contact records, picks up the entire Opt Out Records table (Issue #1), then rolls through looking for matches (Issue #2, related to #1). If a match is found, that Contact's Communication Status value is updated to "Opted Out". If a match isn't found, and the Contact is Opted Out, they are written into the Out Out Records table (stored and batch inserted, technically).

 

Issue #1: Pulling the entire Opt Out Records table doesn't make sense long term.

Issue#2: Neither does iterating in such a slow way through the Opt Out Records list for each incoming Contact. Eats up the governor very quickly.

 

So, my question, how do I more effectively identify which of the incoming Contacts' email addresses are already recorded in the Opt Out Records? I think that will solve both issues.

 

Thanks all. 

 

 

 

trigger manageContactOptOut on Contact (before update, before insert) {

    List<Contact> theContacts = new List<Contact>();
    for(Contact c : Trigger.new){
        theContacts.add(c); 
        theEmails.add(c.Email);
    }
    
    List<Opt_Out_Record__c> optOutList = new List<Opt_Out_Record__c>([SELECT Email__c from Opt_Out_Record__c]);

    Boolean foundOne = false;

    List<Opt_Out_Record__c> newOptOuts = new List<Opt_Out_Record__c>();

    for(Contact myContact : Trigger.new){        
        for(Integer i=0; i < optOutList.size(); i++){
            //We found their email address in the Opt Out Record object
            if(myContact.Email == optOutList[i].Email__c){
                myContact.Communication_Status__c = 'No - Opted-Out';
                foundOne = true;
            }
        }        
    
        //We did NOT find their email address in the Opt Out Record object
        //If the Communication Status = Opted Out, then add it to the tracking object
        if (foundOne == false && myContact.Communication_Status__c == 'No - Opted-Out') {
            Opt_Out_Record__c newOptOut = new Opt_Out_Record__c();
            newOptOut.Email__c = myContact.Email;
         
            newOptOuts.add(newOptOut);
        }
        
        foundOne = false;
    }
    
    try{
        insert newOptOuts;
    }catch (Exception ex) {
        //Oops
    }
}

 

 

Best Answer chosen by Admin (Salesforce Developers) 
MLamb2005MLamb2005

Thanks, your query set me on the right track. Got it working now. If anyone sees any potential pitfalls in what I have, I'd love to hear them. One I just saw is the single item delete statement, I should batch that.

 

Pretty handy utility, now my Sales team can delete any Contacts or Leads they see fit, and if any of the Opted Out ones are imported again in the future, they'll automatically be marked Opted Out. If someone Opts In, they're simply removed from the Opt Out table.

 

Any other enhancements I should build into that? 

 

 

 

trigger manageContactOptOut on Contact (before update, before insert) {

    //Assemble a SET of the incoming Contact email addresses
    Set<String> theEmails = new Set<String>();
    for(Contact c : Trigger.new){
        theEmails.add(c.Email);
    }
    
    //Query a LIST of all Opt Out Records that are in the incoming set
    List<Opt_Out_Record__c> optOutList = new List<Opt_Out_Record__c>([SELECT Email__c from Opt_Out_Record__c WHERE Email__c IN : theEmails]);
       
    //Assemble a SET of all emails in the incoming data which are already Opted Out
    //Also a MAP of Emails to their respective Opt Out Record to help with deleting
    Set<String> optOutEmails = new Set<String>();
    Map<String, Opt_Out_Record__c> optOutMap = new Map<String, Opt_Out_Record__c>();
    for(Integer i=0; i < optOutList.size(); i++){
        optOutEmails.add(optOutList[i].Email__c);
        optOutMap.put(optOutList[i].Email__c, optOutList[i]);
    }
    
    Boolean foundOne = false;
    Opt_Out_Record__c oor = new Opt_Out_Record__c();

    List<Opt_Out_Record__c> newOptOuts = new List<Opt_Out_Record__c>();

    for(Contact myContact : Trigger.new){        
        //We found their email address in the Opt Out Record object
        if(optOutEmails.contains(myContact.Email)){
            //If they are opting in, remove their email from the Opt Out Record table
            if(myContact.Communication_Status__c == 'Yes - Opted-In'){
                oor = optOutMap.get(myContact.Email);
                try{
                    delete oor;
                } catch (Exception ex){
                    System.debug (ex);
                }
            }
            //If they aren't opting in, reset their Communication Status to Opted Out
            else{
                myContact.Communication_Status__c = 'No - Opted-Out';
                foundOne = true;
            }
        }
    
        //We did NOT find their email address in the Opt Out Record object
        //If the Communication Status = Opted Out, then add it to the tracking object
        if (foundOne == false && myContact.Communication_Status__c == 'No - Opted-Out' && myContact.Email <> null) {
            Opt_Out_Record__c newOptOut = new Opt_Out_Record__c();
            newOptOut.Email__c = myContact.Email;
            newOptOuts.add(newOptOut);
        }
        foundOne = false;
    }
    
    System.debug(newOptOuts.size());
    
    try{
        insert newOptOuts;
    }catch (Exception ex) {
        System.debug(ex);
    }
}

 

 

All Answers

dmchengdmcheng

If theEmails is a Set, you could do you SOQL query this way:

SELECT Email__c from Opt_Out_Record__c where Email__c in :theEmails

then your double for-loop:

 

for(Contact con : Trigger.new) {
	for(Opt_Out_Record__c oor : optOutList) {
		if(con.Email == oor.Email__c) {
		}
	}
}

 

But is it possible to make contacts Inactive rather than deleting them?  Then you could avoid some of these problems.

 
MLamb2005MLamb2005

Thanks, your query set me on the right track. Got it working now. If anyone sees any potential pitfalls in what I have, I'd love to hear them. One I just saw is the single item delete statement, I should batch that.

 

Pretty handy utility, now my Sales team can delete any Contacts or Leads they see fit, and if any of the Opted Out ones are imported again in the future, they'll automatically be marked Opted Out. If someone Opts In, they're simply removed from the Opt Out table.

 

Any other enhancements I should build into that? 

 

 

 

trigger manageContactOptOut on Contact (before update, before insert) {

    //Assemble a SET of the incoming Contact email addresses
    Set<String> theEmails = new Set<String>();
    for(Contact c : Trigger.new){
        theEmails.add(c.Email);
    }
    
    //Query a LIST of all Opt Out Records that are in the incoming set
    List<Opt_Out_Record__c> optOutList = new List<Opt_Out_Record__c>([SELECT Email__c from Opt_Out_Record__c WHERE Email__c IN : theEmails]);
       
    //Assemble a SET of all emails in the incoming data which are already Opted Out
    //Also a MAP of Emails to their respective Opt Out Record to help with deleting
    Set<String> optOutEmails = new Set<String>();
    Map<String, Opt_Out_Record__c> optOutMap = new Map<String, Opt_Out_Record__c>();
    for(Integer i=0; i < optOutList.size(); i++){
        optOutEmails.add(optOutList[i].Email__c);
        optOutMap.put(optOutList[i].Email__c, optOutList[i]);
    }
    
    Boolean foundOne = false;
    Opt_Out_Record__c oor = new Opt_Out_Record__c();

    List<Opt_Out_Record__c> newOptOuts = new List<Opt_Out_Record__c>();

    for(Contact myContact : Trigger.new){        
        //We found their email address in the Opt Out Record object
        if(optOutEmails.contains(myContact.Email)){
            //If they are opting in, remove their email from the Opt Out Record table
            if(myContact.Communication_Status__c == 'Yes - Opted-In'){
                oor = optOutMap.get(myContact.Email);
                try{
                    delete oor;
                } catch (Exception ex){
                    System.debug (ex);
                }
            }
            //If they aren't opting in, reset their Communication Status to Opted Out
            else{
                myContact.Communication_Status__c = 'No - Opted-Out';
                foundOne = true;
            }
        }
    
        //We did NOT find their email address in the Opt Out Record object
        //If the Communication Status = Opted Out, then add it to the tracking object
        if (foundOne == false && myContact.Communication_Status__c == 'No - Opted-Out' && myContact.Email <> null) {
            Opt_Out_Record__c newOptOut = new Opt_Out_Record__c();
            newOptOut.Email__c = myContact.Email;
            newOptOuts.add(newOptOut);
        }
        foundOne = false;
    }
    
    System.debug(newOptOuts.size());
    
    try{
        insert newOptOuts;
    }catch (Exception ex) {
        System.debug(ex);
    }
}

 

 

This was selected as the best answer