Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I know that the reason for this it's because I have a query that it's executed inside a loop, but I don't see how to redesign the code to avoid this. Any help will be appreciated.

Basically, I have a scheduled class that queries cases with certain criteria and checks if they have a public article attached, if the article is public, then an email should be sent to the case contact.

This is part of my scheduled class code.

public void sendKBArticle(){

     Boolean hasPublicArticles;
     Id EmailTemplateId='00XP0000000MHPI'; 

     Map<id,case> cMap = new Map<id,case>(
     [Select Id,ContactId from Case   Where Owner.Type='User' AND IsClosed=false AND KB_Article_Sent__c=false 
         AND Status NOT IN ('Pending Bug Fix','Solution Provided','Escalated','Reopened')
             AND RecordType.Name IN ('Internal', 'Customer Portal') AND CreatedDate  = LAST_N_DAYS:3 ]);

        if(!cMap.IsEmpty()){

           for(Case cnt : cMap.values()) {

                ArticlePublicLinksonEmail cpkb=new ArticlePublicLinksonEmail();

                hasPublicArticles=cpkb.getPublicArticles(cnt.id);

                if(hasPublicArticles){

                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setTemplateId(EmailTemplateId);
                    mail.setTargetObjectId(cnt.ContactId);
                    mail.setWhatId(cnt.Id);
                    mail.setSaveAsActivity(true);  
                    Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                    Cnt.KB_Article_Sent__c=true;
                }
            }




           try {
                 update cMap.values();
           } catch (DMLException e){         

                .....some code
           }

        }

     }    


}

As you can see, inside the loop I am passing the object id to another method, this method uses this case id to query if the case has a public article attached. Therefore, if I have many cases I hit the query limit.

Any ideas how to fix this or any suggestions in how to approach this in a different way?

Thanks!

share|improve this question
1  
Rewrite getPublicArticles to accept a list/set of caseids and return a map of ids+integer/boolean instead. –  Martin Peters Jan 2 '13 at 13:05
add comment

1 Answer

up vote 4 down vote accepted

@MartinPeters is on the right track: getPublicArticles needs to work in bulk somehow. You need to call getPublicArticles outside of the loop, since it's doing a query every time. So collect Case Ids before the getPublicArticles and loop through the results to deal with public articles afterward.

Also, you are calling Messaging.sendEmail() inside your loop, which will also hit the governor limit eventually. Instead, keep a running list<Messaging.SingleEmailMessage>, and do Messaging.sendEmail() once the list is full.

set<Id> caseids = cMap.keyset();
//assuming this method is "private map<Id, Boolean> getPublicArticles(set<Id> caseids)"
map<Id, Boolean> caseid2HasPublicArticlesmap = getPublicArticles(caseids);

list<Messaging.SingleEmailMessage> emailstosend = new list<Messaging.SingleEmailMessage>();
for(Case cnt : cMap.values()) {
    if (caseid2HasPublicArticlesmap.get(cnt.Id) == TRUE) {
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                mail.setTemplateId(EmailTemplateId);
                mail.setTargetObjectId(cnt.ContactId);
                mail.setWhatId(cnt.Id);
                mail.setSaveAsActivity(true);  
                //Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                emailstosend.add(mail);
                Cnt.KB_Article_Sent__c=true;
    }
}
Messaging.sendEmail(emailstosend);
share|improve this answer
    
Thanks Jeremy, this seems to be the right approach. I am struggling to convert the getPublicArticles method to "BULK MODE", I will keep trying but I might need to come back for additional help : S –  PepeFloyd Jan 3 '13 at 0:00
    
I was able to create this method and get this working as suggested. Thanks again! –  PepeFloyd Jan 3 '13 at 16:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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