function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Heather MickeyHeather Mickey 

What is the best way to handle this situation, and if it is in fact APEX any additional details will help

Best Answer chosen by Heather Mickey
Heather MickeyHeather Mickey
Ended up going a different route on writing the script. Below is the working APEX script which works beautifully and updates the field with accurate values. *Not including full Method Finish code as most of it is sending emails for batch run successful and batch run errors with list of errors. 
global class Acc_SetNextProgramDateBatch implements Database.Batchable<sObject>, Database.Stateful{
	global String query = 'SELECT Id,Acc_Next_Program_Date__c,(SELECT Id,AccountId__c,Program_Date__c FROM Program__r WHERE Status__c='Open' AND Program_Date__c >= TODAY ORDER BY Program_Date__c ASC LIMIT 1) FROM Account';

	//This list contains the IDs of Accounts, which produced failures
    global List<Id> accsIDsFailed= new List<Id>();   
     
    //This method implements the interface method start.
    global Database.QueryLocator start(Database.BatchableContext BC){       
        return Database.getQueryLocator(query);
    }
  
    //This method implements the interface method execute.
    global void execute(Database.BatchableContext BC, List<Account> scope){
        //Step through all account records
        for(Account acc : scope){
        	Date NextProgramDate; 
            //Assign the Next Program Date to the Account
            if(acc.Program__r != null) {
                for(Program__c ca : acc.Program__r){
                    if(ca.Program_Date__c != null) {
                    	NextProgramDate = ca.Program_Date__c;
                    }
                }
                acc.Acc_Next_Program_Date__c = NextProgramDate;
            }        
       }
        
       Integer i = 0;
       List<Database.SaveResult> updateResults = Database.update(scope, false);
       for (Database.SaveResult res : updateResults) {
           if (! res.isSuccess()) {
               // send Email for not updated accounts
               accsIDsWithFailure.add(scope[i].Id);
           }
           i++;
       }        
       
    }  
    
    //This method implements the interface method finish.
    global void finish(Database.BatchableContext BC) { }
}

 

All Answers

ManojjenaManojjena
Hi Heather Mickey,

Which situation you are talking about ?
Heather MickeyHeather Mickey
Oh now that was odd -- the rest of my post never posted. Apologizes and thanks for asking @Manoj Kumar jena

What is the best way to handle this situation, and if it is in fact APEX any additional details will help
as I am still new to APEX coding and testing.

I have a standard Account object who is the parent of custom Project object. Accounts can have multiple
projects (records). I'd like to have a field on the Account to reflect "Next Project Date" when the Project has a Status = "open".
Example:
**Pretend TODAY is 2015/05/26
Account a
Project 1 for Account a with Date=2015/05/24 and Status=closed
Project 2 for Account a with Date=2015/05/25 and Status=open
Project 3 for Account a with Date=2015/05/26 and Status=open
Project 4 for Account a with Date=2015/05/27 and Status=open
--The Next Project Date on a should be set to 2015/05/26

Account b
Project 5 for Account b with Date=2015/05/24 and Status=closed
Project 6 for Account b with Date=2015/05/25 and Status=open
Project 7 for Account b with Date=2015/05/27 and Status=open
Project 8 for Account b with Date=2015/05/28 and Status=open
--The Next Project Date on b should be set to 2015/05/27

Since Account is a standard object and Project is a custom object, I'm not able to use workflows/field updates to update the field. I'v also
tried a formula field, but that also doesn't work since this is a standard/custom object relationship.

I appreciate anyone's help and thank you greatly.
ManojjenaManojjena
Hi Heather Mickey,

You can achieve this by using a batch and schedule apex . You need to query from project where status=open and ORDER BY CreatedDate DESC LIMIT 1 .And it will  be inner quert of account .

Needs to update account date Next Project Date with the project date .

Interval of schedule apex you needs to decide how frequently you are updating or creating the project .

Let me know if it helps .

Thanks 
Manoj
 
Heather MickeyHeather Mickey

Hi Manoj Kumar jena,

Thank you. I thought the answer was going to be Apex class and batch...  I am not new to coding logic but I am new to Apex. I'm having a hard time of where to start to actually code. Would you have any tips?

I'm trying to review the Visualforce Workbook right now.. but anything to help me jumpstart with this scenario is greatly appreciated.

Thank you,

Heather
ManojjenaManojjena
Hi Heather Mickey,

If batch class will update once daily may be morning everyday will ok for you ?
In middle if you will update or or create any program it will not reflect again for updated result yoy need to wait for next day morning .
Confirm me I will help you to write code .
 
Heather MickeyHeather Mickey

Hi Manoj,

Thank you so very much! Yes, I would imagine the batch could run early AM (so it completes processing before anyone arrives at the office). I appreciate all of your help very, very much.

Thank you,

Heather
ManojjenaManojjena
Hi Heather Mickey,

You need to create below two classes . You need to take care of the field and relationship name .
  
global class BatchAccountUpdate implements Database.Batchable<sObject>{
    global Database.QueryLocator start(Database.BatchableContext BC){
        String query = 'SELECT Id,Name,Program_date__c FROM Account';
        return Database.getQueryLocator(query);
    }
   global void execute(Database.BatchableContext BC, List<Opportunity> scope){
        Set<Id> accountIdSet=new Set<Id>();
        for(Account acc : scope){
            accountIdSet.add(acc.Id);         
        }
		List<Account> accListToUpdate=new List<Account>();
       For(Account ac :[Select id,Date_c(SELECT id,Date__c,Status__c FROM Program__c WHERE Status='Open' ORDER BY Date__c DESC LIMIT 1 )FROM Account ]){
	   ac.Program_date__c=ac.Program__r[0].Date__c;
	     accListToUpdate.add(ac);
	   }
	   try{
	     update accListToUpdate;
	   }catch(DmlException de ){
	     System.debug(de);
	   }
    }   
    global void finish(Database.BatchableContext BC) { }
}
//////////////////
global class AccountUpdateSchedule implements Schedulable{
   global void execute(SchedulableContext SC) {
      BatchAccountUpdate bau=new BatchAccountUpdate();
   }
}

Once you save above two classes you need to schedule the class to execute daily morning .
To do the schedule you need to follow the below image .

User-added image

User-added image


You will find the above image if you follow below steps .
Click on Setup >Develope >apex classes >you will find first image >Select schedule Apex> give some name >select your schedule apex name >change the time as per you and configure as above image .

Let me know any issue .

Thanks 
Manoj
Heather MickeyHeather Mickey

Hi Manoj,

Thank you again. I'm getting an error on the SELECT for line 12; Would you understand why?

Also, just so I can ensure I am learning and understanding what each part means:
Lines 1-4: Why do we query all Accounts? I don't see where we use this again so I was trying to understand where we use this?
Lines 6-9: We are creating a  list of accounts and then creating a set where we assign each account id to the set. *Where do we use this Set again? (Maybe line 13 but both are declared using ac and not acc in line 13.)
Line 11: Create another list of accounts
Line 12: I do not quite understand how the logic of this line is working. I assume this is where we are selecting the ID and Next_Project_Date__c from Account object and selecting the ID, Date_c from the Program objected where Program Status__c = "Open" meanwhile listing the Program records in decending order and limiting to 1. *How does it know to limit to 1 per Program.AccountID? I was wondering how the logic in Line 12 is working? How does the script know to match Account.ID to Program.AccountID before assigning the Date_c from Program object to Next_Project_Date__c in Account object?
Line 13: Assign the Program_Date in Account object the value of the Date_c in the Program object
Line 16-19: Update Accounts with list created on line 11 and values assigned from line 12 & 13. Notate a debug if any error.

I understand the BatchExecutable class and the scheduling of APEX.

Thank you again so much for guiding me through this. I know this isn't your job and you are doing this from kindness and experience and I truly, truly appreciate it.

Thank you,

Heather
Heather MickeyHeather Mickey
---------------Updated the code with field names below, for your review ------------------
  1. global class SetNextProgramDateBatch implements Database.Batchable<sObject>{
  2.     global Database.QueryLocator start(Database.BatchableContext BC){
  3.         String query = 'SELECT Id,Name,Next_Program_Date__c FROM Account';
  4.         return Database.getQueryLocator(query);
  5.     }
  6.    global void execute(Database.BatchableContext BC, List<Account> scope){
  7.         Set<Id> accountIdSet=new Set<Id>();
  8.         for(Account acc : scope){
  9.             accountIdSet.add(acc.Id);        
  10.         }
  11.         List<Account> accListToUpdate=new List<Account>();
  12.        for(Account ac :[SELECT Id,Next_Program_Date__c(SELECT Id, Account__c, Program_Date__c,Status__c FROM Program__c WHERE Status__c='Planned' ORDER BY Program_Date__c DESC LIMIT 1)FROM Account ]){
  13.        ac.Next_Program_Date__c=ac.Program__c[0].Program_Date_vod__c;  
  14.          accListToUpdate.add(ac);
  15.        }
  16.        try{
  17.          update accListToUpdate;
  18.        }catch(DmlException de ){
  19.          System.debug(de);
  20.        }
  21.     }  
  22.     global void finish(Database.BatchableContext BC) { }
  23. }
ManojjenaManojjena
Hi Heather Mickey ,

Above code is working for you or not ?
Heather MickeyHeather Mickey
Hi Manoj,
It is not working. Error on line 12 "Unexpected SELECT"

Thank you,
Heather
ManojjenaManojjena
Hi Heather Mickey,

Add a comma(,) before second select in that query at iine 12.
Heather MickeyHeather Mickey

Hi Manoj,

Thank you. It now has no errors. I created an Apex Test Class, as well. I scheduled the BatchSched but all Account (object) Next Program Date (fields) are null. There are many open Programs, so something isn't working since nothing was populated on the account field.

Would you be able to explain how the code is supposed to work (the questions I asked above and where the relationship link is between Program and Account ID)? Maybe I can help figure out where the issue is if I understand the code better.

Thank you so much,
Heather

-------------------------------------------------Code Used-------------------------------------------------

  1. global class SetNextProgramDateBatch implements Database.Batchable<sObject>{
  2.     global Database.QueryLocator start(Database.BatchableContext BC){
  3.         String query = 'SELECT Id,Name,Next_Program_Date__c FROM Account';
  4.         return Database.getQueryLocator(query);
  5.     }
  6.    global void execute(Database.BatchableContext BC, List<Account> scope){
  7.         Set<Id> accountIdSet=new Set<Id>();
  8.         for(Account acc : scope){
  9.             accountIdSet.add(acc.Id);        
  10.         }
  11.         List<Account> accListToUpdate=new List<Account>();
  12.        for(Account ac :[SELECT Id,Next_Program_Date__c,(SELECT Id, Account__c, Program_Date__c,Status__c FROM Program__r WHERE Status__c='Open' ORDER BY Program_Date__c DESC LIMIT 1)FROM Account ]){
  13.        ac.Next_Program_Date__c=ac.Program__r[0].Program_Date_vod__c;  
  14.          accListToUpdate.add(ac);
  15.        }
  16.        try{
  17.          update accListToUpdate;
  18.        }catch(DmlException de ){
  19.          System.debug(de);
  20.        }
  21.     }  
  22.     global void finish(Database.BatchableContext BC) { }
  23. }
------------------------------------------Schedule Code------------------------------------------------
global class SetNextProgramDateBatchSched implements Schedulable{
   global void execute(SchedulableContext SC) {
      SetNextProgramDateBatch bac =new SetNextProgramDateBatch();
   }
}

-------------------------------------------Test Code-------------------------------------------------------
@isTest
public class SetNextProgramDateBatchTest {
    
    static testMethod void myTest() { 
        // create an account with a program to test logic
        Account testAcc = new Account(LastName = 'test');
        insert testAcc;
        
        Program__c  testProgram = new Program__c (Account__c = testAcc.Id, Status__c = 'Open', Program_Date__c = Date.today());
        insert testProgram;
  
        Test.StartTest();
        
        SetNextProgramDateBatch myBatch = new SetNextProgramDateBatch();
        Database.executeBatch(myBatch);        
        Test.StopTest();
    }
}
ManojjenaManojjena
Hi Heather Mickey,

Basically  when ever you will schedule the schedule class it will execute in that particular time and the execute method of scheduler class will execute .In side that the batch class instance will create and execute the batch class .

When we call the batch class first teh start method will execute and it will execute the query and the list of account will pass to the  scope .
Execute method will execute based on your record size .It will divide the record in to different chunk of records defult batch size will 200 .As we have not difined any batch size .

Assume you have 2000 account record execute method will execute 10 times it will collect 20 id in the set and query 200 account and related program but as we have given one filter like only open program and latest date record .
  
for(Account ac :[SELECT Id,Next_Program_Date__c,(SELECT Id, Account__c, Program_Date__c,Status__c FROM Program__r WHERE Status__c='Open' ORDER BY Program_Date__c DESC LIMIT 1)FROM Account  WHERE Id IN : accountIdSet]){
       ac.Next_Program_Date__c=ac.Program__r[0].Program_Date_vod__c;  
         accListToUpdate.add(ac);
       }
So in scope you will get 200 rcord each time ,I have collected 200 acount id in the set  and query only 200 account each time and related program with status open and latest program date .

So from inner query we can collect all record like 
ac.Program__r;
It will return a list as we have given Limit 1 it will return one record in the list ,So we have added zero index of list it will return first record .
to get the first records date we have added code like 

ac.Program__r[0].Program_Date_vod__c;
And assigning the date to account date .
and added account recor dto th elist to avoid DML limit .

I hope you will clear now . To check add adebug log and check inside for loop .

System.debug('****************'+ac.Program__r[0].Program_Date_vod__c;);
And try to check the debug log .

 
Heather MickeyHeather Mickey
Hi Manoj,

Thank you so much for explaining everything. This has helped me learn so much! :D

I'm getting a "List index out of bounds: 0" Status/Error with the batch failing. I assume it has to do with this line: 
       ac.Next_Program_Date__c=ac.Program__r[0].Program_Date_vod__c; 
but I am unsure of how to correct it? I haven't seen this error before and this is the first time I'm working with looping in Apex. 

Thank you again for all of your help and insight.
Heather MickeyHeather Mickey
Ended up going a different route on writing the script. Below is the working APEX script which works beautifully and updates the field with accurate values. *Not including full Method Finish code as most of it is sending emails for batch run successful and batch run errors with list of errors. 
global class Acc_SetNextProgramDateBatch implements Database.Batchable<sObject>, Database.Stateful{
	global String query = 'SELECT Id,Acc_Next_Program_Date__c,(SELECT Id,AccountId__c,Program_Date__c FROM Program__r WHERE Status__c='Open' AND Program_Date__c >= TODAY ORDER BY Program_Date__c ASC LIMIT 1) FROM Account';

	//This list contains the IDs of Accounts, which produced failures
    global List<Id> accsIDsFailed= new List<Id>();   
     
    //This method implements the interface method start.
    global Database.QueryLocator start(Database.BatchableContext BC){       
        return Database.getQueryLocator(query);
    }
  
    //This method implements the interface method execute.
    global void execute(Database.BatchableContext BC, List<Account> scope){
        //Step through all account records
        for(Account acc : scope){
        	Date NextProgramDate; 
            //Assign the Next Program Date to the Account
            if(acc.Program__r != null) {
                for(Program__c ca : acc.Program__r){
                    if(ca.Program_Date__c != null) {
                    	NextProgramDate = ca.Program_Date__c;
                    }
                }
                acc.Acc_Next_Program_Date__c = NextProgramDate;
            }        
       }
        
       Integer i = 0;
       List<Database.SaveResult> updateResults = Database.update(scope, false);
       for (Database.SaveResult res : updateResults) {
           if (! res.isSuccess()) {
               // send Email for not updated accounts
               accsIDsWithFailure.add(scope[i].Id);
           }
           i++;
       }        
       
    }  
    
    //This method implements the interface method finish.
    global void finish(Database.BatchableContext BC) { }
}

 
This was selected as the best answer