+ Start a Discussion
SFDC n12SFDC n12 

Trigger help needed

Hi,

I need help on the following req

1) I am having a custom field called as "Dealer Principal__c" in my custom object called as "Account Exceptions__c"

2) There is a lookup on my custom object called as Account__c which is a lookup to the account object


My req is i want to populate my custom field  "Dealer Principal__c"  on Account exception__c custom object with  the contact first name and last name of the contact  related to the account that i select if the contact title is set as "Dealer Principal"


if the account record type is group account , the child accounts contact is set which is having 
contact title as "Dealer Principal"



I have written a trigger , which is getting saved , but on asving the record in the system i am getting the following exception,

System.LimitException: Too many SOQL queries: 101

My Trigger :



trigger AF_updateaccountexceptions on AccountExceptions__c(after insert, after update){
    map<id,string> dealercontacts=new map<id,string>();
    set<id> accids=new set<id>();
    
    for(AccountExceptions__c acex:trigger.new){
        accids.add(acex.Account__c);
    }
    
    List<contact> cons=new List<contact>([select accountid,firstname,lastname from contact where title='Dealer Principal' and accountid=:accids]);
    
    for(Contact con:cons){
        string conname=con.firstname+' '+con.lastname;
        dealercontacts.put(con.accountid,conname);
    }
    
    list<AccountExceptions__c> acexlisttoupdate =new list<AccountExceptions__c>();
    
    for(AccountExceptions__c acex:trigger.new){
            AccountExceptions__c accex1=new AccountExceptions__c();
        if(dealercontacts.containskey(acex.Account__c)){
        accex1.id=acex.id;
        accex1.Dealer_Principal_s__c=dealercontacts.get(acex.Account__c);
        acexlisttoupdate.add(accex1);
        }
    }
    
    update acexlisttoupdate;
}


Help me what do i need to change in my trigger

Thanks in Advance
 
Best Answer chosen by SFDC n12
Virendra ChouhanVirendra Chouhan
Use Collection
i.e.
set<String> Stest = new set<String>();
for (AccountExceptions__c myobj: Trigger.New) {
   if (rtMap.get(myobj.account__c) == 'Group Account') {
                // if it is a group account
  	              System.debug ('dos : ' + myobj.DOSUser__c);
		  if (myobj.DOSUser__c != null) {
			Stest.add(myObj.DOSUser__c)
		}
      }              
  }        
  User rvp = [SELECT ManagerID, Manager.Name FROM User WHERE Id = : Stest LIMIT 1];
                    myobj.RVP_Approver__c = rvp.managerId;

Note : This code is not tested

All Answers

Virendra ChouhanVirendra Chouhan
Hi
In which line you are facing this error?
SFDC n12SFDC n12
the trigger is getting saved successfully without any errors 

I am getting this exception on saving a record in my object
Virendra ChouhanVirendra Chouhan
You have another Trigger in the same object ?
SFDC n12SFDC n12
Yes I have another code in the same object

Another Trigger :

trigger AF_AccountExceptions_ApprovalProcess on AccountExceptions__c(before insert, before update) {

    List < Id > accId = new List < Id > ();

    for (AccountExceptions__c spl: Trigger.New) {
        accId.add(spl.Account__c);
    }

    if (trigger.isBefore) {

        Map<String, List<AccountExceptions__c>> accExcMap = new Map<String, List<AccountExceptions__c>>();
        List<AccountExceptions__c> ax = [Select Id, Account__c, Type__c, ADR_Years__c, ADR_Month__c, Executive_Approval_Reasons__c From AccountExceptions__c where Account__c IN :accId];
        List<AccountExceptions__c> aecList;
        for (AccountExceptions__c a : ax) {
            if (a.Type__c == 'Exception Adjustment' || a.Type__c == 'Business Relationship Adjustment') {
                if (accExcMap.get (a.account__c) != null) {
                   aecList = accExcMap.get(a.account__c);
                   aecList.add (a);
                }
                else {
                   aecList = new List<AccountExceptions__c>();
                   aecList.add (a);
                }
                accExcMap.put (a.Account__c, aecList);
            }
        }
        
        /* record types of account */
        Map<String, String> rtMap = new Map<String, String>();
        for (Account a : [SELECT Id, RecordType.Name FROM Account Where Id in :accId]) {
           rtMap.put (a.Id, a.RecordType.Name);
        }

        /* Get the account team members */
        
        List < AccountTeamMember > dosList = [SELECT UserId, TeamMemberRole, Id, AccountId FROM AccountTeamMember WHERE TeamMemberRole = 'Management Team - Auto Finance DOS'
            AND AccountId IN: accId ];
        List < AccountTeamMember > growthDosList = [SELECT UserId, TeamMemberRole, Id, AccountId FROM AccountTeamMember WHERE TeamMemberRole = 'Management Team – Growth DOS'
            AND AccountId IN: accId ];
        List < AccountTeamMember > rvpList = [SELECT UserId, TeamMemberRole, Id, AccountId FROM AccountTeamMember WHERE TeamMemberRole = 'Management Team - Auto Finance RVP'
            AND AccountId IN: accId ];

        /* hold in maps to process multiple account exceptions */
        
        Map<String, String> dosMap = new Map<String, String>();
        for (AccountTeamMember a : dosList) {
            dosMap.put (a.AccountId, a.UserId);
        }
        Map<String, String> growthDosMap = new Map<String, String>();
        for (AccountTeamMember a : growthDosList) {
            growthDosMap.put (a.AccountId, a.UserId);
        }
        Map<String, String> rvpMap = new Map<String, String>();
        for (AccountTeamMember a : rvpList) {
            rvpMap.put (a.AccountId, a.UserId);
        }

        /* logged in user's profile */      
        User u = AF_DealerCRM_Utility.getUserInfo();
        String profileName = u.Profile.Name;

        for (AccountExceptions__c myobj: Trigger.New) {

            if (rtMap.get( myobj.Account__c) == 'Group Account') {
                // if it is a group account
                System.debug ('dos : ' + myobj.DOSUser__c);
                if (myobj.DOSUser__c != null) {
                    User rvp = [SELECT ManagerID, Manager.Name FROM User WHERE Id = : myobj.DOSUser__c LIMIT 1];
                    myobj.RVP_Approver__c = rvp.managerId;
                }
            }
            else {
                // not a group account
                if (profileName == 'AF: Account Executive') {
                    // if the logged in user is an AE
                    
                    if (u.userRole.Name.contains ('Growth')) {
                        myobj.DOSUser__c = growthDosMap.get(myobj.Account__c);
                       
                    }
                    else {
                       myobj.DOSUser__c = dosMap.get(myobj.Account__c);
                    }
                }
                else {
                    if (growthDosMap.get(myobj.Account__c) != null) {
                        myobj.DOSUser__c = growthDosMap.get(myobj.Account__c);
                    } else {
                        myobj.DOSUser__c = dosMap.get(myobj.Account__c);
                    }
                }

                if (rvpMap.get(myobj.Account__c) != null) {
                    myobj.RVP_Approver__c = rvpMap.get(myobj.Account__c);
                }
            }

            if (myobj.Executive_Approval_Reasons__c != null) {            
                String str = myobj.Executive_Approval_Reasons__c;
                myobj.Executive_Approval_Reasons__c = str.replace ('Dealer Annual Limit Exceeded', '');
                System.debug ('Total number : ' + myobj.Executive_Approval_Reasons__c);
            }

            if (accExcMap.get(myobj.Account__c) != null &&  (accExcMap.get(myobj.Account__c)).size() > 3) {
             
                Integer count = 0;
                Date dt = date.newInstance(Integer.ValueOf(myobj.ADR_Years__c), Integer.ValueOf(myobj.ADR_Month__c), 01);
                for (AccountExceptions__c ac : accExcMap.get(myobj.Account__c)) {
                   Date d = date.newInstance(Integer.ValueOf(ac.ADR_Years__c), Integer.ValueOf(ac.ADR_Month__c), 01);
                   if (d.monthsBetween(dt) <= 13) {
                      count++;
                   }
                }
                
                if (count > 3) {
                   myobj.Executive_Committee_Approval__c = 'Yes';
                   if (myobj.Executive_Approval_Reasons__c != '') {
                       myobj.Executive_Approval_Reasons__c += ';Dealer Annual Limit Exceeded';
                       myobj.of_Exceptions_Last_13_months__c+=1;
                   }
                   else {
                       myobj.Executive_Approval_Reasons__c = 'Dealer Annual Limit Exceeded';
                        myobj.of_Exceptions_Last_13_months__c+=1;
                   }
                }
                else {
                    myobj.Executive_Committee_Approval__c = 'No';
                    myobj.Executive_Approval_Reasons__c = '';
                }
            }
        }
    }
}
Virendra ChouhanVirendra Chouhan
Try This query
 List<contact> cons= [select accountid,firstname,lastname from contact where title='Dealer Principal' and accountid IN :accids];
SFDC n12SFDC n12
I tried still getting the same exception on saving the record
Virendra ChouhanVirendra Chouhan
I think in the second Trigger you wrote a SOQL query under the For loop.
because this error occure when the SOQL query fire more then 100 time and it happend if we write a query  Loop.
So try to search and remove it
Hope it'll help you.
PratikPratik (Salesforce Developers) 
Hi,

The root cause for the error is the SOQL under for loop. Can you check your code from the other trigger you mentioned. Line no. 62

Thanks,
Pratik
SFDC n12SFDC n12
oh k , but how do i remove it , i am calling the for loop instance in my query 



for (AccountExceptions__c myobj: Trigger.New) {

            if (rtMap.get( myobj.Account__c) == 'Group Account') {
                // if it is a group account
                System.debug ('dos : ' + myobj.DOSUser__c);
                if (myobj.DOSUser__c != null) {
                    User rvp = [SELECT ManagerID, Manager.Name FROM User WHERE Id = : myobj.DOSUser__c LIMIT 1];
                    myobj.RVP_Approver__c = rvp.managerId;
                }
            }
 
Virendra ChouhanVirendra Chouhan
Use Collection
i.e.
set<String> Stest = new set<String>();
for (AccountExceptions__c myobj: Trigger.New) {
   if (rtMap.get(myobj.account__c) == 'Group Account') {
                // if it is a group account
  	              System.debug ('dos : ' + myobj.DOSUser__c);
		  if (myobj.DOSUser__c != null) {
			Stest.add(myObj.DOSUser__c)
		}
      }              
  }        
  User rvp = [SELECT ManagerID, Manager.Name FROM User WHERE Id = : Stest LIMIT 1];
                    myobj.RVP_Approver__c = rvp.managerId;

Note : This code is not tested
This was selected as the best answer
SFDC n12SFDC n12

Hi,

I added the code and the record is getting saved , but the trigger is not updating the field


trigger AF_updateaccountexceptions on AccountExceptions__c(after insert, after update){
    map<id,string> dealercontacts=new map<id,string>();
    set<id> accids=new set<id>();
   List<contact> cons=new List<contact>([select accountid,firstname,lastname from contact where title='Dealer Principal' and accountid IN :accids]);

    
    for(AccountExceptions__c acex:trigger.new){
        accids.add(acex.Account__c);
    }
    
    
    for(Contact con:cons){
        string conname=con.firstname+' '+con.lastname;
        dealercontacts.put(con.accountid,conname);
    }
    
    list<AccountExceptions__c> acexlisttoupdate =new list<AccountExceptions__c>();
    
    for(AccountExceptions__c acex:trigger.new){
            AccountExceptions__c accex1=new AccountExceptions__c();
        if(dealercontacts.containskey(acex.Account__c)){
        accex1.id=acex.id;
        accex1.Dealer_Principal_s__c=dealercontacts.get(acex.Account__c);
        acexlisttoupdate.add(accex1);
        }
    }
    
    update acexlisttoupdate;
}


let me know if there is any change need to be done in my trigger
Virendra ChouhanVirendra Chouhan
Hello
I think you have to put the SOQL query which you fire on Contact object after the first for loop.
because you use the accids set in that query and after the query you write the add method for this set.
for(AccountExceptions__c acex:trigger.new){
        accids.add(acex.Account__c);
    }
  List<contact> cons=new List<contact>([select accountid,firstname,lastname from contact where title='Dealer Principal' and accountid IN :accids]);

 
SFDC n12SFDC n12
I have added the query after the for loop


trigger AF_updateaccountexceptions on AccountExceptions__c(before insert, before update,after insert, after update){
    map<id,string> dealercontacts=new map<id,string>();
    set<id> accids=new set<id>();

    
    for(AccountExceptions__c acex:trigger.new){
        accids.add(acex.Account__c);
    }
    
   List<contact> cons=new List<contact>([select Id,accountid,firstname,lastname from contact where title='Dealer Principal' and accountid IN :accids]);

    for(Contact con:cons){
        string conname=con.firstname+' '+con.lastname;
        dealercontacts.put(con.accountid,conname);
    }
    
    list<AccountExceptions__c> acexlisttoupdate =new list<AccountExceptions__c>();
    
    for(AccountExceptions__c acex:trigger.new){
            AccountExceptions__c accex1=new AccountExceptions__c();
        if(dealercontacts.containskey(acex.Account__c)){
        accex1.id=acex.id;
        accex1.Dealer_Principal_s__c=dealercontacts.get(acex.Account__c);
        acexlisttoupdate.add(accex1);
        }
    }
    
    update acexlisttoupdate;
}

but now this trigger is throwing exception on save 

"​System.LimitException: Too many SOQL queries: 101"
 
Virendra ChouhanVirendra Chouhan
You changed the second Trigger also where you wrote the query in loop?
SFDC n12SFDC n12
i have put it outside for loop only


   for(Contact con:cons){
        string conname=con.firstname+' '+con.lastname;
        dealercontacts.put(con.accountid,conname);
    }
    
    list<AccountExceptions__c> acexlisttoupdate =new list<AccountExceptions__c>();
    
 
Virendra ChouhanVirendra Chouhan
See my Previous commant where i told you about tot use Collections
SFDC n12SFDC n12
Yes i added these statements in my previous code , but if i end the for loop here , there is a else loop after this which will thrw an error right ?


set<String> Stest = new set<String>();
for (AccountExceptions__c myobj: Trigger.New) {
   if (rtMap.get(myobj.account__c) == 'Group Account') {
                // if it is a group account
                      System.debug ('dos : ' + myobj.DOSUser__c);
                 if (myobj.DOSUser__c != null) {
                       Stest.add(myObj.DOSUser__c)
               }
      }             
  }       
  User rvp = [SELECT ManagerID, Manager.Name FROM User WHERE Id = : Stest LIMIT 1];
                    myobj.RVP_Approver__c = rvp.managerId;



My old trigger 


 for (AccountExceptions__c myobj: Trigger.New) {

            if (rtMap.get( myobj.Account__c) == 'Group Account') {
                // if it is a group account
                System.debug ('dos : ' + myobj.DOSUser__c);
                if (myobj.DOSUser__c != null) {
                    User rvp = [SELECT ManagerID, Manager.Name FROM User WHERE Id = : myobj.DOSUser__c LIMIT 1];
                    myobj.RVP_Approver__c = rvp.managerId;
                }
            }
            else {
                // not a group account
                if (profileName == 'AF: Account Executive') {
                    // if the logged in user is an AE
                    

 
SFDC n12SFDC n12
i tried inactivating the old trigger and tried with the new one , but its saving thre cord now , but not updating the field