+ Start a Discussion
CaptainObviousCaptainObvious 

Trigger to roll up values

I've written a trigger to roll up values from a custom object to associated contracts. It works great- as long as the contract has under 200 Sales:

 

 

trigger rollupValues on PN_Sales__c (after insert, after update, after delete) { Map<Id,Contract> ContractsToUpdate = new Map<Id,Contract>(); Set<Id> ContractIDs = new Set<Id>(); if(Trigger.isInsert || Trigger.isUpdate){ for(PN_Sales__c pnSale : trigger.new){ //check that the Contract is specified if(pnSale.Contract__c != null){ if(!ContractIDs.contains(pnSale.Contract__c)){ ContractIDs.add(pnSale.Contract__c); } } } } if(Trigger.isDelete || Trigger.isUpdate){ for(PN_Sales__c pnSale : trigger.old){ //check that the Contract is specified if(pnSale.Contract__c != null){ if(!ContractIDs.contains(pnSale.Contract__c)){ ContractIDs.add(pnSale.Contract__c); } } } } //Fetch the Contract and Associated Sales Data: if(ContractIDs.size() > 0){ for (Contract conTracts : [SELECT Id, Current_Value__c, Forecasted_Value__c, Current_Volume__c, (SELECT Id, Product__c, Quantity__c, Revenue_USD__c, Gross_Revenue_USD__c, Valid__c, PN_Account__r.PN_Type__c, Contract_Volume__c, Contract_Revenue__c, Status__c FROM PN_Sales__r) FROM Contract WHERE Id in :ContractIDs]){ ContractsToUpdate.put(conTracts.id,conTracts); } //For every Contract... for(Contract conTract: ContractsToUpdate.values()){ //Initialize all sums to '0': Double currentVolume = 0; Double currentValue = 0; Double forecastedValue = 0; Double totalvolume = 0; //...Loop through all associated sales: for (PN_Sales__c pnSale: conTract.PN_Sales__r){ if (pnSale.Quantity__c == null) { pnSale.Quantity__c = 0; } String accountType = pnSale.PN_Account__r.PN_Type__c; String saleStatus = pnSale.Status__c; if (pnSale.Revenue_USD__c == null) { pnSale.Revenue_USD__c = 0; } if (pnSale.Gross_Revenue_USD__c == null) { pnSale.Gross_Revenue_USD__c = 0; } //Now perform the calculations (depends on account type): if ( accountType == 'BV' || accountType== 'CMD' ) { //Calculate Current Value if (saleStatus == 'Actual' || saleStatus == 'Estimate') { if (pnSale.Contract_Revenue__c == 'Include' && pnSale.valid__c==true) { currentValue += pnSale.Gross_Revenue_USD__c; } } //Calculate Forecasted Value if (pnSale.Contract_Revenue__c == 'Include' && pnSale.valid__c==true) { forecastedValue += pnSale.Gross_Revenue_USD__c; } } else { //Calculate Current Value if (saleStatus == 'Actual' || saleStatus == 'Estimate') { if (pnSale.Contract_Revenue__c == 'Include' && pnSale.valid__c==true) { currentValue += pnSale.Revenue_USD__c; } } //Calculate Forecasted Value if (pnSale.Contract_Revenue__c == 'Include' && pnSale.valid__c==true) { forecastedValue += pnSale.Revenue_USD__c; } } //Calculate Volume if (saleStatus == 'Actual' || saleStatus == 'Estimate') { if (pnSale.Contract_Volume__c == 'Include' && pnSale.valid__c==true) { currentVolume += pnSale.Quantity__c; } } } conTract.Current_Value__c = currentValue; conTract.Forecasted_Value__c = forecastedValue; conTract.Current_Volume__c = currentVolume; } //Finally update Contracts with rollup values: update ContractsToUpdate.values(); } }

 

I've taken out debug messages from the code above for the sake of clarity, but the debug log clearly shows the sales looping up to the 199th record and then stopping with the message: System.Exception: invalid query locator.

 

I was wondering if this is a limitation or if there is another approach I should take? I've tried using a SOQL for loop as suggested in the Apex Developer guide, but I couldnt get the values to sum across all sales.

TehNrdTehNrd
I am having the exact same issue. Did you ever get this resolved?
CaptainObviousCaptainObvious

Unfortunately, splitting the query into 2 separate queries was the only option at the time.

 

Although Batch Apex will handle a significant amount of records, our users require this data to be updated in real time.

 

Another approach I've been exploring is creating a master-detail record which is fully maintained by the trigger:

 

When a new record (Sale) is added/updated, a detail record (Sale Item) is created. Because this Sale Item has a Master-detail relationship with the Contract, the rollup is done automatically.

 

The challenge here is maintaining the detail record: If a Sale is deleted, the Sale Item will have to be deleted... If the Sale is incorrectly linked to a Contract, the Sale Item will have to be deleted and re-created with the new contract, etc...