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
Justin.WilliamsJustin.Williams 

Too many nested for statements and queries.

I am trying to get this trigger to be as efficient as possible.  I can get it to work enough but to get the code coverage I change it slightyly in a way that causes too many SOQL queries.  Its pretty obvious why as I have three for statemenets nested with SOQL queries in them.  Up till now thats the only way I know how to do it.  I think I am supposed to move those queires to Maps but I don't know how to conceptually work with maps.

 

WHAT IT DOES:

When some fields are filled in on the opp it takes those values and insertes them into a new record related to the contact record listed as primary contact on the opp. 

HOW I SEE THE PROCESS

1. Trigger an opp cause the Credit Card fields are filled in

2. find the contact ID listed as priamry contact on the Opportunity Contact Roles

3. Create a new payment method record, fill values from the opp and set the ContactID to whoever was the primary contact Role on the Opp.

 

I think I need to approach my code totally differently.

trigger PaymentMethodCreate on Opportunity (after update) {
 
    try {
        List<Id> list_Opps = new List<Id>();
 
        List<Id> Prime_Contact = new List<Id>();
        ID PrimeContact;
        List<pymt__Payment_Method__c> NewMethod = new List<pymt__Payment_Method__c>();
        
        for (Opportunity NewCard: Trigger.New) {
            if ((NewCard.CC__c != Null && 
                NewCard.CC_Expiration_Month__c != Null &&
                NewCard.CC_Expiration_Year__c !=Null) &&
                (trigger.oldmap.get(NewCard.Id).CC__c != NewCard.CC__c ||
                trigger.oldmap.get(NewCard.Id).CC_Expiration_Month__c != NewCard.CC_Expiration_Month__c ||
                trigger.oldmap.get(NewCard.Id).CC_Expiration_Year__c != NewCard.CC_Expiration_Year__c)){
                    list_Opps.add(NewCard.ID);
                }
        }
 
        for (Opportunity CardInfo:[SELECT Id, Credit_Card_Notes__c, CC__c, Card_Type__c, Name_On_CC__c, CC_Expiration_Month__c, CC_Expiration_Year__c FROM Opportunity WHERE Id in :List_Opps]) {
            for(OpportunityContactRole PrimaryContact:[SELECT ContactId FROM OpportunityContactRole WHERE OpportunityId = :CardInfo.Id]){
                Prime_Contact.add(PrimaryContact.ContactID);
                IF(Prime_Contact.size() < 1 ){
                    CardInfo.Credit_Card_Notes__c = 'There are no primary contacts listed in the Opportunity Contact Role section to apply a payment method.';
                    update CardInfo;
                } else {
                    for(Contact Payee:[SELECT Id, MailingStreet, MailingCity, MailingState, MailingPostalCode, MailingCountry, Phone, Email FROM Contact WHERE Id = :PrimaryContact.ContactId]){
                        //This section sets a name to the TempC__c field if it is null because 
                        //it won't work if it is null.
                        String FullName;
                        IF(CardInfo.Name_On_CC__c == null){
                            FullName = 'NoName';
                        } else {
                            FullName = CardInfo.Name_On_CC__c;
                        }
            
                        //The rest of the variables that will be used are set here.
                        Integer Index = FullName.indexOf(' ',0);
                        String Prefix;
                        String FName;
                        String LName;
                        String SubName;            
            
                        //First check if a common salutation is on the name to pull it out and set the Prefix variable with it. 
                        //Then strip it of the prefix to create a substring of the name without the prefix.
                        IF((FullName.left(Index) == 'Mr.') || 
                           (FullName.left(Index) == 'Mr') || 
                           (FullName.left(Index) == 'Mrs.') || 
                           (FullName.left(Index) == 'Mrs') || 
                           (FullName.left(Index) == 'Ms.') || 
                           (FullName.left(Index) == 'Ms') || 
                           (FullName.left(Index) == 'Dr.') || 
                           (FullName.left(Index) == 'Dr')){
                               Prefix = FullName.left(Index);
                               SubName = FullName.substring(Index + 1,FullName.length());
                               Index = SubName.indexOf(' ',0);
                    
                               //This sub-IF statement exists to process different logic depending 
                               //if there is one or more words after scurbbing the salutation.
                               IF(SubName.contains(' ')){
                                   FName = SubName.left(Index);
                                   LName = SubName.substring(Index,SubName.length());
                                   } else{
                                       FName = '';
                                       LName = SubName;
                                   }
                    
                               //This other half of the if statement processes like the other but 
                               //without the logic to deal with a salutation since there ins't one.
                               //However there may actually be a salutation if it is not one of the 
                               //common ones included in the first If Statement.
                           } else {
                               Prefix = '';
                               IF(FullName.contains(' ')){
                     
                                   FName = FullName.left(Index);
                                   LName = FullName.substring(Index,FullName.length());
                               } else{
                                   FName = '';
                                   LName = FullName;
                               }
                           }
            
                        //Now that the variables for the name has been procecced for different situations the 
                        //next code will create a new payment method record.
                        pymt__Payment_Method__c Method = new pymt__Payment_Method__c();
                        Method.Name = CardInfo.Card_Type__c + ' (' + CardInfo.CC__C.right(4) + ')';
                        Method.pymt__Contact__c = Payee.Id;
                        Method.pymt__Type__c = 'Credit Card';
                        Method.pymt__Card_Type__c = CardInfo.Card_Type__c;
                        Method.pymt__Billing_Salutation__c = Prefix;
                        Method.pymt__Billing_First_Name__c = FName;
                        Method.pymt__Billing_Last_Name__c = LName;
                        Method.pymt__Last_4_Digits__c = CardInfo.CC__C.right(4);
                        Method.ccrd__Card_Number__c = CardInfo.CC__C;
                        Method.pymt__Expiration_Month__c = CardInfo.CC_Expiration_Month__c;
                        Method.pymt__Expiration_Year__c = CardInfo.CC_Expiration_Year__c;
                        Method.pymt__Billing_Street__c = Payee.MailingStreet;
                        Method.pymt__Billing_City__c = Payee.MailingCity;
                        Method.pymt__Billing_State__c = Payee.MailingState;
                        Method.pymt__Billing_Postal_Code__c = Payee.MailingPostalCode;
                        Method.pymt__Billing_Country__c = Payee.MailingCountry;
                        Method.pymt__Billing_Phone__c = Payee.Phone;
                        Method.pymt__Billing_Email__c = Payee.Email;
                        Method.pymt__Default__c = true;
                        NewMethod.add(Method);
                    }
                    insert NewMethod;
                       
                        
                    //Now that the card info is securly stored inside the payment method record and is encryped
                    //we can clear out the exposed CC info on the opportunity.
                    CardInfo.CC__C = '';
                    CardInfo.Name_On_CC__c = '';
                    CardInfo.CC_Expiration_Month__c = '';
                    CardInfo.CC_Expiration_Year__c = '';
                    CardInfo.Card_Type__c = '';
                    CardInfo.Credit_Card_Notes__c = 'Credit Card was stored in a related Payment Method and is ready to use.  Go to the Payment Terminal and apply the existing payment method';
                                 Update CardInfo;
            }
        }
    }
}catch (Exception e) {Trigger.new[0].addError(e.getMessage());}
}

 

 

Best Answer chosen by Admin (Salesforce Developers) 
SfdcStevenSfdcSteven
Three nested select statements is problematic, especially when the information is available all in one query using aggregates
 
  for (Opportunity CardInfo:[SELECT Id, Credit_Card_Notes__c, CC__c, Card_Type__c, Name_On_CC__c, CC_Expiration_Month__c, CC_Expiration_Year__c FROM Opportunity WHERE Id in :List_Opps]) {
            for(OpportunityContactRole PrimaryContact:[SELECT ContactId FROM OpportunityContactRole WHERE OpportunityId = :CardInfo.Id]){
//...
                    for(Contact Payee:[SELECT Id, MailingStreet, MailingCity, MailingState, MailingPostalCode, MailingCountry, Phone, Email FROM Contact WHERE Id = :PrimaryContact.ContactId]){

 

Something like this should work by using the OpportunityContactRoles aggregate relationship, and the referencing the parent query. 

 
  for (Opportunity CardInfo:[SELECT Id, Credit_Card_Notes__c, CC__c, Card_Type__c, Name_On_CC__c, CC_Expiration_Month__c, CC_Expiration_Year__c, (Select Contact.MailingStreet, Contact.MailingCity, Contact.MailingState, Contact.MailingPostalCode, Contact.MailingCountry,Contact.Phone,Contact.Email FROM OpportunityContactRoles) FROM Opportunity WHERE Id in :List_Opps]) {
    // Check for CardInfo.OpportuntiyContactRoles.size() == 0
    for (OpportunityContactRole PayeeRole : CardInfo.OpportunityContactRoles) {
        Contact Payee = PayeeRole.Contact

 

 

All Answers

SfdcStevenSfdcSteven
Three nested select statements is problematic, especially when the information is available all in one query using aggregates
 
  for (Opportunity CardInfo:[SELECT Id, Credit_Card_Notes__c, CC__c, Card_Type__c, Name_On_CC__c, CC_Expiration_Month__c, CC_Expiration_Year__c FROM Opportunity WHERE Id in :List_Opps]) {
            for(OpportunityContactRole PrimaryContact:[SELECT ContactId FROM OpportunityContactRole WHERE OpportunityId = :CardInfo.Id]){
//...
                    for(Contact Payee:[SELECT Id, MailingStreet, MailingCity, MailingState, MailingPostalCode, MailingCountry, Phone, Email FROM Contact WHERE Id = :PrimaryContact.ContactId]){

 

Something like this should work by using the OpportunityContactRoles aggregate relationship, and the referencing the parent query. 

 
  for (Opportunity CardInfo:[SELECT Id, Credit_Card_Notes__c, CC__c, Card_Type__c, Name_On_CC__c, CC_Expiration_Month__c, CC_Expiration_Year__c, (Select Contact.MailingStreet, Contact.MailingCity, Contact.MailingState, Contact.MailingPostalCode, Contact.MailingCountry,Contact.Phone,Contact.Email FROM OpportunityContactRoles) FROM Opportunity WHERE Id in :List_Opps]) {
    // Check for CardInfo.OpportuntiyContactRoles.size() == 0
    for (OpportunityContactRole PayeeRole : CardInfo.OpportunityContactRoles) {
        Contact Payee = PayeeRole.Contact

 

 

This was selected as the best answer
Justin.WilliamsJustin.Williams

Awesome, I am so close.  When I try the section to see if there are any OppContactRoles I still get the SOQL Limit Which is odd cause there is only the one inside the first For Loop.  If I change the search criteria from == 0 to <1 or == NULL I no longer get the error and works when there is a contact role, but if there is not contact role it doesn't seem to know that and doesn't do anything.  No error but no work done either.  Here is the code as I have it that provides the SOQL Limit error.

 

trigger PaymentMethodCreate on Opportunity (after update) {
    try {
        List<Id> list_Opps = new List<Id>();
        List<Id> Prime_Contact = new List<Id>();
        List<pymt__Payment_Method__c> NewMethod = new List<pymt__Payment_Method__c>();
        
        for (Opportunity NewCard: Trigger.New) {
            if ((NewCard.CC__c != Null && 
                NewCard.CC_Expiration_Month__c != Null &&
                NewCard.CC_Expiration_Year__c !=Null) &&
                (trigger.oldmap.get(NewCard.Id).CC__c == Null ||
                trigger.oldmap.get(NewCard.Id).CC_Expiration_Month__c == Null ||
                trigger.oldmap.get(NewCard.Id).CC_Expiration_Year__c == Null)){
                    list_Opps.add(NewCard.ID);
                }
        }

        for (Opportunity CardInfo:[SELECT Id, 
                                   Credit_Card_Notes__c, 
                                   CC__c, Card_Type__c, 
                                   Name_On_CC__c, 
                                   CC_Expiration_Month__c, 
                                   CC_Expiration_Year__c,
                                   SecCode__c,
                                   (SELECT Contact.MailingStreet, 
                                    Contact.MailingCity, 
                                    Contact.MailingState, 
                                    Contact.MailingPostalCode, 
                                    Contact.MailingCountry,
                                    Contact.Phone,
                                    Contact.Email FROM OpportunityContactRoles WHERE isprimary = TRUE) 
                                   FROM Opportunity WHERE Id in :List_Opps]) {
            
                                       // Check for CardInfo.OpportuntiyContactRoles.size() == 0
                                       for (OpportunityContactRole PayeeRole : CardInfo.OpportunityContactRoles) {
                                           Contact Payee = PayeeRole.Contact;
                                           IF(CardInfo.OpportunityContactRoles.size()==0){
                                               CardInfo.Credit_Card_Notes__c = 'There are no primary contacts listed in the Opportunity Contact Role section to apply a payment method.';
                                               update CardInfo;
                                           } else {
                                               String FullName;
                                               IF(CardInfo.Name_On_CC__c == null){
                                                   FullName = 'NoName';
                                               } else {
                                                   FullName = CardInfo.Name_On_CC__c;
                                               }
                    
                //The rest of the variables that will be used are set here.
                Integer Index = FullName.indexOf(' ',0);
                String Prefix;
                String FName;
                String LName;
                String SubName;            
            
                //First check if a common salutation is on the name to pull it out and set the Prefix variable with it. 
                //Then strip it of the prefix to create a substring of the name without the prefix.
                IF((FullName.left(Index) == 'Mr.') || 
                   (FullName.left(Index) == 'Mr') || 
                   (FullName.left(Index) == 'Mrs.') || 
                   (FullName.left(Index) == 'Mrs') || 
                   (FullName.left(Index) == 'Ms.') || 
                   (FullName.left(Index) == 'Ms') || 
                   (FullName.left(Index) == 'Dr.') || 
                   (FullName.left(Index) == 'Dr')){
                       Prefix = FullName.left(Index);
                       SubName = FullName.substring(Index + 1,FullName.length());
                       Index = SubName.indexOf(' ',0);
                
                       //This sub-IF statement exists to process different logic depending 
                       //if there is one or more words after scurbbing the salutation.
                       IF(SubName.contains(' ')){
                           FName = SubName.left(Index);
                           LName = SubName.substring(Index,SubName.length());
                           } else{
                               FName = '';
                               LName = SubName;    
                           }
                       //This other half of the if statement processes like the other but 
                       //without the logic to deal with a salutation since there ins't one.
                       //However there may actually be a salutation if it is not one of the 
                       //common ones included in the first If Statement.        
                   } else {
                       Prefix = '';
                       IF(FullName.contains(' ')){
                           FName = FullName.left(Index);
                           LName = FullName.substring(Index,FullName.length());
                       } else{
                           FName = '';
                           LName = FullName;
                       }  
                   }
            
                //Now that the variables for the name has been procecced for different situations the 
                //next code will create a new payment method record.
                pymt__Payment_Method__c Method = new pymt__Payment_Method__c();
                Method.Name = CardInfo.Card_Type__c + ' (' + CardInfo.CC__C.right(4) + ')';
                Method.pymt__Contact__c = Payee.ID;
                Method.pymt__Type__c = 'Credit Card';
                Method.pymt__Card_Type__c = CardInfo.Card_Type__c;
                Method.pymt__Billing_Salutation__c = Prefix;
                Method.pymt__Billing_First_Name__c = FName;
                Method.pymt__Billing_Last_Name__c = LName;
                Method.pymt__Billing_City__c = payee.MailingCity;
                Method.pymt__Billing_Street__c = payee.MailingStreet;
                Method.pymt__Billing_State__c = payee.MailingState;
                Method.pymt__Billing_Postal_Code__c = payee.MailingPostalCode;
                Method.pymt__Last_4_Digits__c = CardInfo.CC__C.right(4);
                Method.ccrd__Card_Number__c = CardInfo.CC__C;
                Method.Sec_Code__c = CardInfo.SecCode__c;
                Method.pymt__Expiration_Month__c = CardInfo.CC_Expiration_Month__c;
                Method.pymt__Expiration_Year__c = CardInfo.CC_Expiration_Year__c;
                insert Method;
            }
                
            //Now that the card info is securly stored inside the payment method record and is encryped
            //we can clear out the exposed CC info on the opportunity.
            CardInfo.CC__C = '';
            CardInfo.Name_On_CC__c = '';
            CardInfo.CC_Expiration_Month__c = '';
            CardInfo.CC_Expiration_Year__c = '';
            CardInfo.Card_Type__c = '';
            CardInfo.Credit_Card_Notes__c = 'Credit Card was stored in a related Payment Method and is ready to use.  Go to the Payment Terminal and apply the existing payment method';       
            Update CardInfo;
        }
    }
    }catch (Exception e) {Trigger.new[0].addError(e.getMessage());}
}

   

 

Again, if I change the line that says, "IF(CardInfo.OpportunityContactRoles.size() < 1 ){" or "IF(CardInfo.OpportunityContactRoles.size()==NULL){" the test code passes with 90% coverage.  If I run with no opp contact role then no payment method will get created, the data on the opportunity doesn't get erased, no message is published on the text field on the opp and no error message.  If there is an opp contact role everything runs perfect.

Justin.WilliamsJustin.Williams

Turns out what the test class is running agains are SOQL queries from other triggers I have that use nested For statements and queries.  I will have to go into those and try and fix them to reduce the dependence on nested queries.  Thank you.!

Justin.WilliamsJustin.Williams

Thanks for you help.  It is so close to working.  Everything works except if I am missing a contact role.  The part where it should check if there are any contact roles that are 'Primary' doesn't seem to work.  When I have contact roles they work fine.  If they don't we are back to nothing happeing.  No errors, no message being displayed in the text box, nothing.

 

trigger PaymentMethodCreate on Opportunity (after update) {
    try {
        //First we set some variables that will be used later in the code.
        List<Id> list_Opps = new List<Id>();
        List<Id> Prime_Contact = new List<Id>();
        List<pymt__Payment_Method__c> newMethods = new List<pymt__Payment_Method__c>();
        
        //This is the criteria for the trigger to happen. Its checks for the card number and the 
        //expiration dats to be filled out as they are the most important part of the payment.
        //There is code to handle if the name is missing and SecCode isn't actually required
        //to take payement right now but is stored in case that changes in the future.
        for (Opportunity NewCard: Trigger.New) {
            if ((NewCard.CC__c != Null && 
                NewCard.CC_Expiration_Month__c != Null &&
                NewCard.CC_Expiration_Year__c !=Null) &&
                (trigger.oldmap.get(NewCard.Id).CC__c == Null ||
                trigger.oldmap.get(NewCard.Id).CC_Expiration_Month__c == Null ||
                trigger.oldmap.get(NewCard.Id).CC_Expiration_Year__c == Null)){
                    list_Opps.add(NewCard.ID);
                }
        }

        //Now we create a left outer join on opportunities and contact roles.  Also for the contact roles they pull parent information from the contact.
        //The list of opportunities are filtered by whatever is in 'List_Opps' which is the variable above.
        for (Opportunity CardInfo:[SELECT Id, Credit_Card_Notes__c, CC__c, Card_Type__c, Name_On_CC__c, CC_Expiration_Month__c, CC_Expiration_Year__c,SecCode__c,
            (SELECT Contact.MailingStreet, Contact.MailingCity, Contact.MailingState, Contact.MailingPostalCode, Contact.MailingCountry, Contact.Phone, Contact.Email FROM OpportunityContactRoles WHERE isprimary = TRUE)
            FROM Opportunity WHERE Id in :List_Opps]) {
                
                IF(CardInfo.OpportunityContactRoles.size()==0){
                    CardInfo.Credit_Card_Notes__c = 'There are no primary contacts listed in the Opportunity Contact Role section to apply a payment method.';                       
                    update CardInfo;
                                       
                } ELSE { 
                    FOR (OpportunityContactRole PayeeRole : CardInfo.OpportunityContactRoles) {
                        Contact Payee = PayeeRole.Contact;
                        String FullName;
                        IF(CardInfo.Name_On_CC__c == null){
                            FullName = 'NoName';
                        } else {
                            FullName = CardInfo.Name_On_CC__c;
                        }
                
                                               
                        //The rest of the variables that will be used are set here.
                        Integer Index = FullName.indexOf(' ',0);
                        String Prefix;     
                        String FName;     
                        String LName;   
                        String SubName;            
            
                //First check if a common salutation is on the name to pull it out and set the Prefix variable with it. 
                //Then strip it of the prefix to create a substring of the name without the prefix.
                        IF((FullName.left(Index) == 'Mr.') || 
                           (FullName.left(Index) == 'Mr') || 
                           (FullName.left(Index) == 'Mrs.') || 
                           (FullName.left(Index) == 'Mrs') || 
                           (FullName.left(Index) == 'Ms.') || 
                           (FullName.left(Index) == 'Ms') ||
                           (FullName.left(Index) == 'Dr.') ||
                           (FullName.left(Index) == 'Dr')){
                               Prefix = FullName.left(Index);
                               SubName = FullName.substring(Index + 1,FullName.length());
                               Index = SubName.indexOf(' ',0);
                               
                       //This sub-IF statement exists to process different logic depending 
                       //if there is one or more words after scurbbing the salutation.
                               IF(SubName.contains(' ')){
                                   FName = SubName.left(Index);
                                   LName = SubName.substring(Index,SubName.length());
                               } else{          
                                   FName = '';        
                                   LName = SubName; 
                               }
                               
                           //This other half of the if statement processes like the other but 
                           //without the logic to deal with a salutation since there ins't one.
                           //However there may actually be a salutation if it is not one of the
                           //common ones included in the first If Statement.
                           } else {    
                               Prefix = '';   
                               IF(FullName.contains(' ')){
                                   FName = FullName.left(Index);
                                   LName = FullName.substring(Index,FullName.length());
                               } else{        
                                   FName = '';       
                                   LName = FullName;
                               }               
                           }
            
                        //Now that the variables for the name has been procecced for different situations the 
                        //next code will create a new payment method record.
                        pymt__Payment_Method__c Method = new pymt__Payment_Method__c();
                        Method.Name = CardInfo.Card_Type__c + ' (' + CardInfo.CC__C.right(4) + ')';
                        Method.pymt__Contact__c = Payee.ID;
                        Method.pymt__Type__c = 'Credit Card';
                        Method.pymt__Card_Type__c = CardInfo.Card_Type__c;
                        Method.pymt__Billing_Salutation__c = Prefix;
                        Method.pymt__Billing_First_Name__c = FName;
                        Method.pymt__Billing_Last_Name__c = LName;
                        Method.pymt__Billing_City__c = payee.MailingCity;
                        Method.pymt__Billing_Street__c = payee.MailingStreet;
                        Method.pymt__Billing_State__c = payee.MailingState;
                        Method.pymt__Billing_Postal_Code__c = payee.MailingPostalCode;
                        Method.pymt__Last_4_Digits__c = CardInfo.CC__C.right(4);
                        Method.ccrd__Card_Number__c = CardInfo.CC__C;
                        Method.Sec_Code__c = CardInfo.SecCode__c;
                        Method.pymt__Expiration_Month__c = CardInfo.CC_Expiration_Month__c;
                        Method.pymt__Expiration_Year__c = CardInfo.CC_Expiration_Year__c;
                        newMethods.add(Method);
                    }               
                    Insert newMethods;
                
                    //Now that the card info is securly stored inside the payment method record and is encryped
                    //we can clear out the exposed CC info on the opportunity.
                    CardInfo.CC__C = '';
                    CardInfo.Name_On_CC__c = '';
                    CardInfo.SecCode__c='';
                    CardInfo.CC_Expiration_Month__c = '';
                    CardInfo.CC_Expiration_Year__c = '';
                    CardInfo.Card_Type__c = '';
                    CardInfo.Credit_Card_Notes__c = 'Credit Card was stored in a related Payment Method and is ready to use.  Go to the Payment Terminal and apply the existing payment method';         
                    Update CardInfo; 
                }
                                   
            }
    }catch (Exception e) {Trigger.new[0].addError(e.getMessage());}
}