1

I received some help with this on the Salesforce forums but I can't get over the hump and get it working. The code below saves fine and I'm able to schedule it and it runs through all of the batches. The issue is that nothing is being added to the Active_Contracts_Per_Year_Amount__c field in the records. I know the query works and should return a number (not a decimal or currency). So it seems to me the Aggregate result section is the problem. Anyone have an idea how to get this to work?

global class CalculateCPYA implements Database.Batchable<sObject> {

    global Database.QueryLocator start(Database.BatchableContext BC) {
        String query = 'Select Id, Name, Active_Contracts_Per_Year_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope) {
        for (Account a: scope) {

            AggregateResult[] agList = [SELECT SUM(amount_per_year__c) totalSum
                                        FROM Contract 
                                        WHERE EndDate > TODAY
                                            AND Id = : a.id];

            Integer totalSum = Integer.valueOf(agList[0].get('totalSum'));
            a.Active_Contracts_Per_Year_Amount__c = totalSum;
        }

        update scope;
    }

    global void finish(Database.BatchableContext BC) {

    }
}
4
  • Are you sure the aggregate query is actually returning any results? You're using Account Ids on the Contract object... Also, you'd be wise to bulkify this code.
    – Nick C
    Jul 3, 2014 at 0:59
  • Doh, I think you might be right. Although I've been through so many iterations of this code that might just be an error in one. That section should read a.accountID. I'll test it right now. Jul 3, 2014 at 1:09
  • I did a quick test with anonymous apex. If there aren't any Contract records for the Account the AggregateResult[] will have a single AggregateList with totalSum=null. Jul 3, 2014 at 1:22
  • Does the batch complete successfully without errors or exceptions? If you browser to /apexpages/setup/listAsyncApexJobs.apexp there should be no failures. Jul 3, 2014 at 1:42

1 Answer 1

5

Before you do anything else, please remove the aggregate query from within the loop.

You're doing a query that will by definition return zero records because you're specifying that the Id of the Contract is the AccountId, which is impossible. Instead, change your WHERE clause to AccountId = :a.Id. But you're not done there...

  1. Iterate through all the accounts and make a Map <Id, Account>.

  2. Do the aggregate query and specify AccountId IN :myAccountMap.keyset(), and GROUP BY AccountId.

  3. Iterate over your aggregate results and put the totalSum into the Account custom field.

  4. Update myAccountMap.values()

I hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .