+ Start a Discussion
HotCopper_SalesHotCopper_Sales 

APEX triggers to produce NEXT ACTION (GTD) methodology

I have created a trigger that 

IF an activity is created or updated
 AND that task is related to an opportunity
then fill in 3 custom fields of that related opportunity (next action, next action date, next action id) with the earliest due activity details. 

I have a problem though. 
If i mark an activity as complete, i run in to an error because the SQQL query will return nothing. 

How can i optimize my code so that 
IF there are NO activities associated with that opportunity (No results from SQQL query) then do something else?

 
trigger TaskUpdateTrigger on Task (after insert, after update) {
  
    // When a task inserted/updated
   	For (Task t: Trigger.new){
           
            // if task is related to an opportunity
            String relatedOppID = String.valueOf(t.whatId); 
            if(relatedOppID.substring(0,3)=='006'){
                            
                // get all tasks associated with that opportunity
                Task[] relatedTasks = [SELECT Id, Subject, ActivityDate FROM Task 
                                       WHERE WhatId=:t.WhatId
                                       AND ActivityDate != null
                                       AND Status != 'Completed'
                                       ORDER BY ActivityDate ASC
                                       LIMIT 1];
				               
                // get the related opportunity 
                Opportunity relatedOpp = [SELECT Id, NextStep FROM Opportunity
                                          WHERE Id =:t.WhatId];
                
             
                // update Opportunity SObject with Next Action details
                relatedOpp.Next_Action__c = relatedTasks[0].Subject;
                relatedOpp.Next_Action_Date__c = relatedTasks[0].ActivityDate;
                relatedOpp.Next_Action_ID__c = relatedTasks[0].Id;
                                 
                // update database
                update relatedOpp;    
            }
    }  
}

 
Best Answer chosen by HotCopper_Sales
VineetKumarVineetKumar
As a best practice you do not write SOQL query inside a for loop and no DML statement inside the for loop.
Below is an optimized code :
trigger TaskUpdateTrigger on Task (after insert, after update) {    
    
    Schema.DescribeSObjectResult r = Opportunity.sObjectType.getDescribe();
    String keyPrefix = r.getKeyPrefix();
    System.debug('Prefix = '+keyPrefix );

    Set<Id> opportunityIdSet = new Set<Id>();
    for (Task t: Trigger.new){
        // if task is related to an opportunity
        String relatedOppID = String.valueOf(t.whatId); 
        if(relatedOppID.startsWith(keyPrefix)){
            opportunityIdSet.add(t.WhatId);
        }
    }
    Map<Id, Task> opportunityIdTaskMap = new Map<Id, Task>();    
    for(Task thisTask : [SELECT Id, Subject, ActivityDate, WhatId FROM Task 
                                       WHERE WhatId IN: opportunityIdSet
                                       AND ActivityDate != null
                                       AND Status != 'Completed'
                                       ORDER BY ActivityDate DESC]){
        //since the query is ordered in descending order, the map will be overwritten with the latest activity.
        opportunityIdTaskMap.put(thisTask.WhatId, thisTask);    
    }
    
    List<Opportunity> opportunityList = new List<Opportunity>();
    for(Id thisOpportunity : opportunityIdTaskMap.keySet()){
        // update Opportunity SObject with Next Action details
        Opportunity opportunityRecord = new Opportunity();
        opportunityRecord.Id = thisOpportunity;
        opportunityRecord.Next_Action__c = opportunityIdTaskMap.get(thisOpportunity).Subject;
        opportunityRecord.Next_Action_Date__c = opportunityIdTaskMap.get(thisOpportunity).ActivityDate;
        opportunityRecord.Next_Action_ID__c = opportunityIdTaskMap.get(thisOpportunity).Id;
        opportunityList.add(opportunityRecord);
    }
    
    if(!opportunityList.isEmpty()){
        update opportunityList;
    }
}


All Answers

HotCopper_SalesHotCopper_Sales
Something like 

if(relatedTasks == 0 )
then
 doSomthingElse();

 
VineetKumarVineetKumar
As a best practice you do not write SOQL query inside a for loop and no DML statement inside the for loop.
Below is an optimized code :
trigger TaskUpdateTrigger on Task (after insert, after update) {    
    
    Schema.DescribeSObjectResult r = Opportunity.sObjectType.getDescribe();
    String keyPrefix = r.getKeyPrefix();
    System.debug('Prefix = '+keyPrefix );

    Set<Id> opportunityIdSet = new Set<Id>();
    for (Task t: Trigger.new){
        // if task is related to an opportunity
        String relatedOppID = String.valueOf(t.whatId); 
        if(relatedOppID.startsWith(keyPrefix)){
            opportunityIdSet.add(t.WhatId);
        }
    }
    Map<Id, Task> opportunityIdTaskMap = new Map<Id, Task>();    
    for(Task thisTask : [SELECT Id, Subject, ActivityDate, WhatId FROM Task 
                                       WHERE WhatId IN: opportunityIdSet
                                       AND ActivityDate != null
                                       AND Status != 'Completed'
                                       ORDER BY ActivityDate DESC]){
        //since the query is ordered in descending order, the map will be overwritten with the latest activity.
        opportunityIdTaskMap.put(thisTask.WhatId, thisTask);    
    }
    
    List<Opportunity> opportunityList = new List<Opportunity>();
    for(Id thisOpportunity : opportunityIdTaskMap.keySet()){
        // update Opportunity SObject with Next Action details
        Opportunity opportunityRecord = new Opportunity();
        opportunityRecord.Id = thisOpportunity;
        opportunityRecord.Next_Action__c = opportunityIdTaskMap.get(thisOpportunity).Subject;
        opportunityRecord.Next_Action_Date__c = opportunityIdTaskMap.get(thisOpportunity).ActivityDate;
        opportunityRecord.Next_Action_ID__c = opportunityIdTaskMap.get(thisOpportunity).Id;
        opportunityList.add(opportunityRecord);
    }
    
    if(!opportunityList.isEmpty()){
        update opportunityList;
    }
}


This was selected as the best answer
HotCopper_SalesHotCopper_Sales
Thank You @VineetKumar.  I consider this very good and advanced code. Well done!
I understand most of it. 

Are you able to add one small modification?

if i mark the last associated activity as complete - it should remove that activity from the 3 Opportunity next action custom fields. 

 
VineetKumarVineetKumar
I'm not sure if I totally understood this. But can you elaborate it a bit more.
As per my understanding, you are expecting if the latest activity is complete, then not update the opportunity or something?