+ Start a Discussion
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12 

Account Trigger error - Too many SOQL queries: 101

Hi,

I have a trigger on Account which just update one field in all the related contacts. If the account is of type "Member" then the trigger updates a field in contact called "Relationship_Category_r__c" to "Member-staff" and if the Account type changes to "Member-past" then the contact field updates to "Member-past"

The trigger below is working fine if the number of contacts in any account is in between 50 - 80. However, it stops working when it exceed more than that. I can't give the exact number but its not working with our account which has around 116 contacts.

I am copying the code below for reference and any advise would be highly appreciated


trigger Update_related_Contacts_On_Account_fieldchange on Account (after update) {
set<id> ids=new set<id>();
 
    for(Account acc:trigger.new){
        ids.add(acc.id);
    }
    map<id,List<contact>> acc_con_maps=new map<id,List<contact>>();
    List<Account> accts=new List<Account>([select id,(select id,Relationship_Category_r__c from contacts) from Account where id=:ids]);
    For(Account accts1:accts){
        acc_con_maps.put(accts1.id,accts1.contacts);
    }
 
    list<contact> constoupdate=new list<contact>();
    for(Account acc1:trigger.new){
        if(acc_con_maps.containsKey(acc1.Id)&&acc_con_maps.get(acc1.id)!=null){
            if(acc1.Type=='Member'){
            
                for(contact con:acc_con_maps.get(acc1.Id)){
                    if(con.Relationship_Category_r__c !='Member'){
                        con.Relationship_Category_r__c ='Member - Staff';
                    constoupdate.add(con);
                }
                }
            }
            else if(acc1.type=='Member - Past'){
            
               for(contact con1:acc_con_maps.get(acc1.Id)){
                   if(con1.Relationship_Category_r__c !='Member - Past'){
                    con1.Relationship_Category_r__c ='Member - Past';
                    constoupdate.add(con1);
                   }
                }
            }
         
        }
    }
    update constoupdate;
}
Best Answer chosen by muhammadb1.3970135761364219E12
Sure@DreamSure@Dream
Hi,

try this:
IF(ISPICKVAL(Account.Account_Type__c,'Member'), 'Member-Staff',ISPICKVAL(Account.Account_Type__c,'Member-past'),'Member-Past','Default value').

Update the formula based on your logic.

All Answers

kevin lamkevin lam
I have modified the following lines of code:

// set<id> ids=new set<id>();

//    for(Account acc:trigger.new){
//        ids.add(acc.id);
//    }
    map<id,List<contact>> acc_con_maps=new map<id,List<contact>>();
//    List<Account> accts=new List<Account>([select id,(select id,Relationship_Category_r__c from contacts) from Account where id=:ids]);
//    For(Account accts1:accts){
      For (Account accts1 : [[select id,(select id,Relationship_Category_r__c from contacts) from Account where id IN :trigger.new.keyset()]) {
        acc_con_maps.put(accts1.id,accts1.contacts);
    }
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Dear Kevin,

Thanks for your reply. However, either I am not understanding your code or there are few problems with your answer

First off all in the For Loop you have double brackets starting "[[" and only one closing bracket. Even after removing the extra bracket the system is generating the following error

"Error: Compile Error: Method does not exist or incorrect signature: [LIST<Account>].keyset() at line 11 column 119"

Can you please elaborate ?

Once again thanks for your time and Please see the code after modified as per your suggestion

trigger Update_related_Contacts_On_Account_fieldchange on Account (after update) {

    map<id,List<contact>> acc_con_maps=new map<id,List<contact>>();
      For (Account accts1 : [select id,(select id,Relationship_Category_r__c from contacts) from Account where id IN :trigger.new.keyset()]) {
        acc_con_maps.put(accts1.id,accts1.contacts);
    }
 
    list<contact> constoupdate=new list<contact>();
    for(Account acc1:trigger.new){
        if(acc_con_maps.containsKey(acc1.Id)&&acc_con_maps.get(acc1.id)!=null){
            if(acc1.Type=='Member'){
            
                for(contact con:acc_con_maps.get(acc1.Id)){
                    if(con.Relationship_Category_r__c !='Member'){
                        con.Relationship_Category_r__c ='Member - Staff';
                    constoupdate.add(con);
                }
                }
            }
            else if(acc1.type=='Member - Past'){
            
               for(contact con1:acc_con_maps.get(acc1.Id)){
                   if(con1.Relationship_Category_r__c !='Member - Past'){
                    con1.Relationship_Category_r__c ='Member - Past';
                    constoupdate.add(con1);
                   }
                }
            }
         
        }
    }
    update constoupdate;
}



Sure@DreamSure@Dream
Hi,


Why dont you make Relationship_Category_r__c field in Contact as a formula field?


No need to write trigger if this is the only purpose.

You can try somethign similar:

IF(Account__r.Account_Type='Member', 'Member-Staff','Member-Past);

Make this as best answer if it solves your problem.

kevin lamkevin lam
Try this:
// set<id> ids=new set<id>();
   Set<ID> ids = Trigger.new.keySet();

//    for(Account acc:trigger.new){
//        ids.add(acc.id);
//    }
    map<id,List<contact>> acc_con_maps=new map<id,List<contact>>();
//    List<Account> accts=new List<Account>([select id,(select id,Relationship_Category_r__c from contacts) from Account where id=:ids]);
//    For(Account accts1:accts){
      For (Account accts1 : [select id,(select id,Relationship_Category_r__c from contacts) from Account where id IN :ids]) {
        acc_con_maps.put(accts1.id,accts1.contacts);
    }
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Sure@Dream thanks for your reply and it will be amazing if I could do something like this. It's always best to avoid un-necessary coding. However, the formula field is not recognizing the Account__r

Error: Field Account__r does not exist. Check spelling.

IF(Account__r.Account_Type='Member', 'Member-Staff','Member-Past')

Any other suggestions are most welcome
kevin lamkevin lam
It should be:

IF(Account.Type="Member", "Member-Staff","Member-Past")
Sure@DreamSure@Dream
Hi,

try this:
IF(ISPICKVAL(Account.Account_Type__c,'Member'), 'Member-Staff',ISPICKVAL(Account.Account_Type__c,'Member-past'),'Member-Past','Default value').

Update the formula based on your logic.

This was selected as the best answer
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Sure@Dream you are a genius for suggesting something so simple for such a big problem of mine.

Thank you very much for such a great idea and Kevin thanks for helping me fix it. Actually the Account.Type is a Picklist so I have just make a tiny change and it's working great

IF(ISPICKVAL(Account.Type,"Member"),"MEMBER","MEMBER-PAS")

Thanks you very much folks for your help, much appreciated

Cheers