function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Lee SinLee Sin 

Do I need to use batch apex in this scenario? How can I avoid gonernor limits in my code?

User-added image

I am trying to build a report in apex and send an html email.
The report is basicly counting records number. But it has cross queries.
Can you look my code and comments and give me some advice?

Thank you!
Best Answer chosen by Lee Sin
Vatsal KothariVatsal Kothari
Hi Jack,

You can refer below code:
List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
List<Account> clientList = new List<Account>();
Map<Id,Account> accMap = new Map<Id,Account>();
Map<Id,Task> taskMap = new Map<Id,Task>();

for(Account acc : [Select id,Client_territories__c, name from Account where account_type__c = 'advisor']){
	accMap.put(a.Id,acc);
}

for(Task t : [Select id from task where calltype__c = 'a' or calltype__c = 'b' or calltype__c = 'c' or calltype__c = 'd']){
	if(taskMap.containskey(t.whatId)){
		taskMap.get(t.whatId).add(t);
	}
	else{		
		List<Task> taskList = new List<Task>();
		taskList.add(t);
		taskMap.put(t.whatId,taskList);
	}
}

for(Account advisor : advisorList){
	if(accMap.containsKey(advisor.Id) && accMap.get(advisor.Id).Client_territories__c == advisor.Client_territories__c){
		clientList.add(accMap.get(advisor.Id));
	}
	
	Integer a = 0;
	Integer b = 0;
	Integer c = 0;
	Integer d = 0;
	
	for(Account client : clientList){
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'a'){
			List<task> t1 = taskMap.get(client.Id);
			a+ = t1.size();
		}
		
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'b'){
			List<task> t2 = taskMap.get(client.Id);
			b+ = t2.size();
		}
		
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'c'){
			List<task> t3 = taskMap.get(client.Id);
			c+ = t3.size();
		}
		
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'd'){
			List<task> t4 = taskMap.get(client.Id);
			d+ = t4.size();
		}
	}
	
	Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
    
	List<String> sendTo = new List<String>();
	sendTo.add(advisor.PersonEmail);
	mail.setToAddresses(sendTo

	mail.setSubject('Report Details');
	String body = 'Report details are as follows <br/> ';
	body += 'Task Call Type A:'+a;
	body += 'Task Call Type B:'+b;
	body += 'Task Call Type C:'+c;
	body += 'Task Call Type D:'+d;
	mail.setHtmlBody(body);

	mails.add(mail);	
}
Messaging.sendEmail(mails);
If this solves your problem, kindly mark it as the best answer.

Thanks,
Vatsal

All Answers

Vatsal KothariVatsal Kothari
Hi Jack,

You can refer below code:
List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
List<Account> clientList = new List<Account>();
Map<Id,Account> accMap = new Map<Id,Account>();
Map<Id,Task> taskMap = new Map<Id,Task>();

for(Account acc : [Select id,Client_territories__c, name from Account where account_type__c = 'advisor']){
	accMap.put(a.Id,acc);
}

for(Task t : [Select id from task where calltype__c = 'a' or calltype__c = 'b' or calltype__c = 'c' or calltype__c = 'd']){
	if(taskMap.containskey(t.whatId)){
		taskMap.get(t.whatId).add(t);
	}
	else{		
		List<Task> taskList = new List<Task>();
		taskList.add(t);
		taskMap.put(t.whatId,taskList);
	}
}

for(Account advisor : advisorList){
	if(accMap.containsKey(advisor.Id) && accMap.get(advisor.Id).Client_territories__c == advisor.Client_territories__c){
		clientList.add(accMap.get(advisor.Id));
	}
	
	Integer a = 0;
	Integer b = 0;
	Integer c = 0;
	Integer d = 0;
	
	for(Account client : clientList){
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'a'){
			List<task> t1 = taskMap.get(client.Id);
			a+ = t1.size();
		}
		
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'b'){
			List<task> t2 = taskMap.get(client.Id);
			b+ = t2.size();
		}
		
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'c'){
			List<task> t3 = taskMap.get(client.Id);
			c+ = t3.size();
		}
		
		if(taskMap.containskey(client.Id) && taskMap.get(client.Id).calltype__c == 'd'){
			List<task> t4 = taskMap.get(client.Id);
			d+ = t4.size();
		}
	}
	
	Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
    
	List<String> sendTo = new List<String>();
	sendTo.add(advisor.PersonEmail);
	mail.setToAddresses(sendTo

	mail.setSubject('Report Details');
	String body = 'Report details are as follows <br/> ';
	body += 'Task Call Type A:'+a;
	body += 'Task Call Type B:'+b;
	body += 'Task Call Type C:'+c;
	body += 'Task Call Type D:'+d;
	mail.setHtmlBody(body);

	mails.add(mail);	
}
Messaging.sendEmail(mails);
If this solves your problem, kindly mark it as the best answer.

Thanks,
Vatsal
This was selected as the best answer
James LoghryJames Loghry
Jack,

Part of your issue is that your querying in a for loop.  Not only that, but you're querying 3 times in each instance.  You'll want to look into how to "bulkify" your code.  In otherwords, you'll want to handle scenarios where you have multiple accounts, not just one account.

Part of being on a multi-tennant architecture means Salesforce has to make all of it's applications work nicely with one another.  One of the ways it does this is by enforcing Governor Limits.  In particular, there is a governor limit on the number of SOQL queries you can make in a single transaction.  This limit is 100 queries. 

In your case, this means that if your Account query returns more than 33 rows, then you'll hit a governor limit.

You can fix your code by moving your queries outside of the for loop.

Below is an example of what you might modify lines 10-19 in your code above to look like:

Set<Id> clientIds = (new Map<Id,Account>(clients)).keySet();

Integer a = b = c = 0;
for(List<Task> tl : [Select Id,CallType__c From Task Where WhatId in :clientIds And CallType__c in (‘a’,’b’,’c’)]){
    for(Task t : tl){
        a += (‘a’ == t1.CallType__c) ? 1 : 0;
        b += (‘b’ == t1.CallType__c) ? 1 : 0;
        c += (‘c’ == t1.CallType__c) ? 1 : 0;
    } 
}

No guarantees on whether or not it compiles though.
James LoghryJames Loghry
Also, if you could paste the code in using the < > option in the WSYWIG editor instead of providing the screenshot of your code, it would be appreciated.  Thanks.
Lee SinLee Sin
@Vatsal Kothari,
where is advisorList defined?
Vatsal KothariVatsal Kothari
Hi Jack,

Replace the 21st line with below code:
for(Account advisor : [Select id,Client_territories__c, name from Account where account_type__c = 'advisor']){