How to execute workflows on records that users don't touch

Question

I have a scenario where I have to create an automation on opportunity. When opportunity on stage 1 for more than 14 days & there are no open task in opportunity & last activity age should be more than 12 days so an email reminder goes to opportunity’s owner to remind him/her to move the opportunity to stage 2. Same with stage 2 to 3 and 3 to 4. I tried to achieve it through workflow and put the criteria: 1. Opportunity stage equal to prospecting 2. Open task count equal to 0 ( I have created a custom number field to update it through trigger to count the open task) 3. Last activity age >= 12 ( last activity age is a custom filed and count last activity age)

Workflow evaluation criteria set as “Created and edited to subsequently meet the criteria”

The problem here is we have many opportunities which are untouched from long time and to meet with the criteria either I need to edit them on by one or need to update through data loader. If I created a new record and keep it as it is untouched for long time it will not catch by workflow rule because on creation time it doesn’t meet the criteria and after when record is eligible to meet the criteria I have to edit it to meet the criteria.

I want anything like batch or trigger to keep checking opportunity records and whenever any record comes under the criteria. An email goes to opportunity owner.

Any approach and help appreciated.

Answer 1

Here, I think you want to use scheduled apex. This is the only way to "notice" the passage of time on existing records or to retro-fit workflow time triggers on data after x-days.

With scheduled apex, you write up an apex class and can trigger it to run every morning, at say 4am, to query for all opportunities and manage them that way. Often the easiest thing to do in the scheduled apex is simply "update" the opportunity record, and use workflow to then send your e-mails etc. (reduces the amount of testing you have to do! and makes the actions more configurable).

To schedule some apex, create a class like this:

global class checkOpps Implements Schedulable {

    global void execute(SchedulableContext sc) {
        checkMyOpps();
    }

    public void checkMyOpps() {

        List<Opportunity> affected = [SELECT Id, Name 
                                     FROM Opportunity
                                     WHERE StageName='Prospecting' 
                                     AND Task_Count__c = 0 AND Last_Active__c >= 12];

       // either do your logic actually here with a Message.SingleEmailMessage here, or just fire an update
       for(Opportunity thisOpp : affected) {
           // Some dummy field to trigger the workflow (gross huh!)
           thisOpp,Trigger_Field__c = thisOpp.Trigger_Field__c + 1;
       }

      update affected;
    }

}

Then in your apex class list, click "Schedule Apex" and set this class up to run at 4am every day. This will keep your opportunities nice and tidy.