2

I need to convert a cross-filter report of Leads and Activities to a SOQL query.

Filters

  • All Leads
  • Created Date: All Time

Cross Filters

Leads without Activities

  • Subject contains: voicemail, call, etc
  • Created Date equals LAST 90 DAYS

This is what I have done so far, I'm struggling to find the logic to only include "Leads without valid activities in the last 90 days":

[SELECT Id, (SELECT Id FROM Tasks 
                       WHERE CreatedDate <= :d 
                         AND Subject LIKE :subjects)
            FROM Lead WHERE OwnerId != sfAdmin.Id]
//d = "LAST 90 Days in my code"
//subjects = "List of all the valid subjects"
//sfAdmin.Id = "Id of a specific user"
0

1 Answer 1

4

This query is actually not supported in SOQL, if you want your root query object to remain Lead. With other child objects, you could do it via Left Inner Join, also known as semi join inner select. Here's what it would look like:

SELECT ... FROM Lead WHERE Id IN (
    SELECT WhatId FROM Task
    WHERE CreatedDate = LAST_90_DAYS
    AND Subject IN ('Voicemail, 'Call')
)

However, trying to run this query will yield an error:

Entity 'Task' is not supported for semi join inner selects

Your alternatives are to create a rollup that you can use as a filter, or to query the Task records as step 1, then use it to filter Lead ids in step 2. The latter would look like:

Set<Id> leadIds = new Map<Id, AggregateResult>([
    SELECT WhatId Id FROM Task
    WHERE What.Type = 'Lead'
    AND CreatedDate = LAST_90_DAYS
    AND Subject IN ('Voicemail', 'Call')
    GROUP BY WhatId
]).keySet();
List<Lead> filteredLeads = [SELECT ... FROM Lead WHERE Id IN :leadIds];

If you don't mind switching your primary query object to be Task, it may be simpler to do that, and you can do your filtering accordingly.

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.