1

We are creating opportunity and line items by APEX code from Rate cards and the jobs(custom objects). As part of the code, we are doing a validation to see if the Ready_for_Billing__c field in jobs (corresponding to the rate card) have been checked to true else we want to update the flag Ready_for_Billing_error__c on the rate card to False.

Can someone help me to rewrite the below code to avoid SOQL inside the for loop? Thanks in advance!

List<sked__Job__c> joblist=new List<sked__Job__c>();
List<Rate_Card__c> Rcardnew =[select Id,Job_Type__c,Last_Opportunity_Creation_Date__c,
                                     Next_Opportunity_Creation_Date__c,Account__c 
                                from Rate_Card__c
                               where (Opportunity_Creation_Date__c!=NULL OR 
                                      Next_Opportunity_Creation_Date_Error__c!=NULL)
                                 and Rate_Card_Type__c='Pain Management' 
                                 and Is_Active__c=true and Account__c=:account 
                                 AND Opportunity_Creation_Frequency__c='Weekly'];

if(Rcardnew.size()>0)
{
    for(Rate_Card__c rcard:Rcardnew)
    {
        joblist=[select id from sked__Job__c  
                  where Invoice_Start__c>=:rcard.Last_Opportunity_Creation_Date__c 
                    and Invoice_Finish__c<=:rcard.Next_Opportunity_Creation_Date__c 
                    and sked__Account__c=:rcard.Account__c 
                    and sked__Job_Status__c='Complete' 
                    and Job_Invoiced__c=false and sked__Type__c=:rcard.Job_Type__c 
                    and Ready_for_Billing__c=FALSE 
                    and Facility_Patient_Sevices_Count__c<=0];

        if(joblist.size()>0)
        {
            try
            {
                rcard.Ready_for_Billing_error__c=TRUE;
                update rcard;
            }
            catch(DMLException e)
            {
                //handled exception
            }
        }
    }
}
6
  • hey please avoid using queries in for loop in above code you have using joblist in a for loop please try to use out of the for loop and try to search in google @Mani – Pavan tej Mar 16 '16 at 12:01
  • Pavan, that's what the OP needs help with. "Search in Google" is something that we all can do, he just needed to be helped, not to be told to search. – Sebastian Kessel Mar 20 '16 at 23:03
  • is there a schema relationship between Sked_Job__c and Rate_Card__c? – cropredy Mar 23 '16 at 5:18
  • @cropredy: nope, there is no schema relationship between Sked_Job__c and Rate_Card__c. They are related based on the condition that I have given in the code above. – Mani Mar 23 '16 at 5:50
  • is account a singleton or a list (of Accounts) – cropredy Mar 23 '16 at 5:53
0
List<sked__Job__c> joblist=new List<sked__Job__c>();
List<sked__Job__c> jobsortedlist = new List<sked__Job__c>();
List<Rate_Card__c> Rcardnew =[select Id,Job_Type__c,Last_Opportunity_Creation_Date__c,
                                     Next_Opportunity_Creation_Date__c,Account__c 
                                from Rate_Card__c
                               where (Opportunity_Creation_Date__c!=NULL OR 
                                      Next_Opportunity_Creation_Date_Error__c!=NULL)
                                 and Rate_Card_Type__c='Pain Management' 
                                 and Is_Active__c=true and Account__c=:account 
                                 AND Opportunity_Creation_Frequency__c='Weekly'];

joblist=[Select id from sked__Job__c  
                             Where sked__Job_Status__c='Complete' AND
                                 Ready_for_Billing__c=FALSE  AND 
                                Facility_Patient_Sevices_Count__c<=0];


if(!Rcardnew.isNotEmpty() && joblist.isNotEmpty()){
    for(Rate_Card__c rcard:Rcardnew) {
        for(sked__Job__c job:joblist){
            if(job.Invoice_Start__c >= rcard.Last_Opportunity_Creation_Date__c && 
                job.Invoice_Finish__c <=rcard.Next_Opportunity_Creation_Date__c && 
                job.sked__Account__c==rcard.Account__c && 
                job.Job_Invoiced__c=false and sked__Type__c==rcard.Job_Type__c  ){
                jobsortedlist.add(rcard);
             }
        }
    }

     if(jobsortedlist >0)
        {
            try
            {  for(Rate_Card__c rcard:Rcardnew) 
                rcard.Ready_for_Billing_error__c=TRUE;
                update rcard;
            }
            catch(DMLException e)
            {
                //handled exception
            }
        }
}
4
  • I have tested it and it works fine(creates opty and line items as jobs) – Mani Mar 20 '16 at 11:31
  • @pavantej: I am doing an order by when I query the rate card and job since both are related by the job type. – Mani Mar 20 '16 at 23:01
  • @cropredy: I have amended the original question, thx! – Mani Mar 23 '16 at 2:24
  • This has some issues: (1) - DML inside of for loop; (2) if(jobsortedlist >0) won't compile (missing .size() - and besides, is superfluous. (3) ` if(!Rcardnew.isNotEmpty() && joblist.isNotEmpty())` is also wrong/typo (double negative0 and the whole line is superfluous. – cropredy Mar 23 '16 at 5:21
0

So, to address your specific concern, some logic like this should suffice: (I renamed account to be aId to be clearer)

Sked_Job__c[] jobList = [select ... from Sked_Job__c where sked_account__c = :aId];

Rate_card__c[] rcUpdList = new List<Rate_Card__c> ();
for (Rate_card__c rc : [select ... from Rate_Card__c where account__c = :aId ...)
   for (Sked_job__c sj : jobList)
      if (conditional logic to see if sj matches rc for billing error criteria) {
         rcUpdList.add(new Rate_card__c(id = rc.id, ready_for_billing_error__c = true));
         break;
      }

update rcUpdList;

However, I would suspect that this little routine fits inside of something larger that is (or should be) bulkified since you would presumably want to process many Rate Cards in a batch, each with a different account. If so, you're going to need to rethink all of this so that the routine is passed a list of accounts - and the soql for jobList and Rate Cards fetch all ratecards and all sked_Job__c for all accounts. But, we'll leave that for another day after you digest and learn from this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.