+ Start a Discussion
surekha Ksurekha K 

Workflow Email alert for existing records based on Last Modified Data

Hi All,

   I have a scenario, I want to send an email alert for the opportunity owner if the opportunity was not updated since 30 days based on Last Modified Date.I was given Evaluation Criteria and Rule Criteria as below.

Evaluation Criteria: Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria
Rule Criteria: (Opportunity: StageNOT EQUAL TO Order Won) AND (Opportunity: StatusEQUALSActive)

And I used 3 email alerts and one field update.
First email alert on 30 Days after Last Modified Date.
Second email Alert on 37 Days after Last Modified Date.
Third Email Alert on 44 Days after Last Modified and also 'Status Field' should be updated as a value 'dropped'.

My problem is it is only working for newly created and edited records.
Its not working for existing records.
If I edit and save all the opportinities the last modified date will change.But I dont want to do like this.Without touching the records i want to fire this workflow.

Or else is there any other way to send email alert to the oportunity owner.
If any one aware on this please let me know as soon as possible.

Thanks in Advance

Regards,
Surekha
AshwaniAshwani
You should use time trigger workflow instead on immiediate workflow. Also workfow is fired when new record is inserted/updated and meet criteria so old record won't be recognized.

Time based workflow should work otherwise batch can be used to make this work.
surekha Ksurekha K
Hi Avilion,

Thanks for your reply.

I used Time trigger  workflow only..i.e.,  Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria.

Thanks & Regards,
Surekha

KaranrajKaranraj
Hi Suresh,

If you want to perform this logic for old records also then you have to write batch class and schedule the batch class to run every day.

Step 1: In the batch class query fetch all the Opportunity records which LastModifiedDate is greater than 30 days.
Step 2: In the execute method, loop through the list of records and perform your business logic.
Step 3[optional]: Either you can send the status of the batch class to the any admin users.

Here is the link to know about Batch class :- http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
Here is the blog post : http://blog.shivanathd.com/2013/01/how-to-write-batch-class-in.html

surekha Ksurekha K
Hi Karanraj,

    Thanks for your post.If it is not too much can you please help me with the complete code for my requirement.

Actually i have 112 records with the Last Modified Date before July 7th and from 8th July to 7th Aug 603 records was there.And how  to appoach for this and what about for newly creating records?Workflow is not required for newly creating records?Batch apex is taking care of newly creating records also? 


Thanks & Regards,
Surekha
surekha Ksurekha K
Hi All,

Finally i done this requirement.everything is working fine...
But I am unable to cover the test class.I wrote the test class but it covered only 25%..
If any one good in to write the test class Please help me out .
The below is the batch class for existing records.

global class EmailRemindersToOppOwnerExisting implements Database.Batchable<sObject>,Schedulable{
   
    global void execute(SchedulableContext sc){
    Database.executebatch(this);
    }   
    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator([SELECT Id,Name,LastModifiedDate,StageName,Status__c,Start_Date__c,CloseDate,Amount,Winnability__c,Account.name,owner.email,owner.name FROM Opportunity where StageName!=:'Order Won' and Status__c=:'Active' and  OwnerId='005N0000000oJIt' and LastModifiedDate<2014-08-03T00:00:00.000Z]);
    }
   
     global void execute(Database.BatchableContext BC, List<Opportunity> Opportunitylist) {
         list<String> FirstRemainderEmails=new list<string>();
         list<String> SecodRemainderEmails=new list<string>();
         list<String> ThirdRemainderEmails=new list<string>();
         List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
         list<Opportunity> OpportunityToUpdate=new list<Opportunity>();
         System.debug('Opportunitylist ******** '+Opportunitylist);
         for(Opportunity opp : Opportunitylist){
          DateTime todaydate = System.now();
          Date myDate = date.newinstance(todaydate.year(), todaydate.month(), todaydate.day());
          String OwnerName=opp.Owner.name;
          DateTime LastModifiedDate;
          LastModifiedDate = opp.LastModifiedDate;
          String lstdate;
          lstdate= LastModifiedDate.format('dd MMMM yyyy');
          String opportunityName=opp.name;
          String oppAccount=Opp.Account.name;
          String oppStage=opp.stagename;
          String oppAmount=String.valueof(opp.Amount);
          String oppStartDate=String.valueof(opp.Start_Date__c);
          String oppCloseDate=String.valueof(opp.CloseDate);
          String oppWinnability=String.valueof(opp.Winnability__c);
          if(myDate==Date.ValueOf('2014-09-02')){
              FirstRemainderEmails.add(opp.owner.email);            
                String OpportunityId1=opp.id;
                String url = 'https://cs6.salesforce.com/'+OpportunityId1;               
                Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                 mail.setToAddresses(new String[] { opp.owner.email,})                 
                 mail.setSubject('First reminder to update Opportunity');
                 String messageBody ='<html><body><p>Dear '+ OwnerName +',<br/><br/>'+
                     </body></html>';
                    mail.setHtmlBody(messageBody);    
                    mails.add(mail);
          }
          else if(myDate==Date.valueOf('2014-09-03')){
                SecodRemainderEmails.add(opp.Owner.email);             
                String OpportunityId2=opp.id;
                 String url = 'https://cs6.salesforce.com/'+OpportunityId2;
               
                 Messaging.SingleEmailMessage mail1 = new Messaging.SingleEmailMessage();
                 mail1.setToAddresses(new String[] { opp.owner.email });               
                 mail1.setSubject('Second reminder to update Opportunity');
                 String messageBody ='<html><body><p>Dear '+ OwnerName +',<br/><br/>'+ </body></html>';
                    mail1.setHtmlBody(messageBody);    
                    mails.add(mail1);
            }
       else if(myDate==Date.valueOf('2014-09-04')){
               opp.Status__c ='Dropped';
               OpportunityToUpdate.add(opp);
               ThirdRemainderEmails.add(opp.owner.email);               
                 String OpportunityId3=opp.id;
                 String url = 'https://cs6.salesforce.com/'+OpportunityId3;
                 Messaging.SingleEmailMessage mail2 = new Messaging.SingleEmailMessage();
                 mail2.setToAddresses(new String[] { opp.owner.email });
                 mail2.setSubject('Third reminder to update Opportunity');
                String messageBody ='<html><body><p>Dear '+ OwnerName +',<br/><br/>'+</body></html>';
                mail2.setHtmlBody(messageBody);    
                 mails.add(mail2);
          }
      }
         if(!mails.isEmpty()){
           Messaging.SendEmail(mails);
          }
        update OpportunityToUpdate; 
     }
    
     global void finish(Database.BatchableContext BC){    
   }
    
}

Note: The test class is in the next post!
surekha Ksurekha K
Test Class:

@isTest
Class TestEmailRemindersToOppOwnerExisting {
    static testMethod void testSchedule(){
       Test.StartTest();
       EmailRemindersToOppOwnerExisting sh1=new EmailRemindersToOppOwnerExisting();
       String sch = '0 0 23 * * ?';
       system.schedule('Test Territory Check', sch, sh1);
       Test.stopTest();
    }
   
    public static testMethod void testEmailRemindersToOppOwnerExisting(){
        //String query='SELECT Id,Name,LastModifiedDate,StageName,Status__c,Start_Date__c,CloseDate,Amount,Winnability__c,AccountId,owner.email,owner.name FROM Opportunity where StageName!=:Order Won and Status__c=:Active and  OwnerId=005N0000000oJIt and LastModifiedDate<2014-07-21T00:00:00.000Z';
       
        Account mainAccount= new Account(  
          Name = 'Telia Test',
          Industry ='BFSI-CORE'
         
          );
         insert mainAccount;
        
         Contact mainContact = new Contact(
          firstName = 'Test',
          lastName = 'Contact1',
          email = 'surekha123@gmail.com',
          accountId = mainAccount.id
          );
         insert mainContact;
       
         Opportunity mainOpp = new Opportunity(
          Name = 'Test Opportunity',
          AccountId = mainAccount.Id,
          Type = 'New Contract',
          StageName = 'Qualified Lead',
          Amount = 1000000,
          Winnability__c = '10%',
          Status__c='Active',
          CloseDate = Date.today() + 5
          );       
          insert mainOpp;
         
          List<Opportunity> OpportunityToUpdate=new List<Opportunity>(); 
          OpportunityToUpdate.add(mainOpp)  ;                    
       
           Test.StartTest();
           Database.BatchableContext BC;
           EmailRemindersToOppOwnerExisting obj=new EmailRemindersToOppOwnerExisting();
           ID batchprocessid = Database.executeBatch(obj );
           obj.start(BC);       
           obj.execute(BC,OpportunityToUpdate);
           obj.finish(BC);
           Test.StopTest();
      }
     
}

Can any one aware on this please help me out...

Regards,
Surekha