Syntax Highlighter JS

Thursday, June 14, 2012

Changes to SOQL queries in salesforce?

Previously, I created a class that could be used to automatically send reminder emails about approvals to users.  This class has been in production with any issues or changes since my last deployment on 5/4/2012.

Yesterday, I went to deploy a totally unrelated change set that was comprised of just one new field and that field wasn't related to the approvals at all.   Upon trying to deploy this change, I was greeted with this error message:


Since Salesforce won't let you deploy code with such errors or conflicts, this code hasn't changed for months, and this error isn't related to any other logic (i.e. validation rules, etc) outside of the class, the change had to be on the Salesforce platform itself.  I do keep up with release notes and known issues, so it is a fairly safe bet to say this is an undocumented change.

After some testing, I determined that the issue was that the WI.Actor.Id was now null in the execute method's call to ApprovalReminderEmails.SendReminderEmail.

This data was previously being populated by this query:


SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, 
(SELECT Id, Actor.email, Actor.Name FROM WorkItems)  
FROM ProcessInstance  
WHERE Status = 'Pending' AND IsDeleted = False AND SystemModStamp < Today 
 

After some more testing, I made the query read like this (note the addition of ActorID in blue):

SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, 
(SELECT Id, ActorID, Actor.email, Actor.Name FROM WorkItems)  
FROM ProcessInstance  
WHERE Status = 'Pending' AND IsDeleted = False AND SystemModStamp < Today 
 

Please note that you will need to change WI.Actor.ID to WI.ActorID  in the call to ApprovalReminderEmails.SendReminderEmail as well.

Here is the full updated class: global class ApprovalReminderEmails implements Database.Batchable<sObject>,Database.Stateful { /********************************************************************* Add/Edit these constants to contain your email template names PLEASE NOTE THAT THESE MUST BE VISUALFORCE TEMPLATES TO WORK! Also, the VF template must NOT have recipientType set in the <messaging:emailTemplate> tag or you will get the error: INVALID_ID_FIELD, WhatId is not available for sending emails to UserIds ***********************************************************************/ static final string OpportunityEmailTemplateName = 'Approval_Opportunity_Reminder'; // this method is used to get all reocrds that will be processed by execute method global Database.QueryLocator start(Database.BatchableContext bc){ // try to use the .type to determine object type in SOQL // instead of DescribeSObjectResult apex code String query = 'SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, (SELECT Id, ActorID, Actor.email, Actor.Name FROM WorkItems) FROM ProcessInstance WHERE Status = \'Pending\' AND IsDeleted = False AND SystemModStamp < Today'; if (test.IsRunningTest() == true) { query = 'SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, (SELECT Id, ActorID, Actor.email, Actor.Name FROM WorkItems) FROM ProcessInstance WHERE Status = \'Pending\' AND IsDeleted = False'; } // query w/o date when test return Database.getQueryLocator(query); } // querylocator start // find and process all pending approval requests that aren't deleted // and are older than today global void execute(Database.BatchableContext BC, List<sObject> scope) { // loop thru results for (SObject s : scope) { ProcessInstance PI = (ProcessInstance)s; for(ProcessInstanceWorkitem WI : PI.WorkItems) { ApprovalReminderEmails.SendReminderEmail(PI.TargetObject.ID, PI.TargetObject.Type, WI.ActorId, WI.Actor.Email, WI.Actor.Name, PI.TargetObject.Name); } // loop thru WorkItems } // loop thru scope which contains the process instance results } // end SendReminders global void finish(Database.BatchableContext info) { } // finish private static void SendReminderEmail(Id TargetID, String TargetObjectType, Id ActorID, string ActorEmailAddress, string ActorName, string TargetDesc) { id EmailTemplateID; // target object type, name and ID will be null in the test methods but work in non test system.debug('TargetObjectType = ' + TargetObjectType); if (TargetObjectType == 'Opportunity') { EmailTemplateID = FindEmailTemplateIDByDeveloperName(OpportunityEmailTemplateName); } /** << Insert code here for other objects & email templates >> **/ // Make sure this transaction won't fail due to lack of daily capacity Messaging.reserveSingleEmailCapacity(1); Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage(); String[] toAddresses; // email will be null during testing ... if (test.IsRunningTest() == true) { toAddresses = new String[] {'no-reply@salesforce.com'}; } else { toAddresses = new String[] {ActorEmailAddress}; } // prevent Email Exception due to null address during testing if (EmailTemplateID != null) { // use our template email.setTemplateId(EmailTemplateID); email.saveAsActivity = false; // populate the template w/ object data email.setTargetObjectId(ActorID); email.setWhatId(TargetID); } else { // use a generic HTML email when no template is setup / found // get the current salesforce instance to build the link with string domain = URL.getSalesforceBaseUrl().toExternalForm(); string TargetLink = domain + '/' + string.valueof(TargetID); email.setToAddresses(toAddresses); // set the subject on the email email.setSubject('Reminder: Item pending approval'); // set the body of the email email.setHTMLBody('Dear ' + ActorName + ', <br/><br/>' + 'The following ' + TargetObjectType + ' is still pending your approval:<br/>' + TargetDesc + '<br/>' + '<br/> Click this link to view the full record:<br/> ' + '<a href=\'' + TargetLink + '\'>' + TargetLink + '</a>' + '<br/>Please DO NOT reply to this email.' ); } // check for template id // send our email by creating an array of emails and calling the send email method. Messaging.SingleEmailMessage[] EmailsToSend = new Messaging.SingleEmailMessage[] { email }; Messaging.sendEmail(EmailsToSend); } // end send reminder email // this method could be moved to a utility class and made public . . . . public static ID FindEmailTemplateIDByDeveloperName(string DeveloperName) { id EmailTemplateID; List<EmailTemplate> EmailTemplateResults = new List<EmailTemplate>(); if (DeveloperName != null) { EmailTemplateResults = [SELECT Id, Name, DeveloperName, IsActive FROM EmailTemplate WHERE DeveloperName = :DeveloperName]; if (EmailTemplateResults.size() > 0) { if (EmailTemplateResults[0].IsActive == true) { EmailTemplateID = EmailTemplateResults[0].id; } // check for active email template } // check for null search results } // check for blank name return EmailTemplateID; } // FindEmailTemplateIDByDeveloperName static testmethod void TestSendReminderEmails() { /** Create test data - edit as needed for custom rules. If you don't have approval processes in place for your test data, you will get lower code coverage in apex testing I have caught system.DMLException of NO_APPLICABLE_PROCESS to prevent total failure of the test unit. **/ // Create 5 test accounts so that only one (5 Accs + 5 Oppty) // executebatch will be invoked during testing. List <Account> AccountsToInsert = new List<Account>(); for(integer i = 0; i<5; i++) { Account a = new Account(Name='Test Account' + i); AccountsToInsert.add(a); } // loop to create 5 accounts insert AccountsToInsert; // no approval process is defined for account in this code, // so we use it to test the generic / HTML template . . . try { for(Account a: AccountsToInsert) { // Create an approval for the account and submit Approval.ProcessSubmitRequest AccountRequest = new Approval.ProcessSubmitRequest(); AccountRequest.setObjectId(a.ID); Approval.ProcessResult AccountRequestResult = Approval.Process(AccountRequest); System.assert(AccountRequestResult.isSuccess()); } // loop thru accounts submitting them for approval } catch (DMLException e) { system.debug('>>>> NO APPROVAL PROCESS FOR ACCOUNTS!'); } // Create opportunities for the accounts date myDate = date.today(); List <Opportunity> OpportunitiesToInsert = new List<Opportunity>(); for(Account a: AccountsToInsert) { Opportunity MyOppty = new opportunity(Name='Test Opportunity for ' + a.name,CloseDate=myDate,StageName='Lead'); MyOppty.AccountId = a.Id; OpportunitiesToInsert.add(MyOppty); } // loop thru accounts insert OpportunitiesToInsert; try { for(Opportunity o: OpportunitiesToInsert) { // create an approval for the opportunity Approval.ProcessSubmitRequest OpptyRequest = new Approval.ProcessSubmitRequest(); OpptyRequest.setObjectId(o.ID); Approval.ProcessResult OpptyRequestResult = Approval.Process(OpptyRequest); System.assert(OpptyRequestResult.isSuccess()); } // loop thru opportunities } catch (DMLException e) { system.debug('>>>> NO APPROVAL PROCESS FOR OPPORTUNTIES!'); } /* End create test data */ test.startTest(); ApprovalReminderEmails ARE = new ApprovalReminderEmails(); // the batch size can be no larger than 10 due to current apex email limits integer batchSize = 10; database.executebatch(ARE, batchSize); //increase code coverage by directly calling the method //since it won't get called in testing due to null values returned during testing only //I am using a standard salesforce sample here but need to change this if you removed the sample ApprovalReminderEmails.FindEmailTemplateIDByDeveloperName('ContactFollowUpSAMPLE'); test.stopTest(); } // end test method } // end class Salesforce claims that they test user classes prior to installing updates to avoid exactly this kind of issue but maybe they just missed this one. If you have any information on exactly why this change occurred or what the change was, please comment below.

P.S.:  I posted this because it is important to realize that not everything in salesforce is versioned (CSS, SOQL query optimizer, etc) and because this can result in your instance breaking without warning.  Since salesforce is really good about updating their software, this is an on going concern for each update.  Hopefully salesforce will read this and add versioning to more of the platform.

11 comments:

  1. Wonderful Post!..Very helpful..
    I have similar requirement to send recurring emails to Approvers if they haven't take action yet.
    Few questions, " Will this code work if Approver is Queue(not a User)?". Also will it work for Custom object? Where should i set the object information in the code so that it will work only for that object alone?.
    Please reply. Your response is highly appreciated.

    ReplyDelete
  2. Vani,

    Yes, this will work for custom objects as the database table for approvals is the same for all objects.

    This could work for a queue but only if that queue has an email address defined and the address is valid.

    The database treats the actor (user or queue) as the same when it comes to determining the email.

    Thanks,

    Anthony

    ReplyDelete
  3. Anthony,
    Wonderful! Thanks for the prompt response..
    but still have some more queries...
    I have a queue with 5 members but need to send email alert of them. Will it possible?
    I want to set a custom object info and code should work only for that object. where can i define that in above code.
    I believe i can mention the Target object name as part of query itself that would restrict the code to work only for that object. Pls confirm.

    Thanks again,
    Vani

    ReplyDelete
  4. Vani,

    When you setup a queue, you have the option of defining an email address for it. This is the email address that would be logged as the email for the actor (approver) in the approval process. This email address is something you configure on your end. So it *could* be setup to email all members of the queue on your email server but this isn't built into Salesforce. In other words, Salesforce will not query each member of the queue's email address and bulk email all of them. Instead, you may setup a group, list serv or something similar to accomplish this task.

    My code is currently setup to send approval reminders for all objects that have pending approvals. However, you may be able to modify the SOQL's where clause to filter by the TargetObject.Type field such that you only send out reminders for given object(s).

    In your development environment or sandbox: Create some approval processes (for different objects), then submit approvals for records on those different objects and finally use force.com explorer to test and refine your SOQL until you are getting back the results that you want.

    Then you can plug the updated SOQL into my code and be ready to test and finally deploy.

    Thanks,

    Anthony

    ReplyDelete
  5. Vani,

    Another thought here, you could add the actor.type field to the query. Then check to see if that type is "User" or "Queue". When it is "Queue" and no actor.email is defined, you can then pull the Actor ID as it will be the queue ID.

    Then you can get a list of Users and Groups in the Queue by using a query like this (where MyQueueID is the bind var that holds your ActorID) :
    Select UserOrGroupId From GroupMember where GroupId = :MyQueueID

    With that list of ID's, you can then determine which are members and then query for those member emails.

    Thanks,

    Anthony

    ReplyDelete
  6. Great! This really helps!
    Thank you so much... I will play around with above options.
    Regards,
    Vani

    ReplyDelete
  7. Anthony

    would you have a sample VF template to demonstrate how that works ?

    Thanks

    ReplyDelete
    Replies
    1. Damien,

      Are you looking to limit the reminders to a given type? Or are you looking to email a queue?

      Delete
  8. Anthony,

    I am receiving these errors when running this code in my Sandbox:

    Send Email Failed. INVALID_EMAIL_ADDRESS
    Too many email Invocations 11

    Any ideas here?

    ReplyDelete
    Replies
    1. Ernest, check this https://teachmesalesforce.wordpress.com/2011/06/05/sending-more-than-10-e-mails/

      Delete