You need to sign in to do that
Don't have an account?
Heather 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:
This was the code I had previous tried:
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
Thanks,
Karanraj
All Answers
Thanks,
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.