0

I have a Lead field named LastOpenedDate__c, and in a bulk context I'm trying to get a count of all tasks completed for each lead in a list, but only tasks completed on or after each lead's LastOpenedDate__c.

Conceptually, my code looks like this.

    for(Lead eachLead : [
        SELECT Id, (
            SELECT Id
            FROM Task
            WHERE Status = :TaskUtil.COMPLETED_STATUS
            AND Type IN :TaskUtil.getTouchpointTypeSet()
            AND ActivityDate >= :l.LastOpenedDate__c
        )
        FROM Lead l
        WHERE Id IN :this.recordIds
    ]) {
        this.currentTouchpointsById.put(
                eachLead.Id, Decimal.valueOf(eachLead.Tasks.size()));
    }

The error I get trying to save this code is:

expecting a colon, found 'l.LastOpenedDate__c'

I know what I'm trying to do generally works in an RMDBS with SQL. Is what I'm trying to do possible in SOQL? And if so, what's wrong with my syntax?

2
  • Nope. not possible in SOQL. I had the same question salesforce.stackexchange.com/questions/50397/… when i started with SOQL which didn't change yet. Jun 7, 2017 at 7:23
  • 2
    The problem here is that the Task object doesn't know the type of the related human on its field WhoId. It can be a Lead or a Contact. So you won't be able to add a formula to the Task object to access the LastOpenedDate__c field. I think the only way is to add a trigger which updates a new field on the task insert or update events. This new field should be of type checkbox and be equals to ActivityDate >= :TheLeadRelated.LastOpenedDate__c Jun 7, 2017 at 7:23

2 Answers 2

2

Usually, the common solution is to use a formula of type checkbox with, for example, the value:

ActivityDate >= Who.LastOpenedDate__c

Then, in the SOQL query, it would be possible to check if the result of this formula is true by using:

SELECT Id, (
   SELECT Id
   FROM Task
   WHERE Status = :TaskUtil.COMPLETED_STATUS
   AND Type IN :TaskUtil.getTouchpointTypeSet()
   AND Your_New_Formula__c = true
)

The problem here, is that the Task object doesn't know the type of the related human on its WhoId field. It can be a Lead or a Contact. So you won't be able to access the LastOpenedDate__c field.

The only solution I know is to add a trigger on the update and insert events of the task object. In this trigger, you have to query the related lead and then you set a new custom field of type checkbox to: ActivityDate >= :TheRelatedLead.LastOpenedDate__c.

4
  • I think you should undelete this one as it's a pretty standard solution (use a formula), and just explain why it doesn't work in this case.
    – Adrian Larson
    Jun 7, 2017 at 11:18
  • Hi @AdrianLarson ! It's been awhile. I removed my answer because it was a standard solution as you said. Jun 7, 2017 at 11:50
  • It has been a while indeed. Welcome back! :) Just because it's standard doesn't mean it's widely known enough. I think this is a quality answer.
    – Adrian Larson
    Jun 7, 2017 at 11:52
  • I get the solution but you would actually need two triggers, one on Task to handle insert and update, and one on the parent object (e.g., Lead in my case) to handle changes to the field of reference (e.g., LastOpenedDate__c).
    – Marty C.
    Jun 7, 2017 at 13:40
0

The solution I used in the end was to count the tasks one by one explicitly in code, which I knew was possible but was hoping for more elegant solution.

    // Compile the aggregate query results
    for(Lead eachLead : [
        SELECT Id, LastOpenedDate__c, (
            SELECT Id, ActivityDate
            FROM Tasks
            WHERE Status = :TaskUtil.COMPLETED_STATUS
            AND Type IN :TaskUtil.getTouchpointTypeSet()
        )
        FROM Lead
        WHERE Id IN :this.recordIds
    ]) {

        // Convenient local counters
        Integer touchpoints = 0;
        Integer currentTouchpoints = 0;

        // Count the tasks
        for (Task eachTask : eachLead.Tasks) {

            // Always counts as a historical touchpoint
            touchpoints++;

            // Conditionally counts as a current touchpoint
            if (eachTask.ActivityDate >= eachLead.LastOpenedDate__c) {
                currentTouchpoints++;
            }
        }

        // Remember the counters
        this.touchpointsById.put(eachLead.Id, touchpoints);
        this.currentTouchpointsById.put(eachLead.Id, currentTouchpoints);
    }

You must log in to answer this question.

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