2

I am getting this error message Too many query rows 500001. My trigger creates a task each time an email message is sent with a WhoID based on the personal or work email fields of a contact, are there any steps I can take to lock it down.

Hope it helps,

trigger EmailToTask on EmailMessage (after insert) {
list<Task> lstTask          = new list<Task>();
map<String,Id> mapEmail     = new map<String,Id>();

for(Contact con: [SELECT Id,Email,npe01__WorkEmail__c,npe01__HomeEmail__c FROM Contact]){
    if(string.isNotBlank(con.Email))
        mapEmail.put(con.Email,con.Id);
    if(string.isNotBlank(con.npe01__WorkEmail__c))
        mapEmail.put(con.npe01__WorkEmail__c,con.Id);
    if(string.isNotBlank(con.npe01__HomeEmail__c))
  mapEmail.put(con.npe01__HomeEmail__c,con.Id);
}
 for (EmailMessage e : Trigger.new) {
   if (e.RelatedToId != NULL 
 && (mapEmail <> NULL && !mapEmail.isEmpty())
      ) {
       Task t =  new Task();
       t.ActivityDate  = date.today();
       t.Description   = e.TextBody;
       t.Move_Type__c  = 'Personal Note/Email from Donor';
       t.Priority      = 'Normal';
       t.Status        = 'Completed';
       t.Subject       = e.Subject;
       t.WhatId        = e.RelatedToId;
       if(mapEmail.containsKey(e.ToAddress)){
            t.WhoId = mapEmail.get(e.ToAddress);
       }     
       lstTask.add(t);
    }
}
if(lstTask <> NULL && !lstTask.isEmpty()){
    insert lstTask;
}}
  • How many contacts do you have in the system? That first query could be a problem – curtisk Jul 11 '17 at 2:40
  • Quite a ton, about 50k, is there a way to lock it down or invoke a WHERE statement, such as WHERE email = .eToAddress or something? – Medhanie Habte Jul 11 '17 at 2:42
1

If you want to query more than 50000 records then you need to use the following syntax:

for(List<Contact> contactsList: [SELECT Id,Email,npe01__WorkEmail__c,npe01__HomeEmail__c FROM Contact]){
  for(Contact con: contactsList){
    if(string.isNotBlank(con.Email))
      mapEmail.put(con.Email,con.Id);
    if(string.isNotBlank(con.npe01__WorkEmail__c))
      mapEmail.put(con.npe01__WorkEmail__c,con.Id);
    if(string.isNotBlank(con.npe01__HomeEmail__c))
      mapEmail.put(con.npe01__HomeEmail__c,con.Id);
  }
}
1

One way you can reduce your query rows is to apply the filters you are testing for in the first for loop.

However, you want to avoid field != null queries as they are very expensive and result in a full table scan.

One thing you can do is make three calculation fields on your contact object

eg Work_Email_Not_Blank__c and define it something like:

if (isBlank(con.npe01__WorkEmail__c),FALSE,TRUE)

THEN you need to use a workflow or process to copy these fields into static versions eg Work_Email_Not_Blank__c -> Work_Email_Not_Blank_Static__c

Then you can change your top query to something like:

SELECT Id,Email,npe01__WorkEmail__c,npe01__HomeEmail__c 
FROM Contact
WHERE Work_Email_Not_Blank_Static__c = true 
  AND Work_Email_Not_Blank_Static__c
  AND Work_Email_Not_Blank_Static__c
  • Added workflow step - calcs won't help unless they are copied to a standard text field that can be indexed – Caspar Harmer Jul 11 '17 at 2:50

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.