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?
WhoId
. It can be aLead
or aContact
. So you won't be able to add a formula to theTask
object to access theLastOpenedDate__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 toActivityDate >= :TheLeadRelated.LastOpenedDate__c