You need to sign in to do that
Don't have an account?
muhammadb1.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;
}
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;
}
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
// 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);
}
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;
}
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.
// 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);
}
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
IF(Account.Type="Member", "Member-Staff","Member-Past")
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.
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