Apex Code Development You need to sign in to do that
Don't have an account?
MurrayH SOQL Governor LImits
I am having problems with the governor limits on SOQL queries. I am trying to populate a list. I tried the "For" procedure as outlined in the Apex Documentation, but I couldn't get it to work. I also tried to break the query into three parts and then add the parts to the list, and that didn't work either. I am new to this, so any comments would be really appreciated.
trigger ClassList1 on Program_Schedule__c (after insert, after update) {
//create a variable to hold the records to insert into the Class Members Object
List<Class_Member__c> AddClassMembers = New List<Class_Member__c>();
//Get a list of approved clients for all programs
// using addall to list methodology
//List<Program_Interest__c> ApprovedClients = New List<Program_Interest__C>();
//ApprovedClients.addall ([Select Client__c, Already_Enrolled__c, Program_of_Interest__c from Program_Interest__c where Name < 'G' and Next_Session__c = true AND Already_Enrolled__c = False]);
//ApprovedClients.addall ([Select Client__c, Already_Enrolled__c, Program_of_Interest__c from Program_Interest__c where Name >= 'H' and Name < 'I' and Next_Session__c = true AND Already_Enrolled__c = False]);
//ApprovedClients.addall ([Select Client__c, Already_Enrolled__c, Program_of_Interest__c from Program_Interest__c where Name >= 'T' and Next_Session__c = true AND Already_Enrolled__c = False]);
//Using the For methodology
For(List<Program_Interest__c> ApprovedClients : [Select Client__c, Already_Enrolled__c, Program_of_Interest__c from Program_Interest__c where Next_Session__c = true AND Already_Enrolled__c = False])
{
//Get existing class lists for duplicate checking
List<Class_Member__c> ExistingClassMembers = New List<Class_Member__C>([Select Program_Schedule__C from Class_Member__C]);
//Get list size
Double TotalNumApprovedClients = ApprovedClients.Size();
//Create a trigger list to hold ids
List<id> ProgInstance = New List<id>();
//Iterate through trigger records
For (Program_Schedule__c PSACM : trigger.new){
//PSACM.Class_List_Created__C = True;
//PSACM.Create_Class_List__C = False;
ProgInstance.add(PSACM.id);
ProgInstance.add(PSACM.Program__c);
ID ProgSchedID = PSACM.ID;
ID ProgID = PSACM.Program__c;
//Check to see if create box is checked
If (PSACM.Create_Class_List__C == true){
//Double CountClients = [Select Count() from Program_Interest__c where Next_Session__c = true AND Id in :ProgInstance ];
//iterate through list of approved clients and add those with matching program id's to the AddClassMembers list
For (Program_Interest__c APPCL : ApprovedClients)
{
If (APPCL.Program_of_Interest__c == ProgID)
{
ID ClientID = APPCL.Client__c;
APPCL.Already_Enrolled__c = True;
AddClassMembers.add(New Class_Member__c(Client__c = ClientID, Program_Schedule__c = ProgSchedID, Program__c = ProgID ));
}
}
Double CountClientstobeadded = AddClassMembers.size();
//Check to see if the number of clients is greater than the class capacity
Double ClassCap = PSACM.Capacity__c;
If (ClassCap < CountClientstobeadded){
PSACM.AddError ('Too many clients for the class, adjust client numbers or increase class size');}
//check to see if class list exists
Double CountExistingClients = [Select Count() from Class_Member__c where Program_Schedule__C in :ProgInstance ];
Double ClassPlacesLeft = ClassCap - CountExistingClients;
If (CountClientstobeAdded > ClassPlacesLeft){
PSACM.AddError ('Clients previously added, check and add manually if necessary');
}
//If (CountExistingClients == 0){
//If (ClassCap >= CountClientstobeadded){
//For (Program_Schedule__c PSACM : trigger.new)
//PSACM.Class_List_Created__C = True;
//PSACM.Create_Class_List__C = False;
try{
insert AddClassMembers;
Update ApprovedClients;
//Update PSACM;
}
catch (DmlException de) {
for (Integer i = 0; I < de.getNumDml(); i++) {
PSACM.addError(de.getDmlMessage(i));
}
}
}}
}}
your 2 options are
1) Move you code into a @future method this will give you 10,000 DML statemets or 20,000 queries and just call this @future method form the trigger
2) Rewrite you code so you do not so SELECT/SOQL statemetns within a nested FOR LOOP. Instead of doing additional selects with in 2 FOR loops try to select the data you need using INNER JOINS.
Also move the
insert AddClassMembers;
Update ApprovedClients;
outside all of your loops
All Answers
What are you trying to accomplish with this trigger? What is the business logic behind it?
I am creating class lists for an institution that educates the less fortunate.
The potential students are pre-qualified to attend a course, and are put on a 'program interest' list. When a course is scheduled, this code examines the 'program interest' list, and places the qualified students into the class list for that scheduled course.
It first counts the number of potential applicants to ensure that the total number of students in the class is below a certain number. Then it creates the class list and updates the program interest table to show that they have been assigned to the next class.
My problem now is that the original list that I produce is about 1200 records, so I run into the 1000 select limit with the SOQL query.
Thanks for your interest, let me know if you need further information.
Murray
your 2 options are
1) Move you code into a @future method this will give you 10,000 DML statemets or 20,000 queries and just call this @future method form the trigger
2) Rewrite you code so you do not so SELECT/SOQL statemetns within a nested FOR LOOP. Instead of doing additional selects with in 2 FOR loops try to select the data you need using INNER JOINS.
Also move the
insert AddClassMembers;
Update ApprovedClients;
outside all of your loops
I tried your inner join idea, and it seemed to do the trick. Thanks for the effore!