You need to sign in to do that
Don't have an account?

Too many SOQL Error
public class PopulateCorporateFields {
public List<Corporate_Account_List_Assignment__c> calist { set; get; }
public PopulateCorporateFields(ApexPages.StandardController controller) {
}
public void updateFields()
{
calist = [SELECT Account_Name__c,Corporate_Partnership_Director_1__c,Corporate_Partnership_Director_2__c,Corporate_Partnership_Director_3__c,Corporate_Partnership_Director_4__c,Corporate_Partnership_Director_5__c,Marketing_Solutions_1__c, Marketing_Solutions_2__c, Marketing_Solutions_3__c, Media_Services_1__c, Media_Services_2__c, Media_Services_3__c,Digital_Manager_1__c,Digital_Manager_2__c,Digital_Manager_3__c,Digital_Manager_4__c,Digital_Manager_5__c, Id,Name,Ultimate_Parent_Name__c FROM Corporate_Account_List_Assignment__c];
for (Corporate_Account_List_Assignment__c ca : calist)
{
ca.Corporate_Partnership_Director_1__c = null;
ca.Corporate_Partnership_Director_2__c = null;
ca.Corporate_Partnership_Director_3__c = null;
ca.Corporate_Partnership_Director_4__c = null;
ca.Corporate_Partnership_Director_5__c = null;
ca.Digital_Manager_1__c = null;
ca.Digital_Manager_2__c = null;
ca.Digital_Manager_3__c = null;
ca.Digital_Manager_4__c = null;
ca.Digital_Manager_5__c = null;
ca.Marketing_Solutions_1__c = null;
ca.Marketing_Solutions_2__c = null;
ca.Marketing_Solutions_3__c = null;
ca.Media_Services_1__c = null;
ca.Media_Services_2__c = null;
ca.Media_Services_3__c = null;
System.debug('******ID********'+ca.Id);
Account ac = [SELECT Name,ParentId,Id FROM Account where Id =: ca.Account_Name__c];
List<AccountTeamMember> atmcpd =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Corporate Partnership Director' ];
List<AccountTeamMember> atmMS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'IMS' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmMDS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmDM =[ SELECT Id,TeamMemberRole,UserId, IsDeleted FROM AccountTeamMember WHERE AccountId =: ac.Id AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE ];
if(atmcpd!= null || atmcpd.size() > 0 )
{
// User u = [select Id,Name from user where Id=: atm.get(0).UserId];
//System.debug('******Size*******'+atmcpd.size()+'**********'+atmcpd.get(0).UserId);
if(atmcpd.size() > 0)
ca.Corporate_Partnership_Director_1__c = atmcpd.get(0).UserId;
if(atmcpd.size()>1)
ca.Corporate_Partnership_Director_2__c = atmcpd.get(1).UserId;
if(atmcpd.size()>2)
ca.Corporate_Partnership_Director_3__c = atmcpd.get(2).UserId;
if(atmcpd.size()>3)
ca.Corporate_Partnership_Director_4__c = atmcpd.get(3).UserId;
if(atmcpd.size()>4)
ca.Corporate_Partnership_Director_5__c = atmcpd.get(4).UserId;
}
if(atmms != null || atmms.size() > 0)
{
if(atmms.size() > 0)
ca.Marketing_Solutions_1__c = atmms.get(0).UserId;
if(atmms.size() > 1)
ca.Marketing_Solutions_2__c = atmms.get(1).UserId;
if(atmms.size() > 2)
ca.Marketing_Solutions_3__c = atmms.get(2).UserId;
}
if(atmmds != null || atmmds.size() > 0)
{
if(atmmds.size() > 0)
ca.Media_Services_1__c = atmmds.get(0).UserId;
if(atmmds.size() > 1)
ca.Media_Services_2__c = atmmds.get(1).UserId;
if(atmmds.size() > 2)
ca.Media_Services_3__c = atmmds.get(2).UserId;
}
if(atmdm != null || atmdm.size() > 0)
{
if(atmdm.size() > 0)
ca.Digital_Manager_1__c = atmdm.get(0).UserId;
if(atmdm.size() > 1)
ca.Digital_Manager_2__c = atmdm.get(1).UserId;
if(atmdm.size() > 2)
ca.Digital_Manager_3__c = atmdm.get(2).UserId;
if(atmdm.size() > 3)
ca.Digital_Manager_4__c = atmdm.get(3).UserId;
if(atmdm.size() > 4)
ca.Digital_Manager_5__c = atmdm.get(4).UserId;
}
}
update calist;
}
}
Getting too many soql queries error getting. Pleas help me on this how to optimize it.
Well you have lots of SOQL statements inside for loop, whihc is not a good practice. You may consider using collections to avoid soql inside for loops. I am highlighting the problem areas in your code.
Have a look here on how to bulkify your code to avoid too many soql : http://wiki.developerforce.com/page/Best_Practice:_Bulkify_Your_Code
thanks for your reply. How to use collection at that part is my concern. Could you please help me on this.
GO through this link
http://wiki.developerforce.com/page/Apex_Code_Best_Practices
public class PopulateCorporateFields {
public List<Corporate_Account_List_Assignment__c> calist { set; get; }
public PopulateCorporateFields(ApexPages.StandardController controller) {
}
public void updateFields()
{
List<Id> AccIds = new List<Id>();
calist = [SELECT Account_Name__c,Corporate_Partnership_Director_1__c,Corporate_Partnership_Director_2__c,Corporate_Partnership_Director_3__c,Corporate_Partnership_Director_4__c,Corporate_Partnership_Director_5__c,Marketing_Solutions_1__c, Marketing_Solutions_2__c, Marketing_Solutions_3__c, Media_Services_1__c, Media_Services_2__c, Media_Services_3__c,Digital_Manager_1__c,Digital_Manager_2__c,Digital_Manager_3__c,Digital_Manager_4__c,Digital_Manager_5__c, Id,Name,Ultimate_Parent_Name__c FROM Corporate_Account_List_Assignment__c ];
//Account ac = [SELECT Name,ParentId,Id FROM Account where Id =: ca.Account_Name__c];
for(Corporate_Account_List_Assignment__c Cal:calist )
AccIds.add(Cal.Account_Name__c);
List<AccountTeamMember> atmcpd =[ SELECT Id,TeamMemberRole,UserId, IsDeleted,AccountId FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Corporate Partnership Director' ];
List<AccountTeamMember> atmMS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted,AccountId FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Marketing Solutions' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmMDS =[ SELECT Id,TeamMemberRole,UserId, IsDeleted,AccountId FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Media Services' AND Isdeleted = FALSE ];
List<AccountTeamMember> atmDM =[ SELECT Id,TeamMemberRole,UserId,AccountId, IsDeleted FROM AccountTeamMember WHERE AccountId in :AccIds AND TeamMemberRole = 'Digital Manager' AND Isdeleted = FALSE ];
System.debug('**************CPD*********'+atmcpd+'*********MS******'+atmMS+'******MDS*****'+atmMDS+'***********atmDM****'+atmDM);
for(Corporate_Account_List_Assignment__c Cal: calist)
{
integer i = 0;
for(AccountTeamMember atm:atmcpd)
{
if(Cal.Account_Name__c == atm.AccountId)
{
if(i==0 )
cal.Corporate_Partnership_Director_1__c = atm.UserId;
else if(i==1)
cal.Corporate_Partnership_Director_2__c = atm.UserId;
else if(i==2)
cal.Corporate_Partnership_Director_3__c = atm.UserId;
else if(i==3)
cal.Corporate_Partnership_Director_4__c = atm.UserId;
else if(i==4)
cal.Corporate_Partnership_Director_5__c = atm.UserId;
}
i++;
}
integer j = 0;
for(AccountTeamMember atm:atmMS)
{
if(Cal.Account_Name__c == atm.AccountId)
{
if(j==0)
cal.Marketing_Solutions_1__c = atm.UserId;
else if(j==1)
cal.Marketing_Solutions_2__c = atm.UserId;
else if(j==2)
cal.Marketing_Solutions_3__c = atm.UserId;
}
j++;
}
integer k = 0;
for(AccountTeamMember atm:atmMDS)
{
if(Cal.Account_Name__c == atm.AccountId)
{
if(k==0 )
cal.Media_Services_1__c = atm.UserId;
else if(k==1)
cal.Media_Services_2__c = atm.UserId;
else if(k==2)
cal.Media_Services_3__c = atm.UserId;
}
k++;
}
integer l = 0;
for(AccountTeamMember atm:atmDM)
{
system.debug('i value before*'+i);
if(Cal.Account_Name__c == atm.AccountId)
{
if(l==0 )
{
system.debug('i=0'+atm.UserId);
cal.Digital_Manager_1__c = atm.UserId;
}
else if(l==1)
{
cal.Digital_Manager_2__c = atm.UserId;
system.debug('i=1'+atm.UserId);
}
else if(l==2)
{
system.debug('i=2'+atm.UserId);
cal.Digital_Manager_3__c = atm.UserId;
}
else if(l==3)
{
cal.Digital_Manager_4__c = atm.UserId;
system.debug('i=3'+atm.UserId);
}
else if(l==4)
{
cal.Digital_Manager_5__c = atm.UserId;
system.debug('i=4'+atm.UserId);
}
}
l++;
}
}
system.debug('updated list'+calist);
update calist;
}
}
After changing also not working. pls help me on it