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
Elliot32Elliot32 

SOQL - Invalid Bind Expression - Datetime Comparison

Hello Forum,

Disclaimer: Apex novice.

I am having a slight issue with a Task Trigger. I am attempting to rollup the number of Tasks to a Lead record that fit the following criteria:
- Type = 'Call'
- Status = 'Completed'
- The Activity_Date_Time__c field (datetime) must be greater than the Lead.Last_Transfer_Date_Time__c field (datetime)

Please see the below code:
  
trigger EPS_CallsSinceTransfer_v3 on Task (after insert, after update, after delete, after undelete) {

    List<Id> leadIdList = new List<Id>();
    
    If (Trigger.isInsert || Trigger.isUndelete) {
        For (Task t : Trigger.new) {
            leadIdList.add(t.WhoId);
        }
    }
    
    If (Trigger.isDelete || Trigger.isUpdate) {
        For (Task t : Trigger.old) {
            leadIdList.add(t.WhoId);
        }
    }
    
    List<Lead> leadUpdateList = new List<Lead>();
    
    For (Lead led : [SELECT Id, Calls_this_Cycle__c, Last_Transfer_Date_Time__c, (SELECT Id, Activity_Date_Time__c FROM Tasks WHERE (Type = 'Call' AND Status = 'Completed') AND Activity_Date_Time__c >: Lead.Last_Transfer_Date_Time__c) FROM Lead WHERE Id =: leadIdList]) {
        led.Calls_this_Cycle__c = led.Tasks.size();
        leadUpdateList.add(led);
        update leadUpdateList;
    }
}

Each time I attempt to save the record I get the following error:

Error: Compile Error: Invalid bind expression type of Schema.SObjectField for column of type Datetime at line 19 column 203

Any ideas?

Thanks,
Elliot
JeffreyStevensJeffreyStevens
Ya - I think you've got a problem where you're doing Activity_DAte_Time__c > :Lead.Last_Transfer_Date_Time__c.

The sObject Lead really isn't populated at that point.

I'm not sure you can do that with the nested SOQL's.
JeffreyStevensJeffreyStevens
And - ofcourse - you should never do the DML (line 22) inside of a loop.

Try something like this ... 
// From Line 19....

// Get all of the Tasks for the Leads & build a map of map<LeadId,list<Tasks>>
map<id,list<Task>> mLeadTasks = new map<id,list<Task>>();
list<Task> Tasks = [SELECT id,whoId FROM Task WHERE WhoId IN :leadIdList]();
for(Task t :tasks) {
  list<task> iterTasks = new list<task>();
  if (mLeadTasks.containsKey(t.whoId)) {
    iterTasks = mLeadTasks.get(t.whoId);
  }
  iterTasks.add(t);
}

// Now you have a map of <LeadId,list<tasks>>
list<lead> leadsToUpdate = [SELECT id, otherFields... FROM Lead WHERE id IN :mLeadTasks.keyset()];
for(Lead l :leadsToUpdate) {
  if(mLeadTasks.containsKey(l.id)) {
    list<task> iterTasks = mLeadTasks.get(l.id);
    for(Task t :iterTasks) {
      // TODO - count up stuff you need from the tasks, etc
    }
    l.FieldsToUpdate = fields added up from TODO
  }
}

if(LeadsToUpdate.size()>0) {
  update LeadsToUpdate();
}

Okay - so that might have got a bit messy - and my logic might have started to break down a bit at the end.  But basically, I think you are correct in the first part - get a list of LeadIds from the triggered tasks.  But after you have those Ids, I think you need to go and get the tasks associagted for those Leads, and do the counting, selecting, etc.  Then get the list of Leads, and update the field(s) on the Lead.

Hope that helps