1

I am trying to create a count summary of all calls logged by a specific sales rep (as Task) for each of his prospects (e.g. 8 calls for prospect A, 14 for B, etc.) using Apex.
Below is what I got so far, unfortunately, Salesforces complains: "Too many SOQL calls":

List<Contact> prospects = [SELECT id FROM Contact WHERE OwnerId = '00651000003CWWpAAO'];
for(Contact c: prospects){
    List<AggregateResult> countResult = [SELECT COUNT(Activity_Type__c) FROM Task WHERE WhatId = :c.Id AND Activity_Type__c  = 'Call']; 
    System.debug(countResult[0].get('expr0'));
}

What's a better way to approach this and get the desired data?

0

2 Answers 2

6

You can use an additional GROUP BY to get all the values at once.

List<Contact> prospects = [SELECT id FROM Contact WHERE OwnerId = '00651000003CWWpAAO'];
Map<Id, AggregateResult> results = new Map<Id, AggregateResult>(
  [SELECT COUNT(Id) callCount, WhoId Id 
   FROM Task 
   WHERE WhoId = :prospects AND Activity_Type__c = 'Call' 
   GROUP BY WhoId]
);

You can then loop through each contact and get the result from the Map.

0
6

You are running a SOQL query in a loop, which violates the first principle of bulkification. Simply move the query out of the loop and use GROUP BY:

SELECT COUNT(Activity_Type__c) count, WhatId
FROM Task
WHERE WhatId IN :prospects AND Activity_Type__c  = 'Call'
GROUP BY WhatId

Then your AggregateResult records will have count and WhatId properties you can access to get the total for each Contact.

(Side note: Are you sure you want to be querying WhatId? Contact relationships should be in WhoId).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .