function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Heather  MickeyHeather Mickey 

Update Account object with Custom object value: Error on global string query

I'm new to APEX so any help troubleshooting errors to letting me know where I went wrong with my code is greatly appreciated. Currently I am getting an error on the global string query line (2). Error: expecting a semi-colon, found 'Open'

Account is a standard object and Program is a custom object with a lookup relationship of Program to Account, so I need to write APEX to set a field on Account with a value from Program. There are many Programs related to an Account.
I'd like to have a field on the Account to reflect "Next Project Date" when the Project has a Status = "Open" and Date is >= to Today
Example:
**Pretend TODAY is 2015/05/26
Account a
Program 1 for Account a with Date=2015/05/24 and Status=closed
Program 2 for Account a with Date=2015/05/25 and Status=open
Program 3 for Account a with Date=2015/05/26 and Status=open
Program 4 for Account a with Date=2015/05/27 and Status=open
--The Next Program Date on a should be set to 2015/05/26

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

Below is the current code that I have compiled:
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) { }
}
I have also tried another way of writing this, but was unsuccessful because I was getting a different error and didn't know how to handle the error since it was my first time looping in a list. The script would run, but I would get a Failed error of: System.ListException: List index out of bounds: 0

This was the code I had previous tried:
global class Acc_SetNextProgramDateBatch implements Database.Batchable<sObject>, Database.Stateful{
	global String query = 'SELECT Id,Acc_Next_Program_Date__c FROM Account';

    //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){
        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,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 
                        WHERE Id IN: accountIdSet]){
			ac.Acc_Next_Program_Date__c=ac.Program__r[0].Program_Date_vod__c;  
         	accListToUpdate.add(ac);
       }
        
       try{
         update accListToUpdate;
       }catch(DmlException de){
         System.debug(de);
       }
        
    }  
    
    //This method implements the interface method finish.
    global void finish(Database.BatchableContext BC) { }
}

Any advice and guidance is greatly appreciated. I've been working on this for over a week without any avail.

Thank you,

Heather

 

Best Answer chosen by Heather Mickey
KaranrajKaranraj
Heather - You have to escape the single quote in the global string query variable with \. Try replace the error line with below
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';
Now the entire string will be consider as one string variable value.

Thanks,
Karanraj


 

All Answers

KaranrajKaranraj
Heather - You have to escape the single quote in the global string query variable with \. Try replace the error line with below
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';
Now the entire string will be consider as one string variable value.

Thanks,
Karanraj


 
This was selected as the best answer
Heather  MickeyHeather Mickey
Hi Karanraj,

That was exactly it!! I couldn't figure out how to get around that error and your suggestion solved it! Thank you so very much!! Script runs perfectly and provides accurate values in the field.