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
Shawn Reichner 29Shawn Reichner 29 

Aggregate Results issue - Please Help!

Hello awesome devs! 

I have the following class which I woudl like to batch on in a nightly running batch.  This class is to Aggregate some subscription records and group by a String field which will contain the 18 digit ID number from an Opportunity record that the subscriptions make up.  I am then summing the total amoutn from those subscriptions in the aggregate results method so I am left with an amouny for each grouping of subscriptions grouped by the ID number.  

I then want to use that SUM amount to update a field on the Opportunity that is related to the group of subscriptions.  What is happening with my code below is when I run the class, all subscriptions are being summed for every opportunity.  Meaning any opportunity I go to now has the same SUM value which is the value of all Subscriptions in our database with an Opp ID populated and it seems like it is not grouping. 

What did I do wrong, or what should I try as I am stumped.....

Thank you in advance for any help you can provide,

Shawn
  
global class AMPCurrentAmountBatching Implements Schedulable {

    global void execute(SchedulableContext sc){
        AMPCurrentAmountBatching();
    }
    
    public void AMPCurrentAmountBatching(){
        
       List<Opportunity> opps = new List<Opportunity>();
        
       Id idOpp;
       Decimal amt;
       Set<ID> setIdOpp = new Set<ID>();
       Map<Id,Opportunity> mapOpp = new Map<Id,Opportunity>();
       List<AggregateResult> AR = new List<AggregateResult>();
       
        
        For(AggregateResult objar : [SELECT OpportunityId__c, SUM(Total_Booking_Amount__c)
                                    FROM Zuora__Subscription__c WHERE OpportunityId__c !=null
                                    GROUP BY ROLLUP(OpportunityId__c)])
        {
            AR.add(objar);
            setIdOpp.add((ID)objar.get('OpportunityId__c'));  
        }
        
        If(!setIdOpp.isEmpty()){
           // mapOpp = new Map<Id,Opportunity>([SELECT ID, Current_Value__c FROM Opportunity WHERE Id IN: setIdOpp]);
           opps = [SELECT ID, Current_Value__c FROM Opportunity WHERE Id IN: setIdOpp];
        }
        
        For(AggregateResult objar : AR){
            idOpp = (Id)objar.get('OpportunityId__c');
            amt = (Double)objar.get('expr0');
          
        }
        If(opps.size()>0){
        For(Opportunity o : opps){
            o.Current_Value__c = amt;
        }
        }
        
        update opps;
        
    }
    
    
}

 
Maharajan CMaharajan C
Hi Shawn,

Try the Below Code:

global class AMPCurrentAmountBatching Implements Schedulable {

    global void execute(SchedulableContext sc){
        AMPCurrentAmountBatching();
    }
    
    public void AMPCurrentAmountBatching(){
        
       List<Opportunity> opps = new List<Opportunity>();
        
       Id idOpp;
       Decimal amt;
       Set<ID> setIdOpp = new Set<ID>();
       Map<Id,Opportunity> mapOpp = new Map<Id,Opportunity>();
       List<AggregateResult> AR = new List<AggregateResult>();
       
        
        For(AggregateResult objar : [SELECT OpportunityId__c, SUM(Total_Booking_Amount__c)
                                    FROM Zuora__Subscription__c WHERE OpportunityId__c !=null
                                    GROUP BY OpportunityId__c])
        {
            AR.add(objar);
            setIdOpp.add((ID)objar.get('OpportunityId__c'));  
        }
        
      /*  If(!setIdOpp.isEmpty()){
           // mapOpp = new Map<Id,Opportunity>([SELECT ID, Current_Value__c FROM Opportunity WHERE Id IN: setIdOpp]);
           opps = [SELECT ID, Current_Value__c FROM Opportunity WHERE Id IN: setIdOpp];
        }  */
        
        For(AggregateResult objar : AR){
            Opportunity Opp = new Opportunity();
            Opp.Id = (Id)objar.get('OpportunityId__c');
            Opp.Current_Value__c = (Decimal)objar.get('expr0');     // 
            opps.add(Opp);
          
        }
        If(opps.size()>0){
           update opps;
        }
        
        
    }
    
    
}

===================================

Or simply try the below code:

global class AMPCurrentAmountBatching Implements Schedulable {

    global void execute(SchedulableContext sc){
        AMPCurrentAmountBatching();
    }
    
    public void AMPCurrentAmountBatching(){
        
       List<Opportunity> opps = new List<Opportunity>();       
        
        For(AggregateResult objar : [SELECT OpportunityId__c, SUM(Total_Booking_Amount__c)
                                    FROM Zuora__Subscription__c WHERE OpportunityId__c !=null
                                    GROUP BY OpportunityId__c])
        {
            Opportunity Opp = new Opportunity();
            Opp.Id = (Id)objar.get('OpportunityId__c');
            Opp.Current_Value__c = (Decimal)objar.get('expr0');    /// Change if Decimal is not working
            opps.add(Opp); 
        }
        
        If(opps.size()>0){
           update opps;
        }
        
        
    }
    
    
}


Can you please Let me know if it helps or not!!!

If it helps don't forget to mark this as a best answer!!!

Thanks,
Raj
Shawn Reichner 29Shawn Reichner 29
Raj, Thank you so very much for your help here!  I so appreciate it, however both code solutions produce the same error when trying to run. Any ideas on how to tweak the code to resolve this issue?  It seems as if we are settign the ID on line 17, but not sure why it is not taking. Any ideas?

Line: 23, Column: 1
System.DmlException: Update failed. First exception on row 2; first error: MISSING_ARGUMENT, Id not specified in an update call: []

Thank you again,

Shawn
Maharajan CMaharajan C
Yes Shawn, Small mistake:

global class AMPCurrentAmountBatching Implements Schedulable {

    global void execute(SchedulableContext sc){
        AMPCurrentAmountBatching();
    }
    
    public void AMPCurrentAmountBatching(){
        
       List<Opportunity> opps = new List<Opportunity>();       
        
        For(AggregateResult objar : [SELECT OpportunityId__c Oid, SUM(Total_Booking_Amount__c) Amt
                                    FROM Zuora__Subscription__c WHERE OpportunityId__c !=null
                                    GROUP BY OpportunityId__c])
        {
            Opportunity Opp = new Opportunity();
            Opp.Id = (Id)objar.get('Oid');
            Opp.Current_Value__c = (Decimal)objar.get('Amt');    /// Change if Decimal is not working
            opps.add(Opp); 
        }
        
        If(opps.size()>0){
           update opps;
        }
        
        
    }
    
    
}

Can you please Let me know if it helps or not!!!

If it helps don't forget to mark this as a best answer!!!

Thanks,
Raj
Shawn Reichner 29Shawn Reichner 29
Raj, we are getting closer my friend!  Now it works in Sandbox, but when attempting to push to production with a test class, the test fails with the followign message....System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch

First time I am seeing this message, any idea how to refactor the code to get past this?

Shawn
Maharajan CMaharajan C
https://help.salesforce.com/articleView?id=000192834&language=en_US&type=1
Shawn Reichner 29Shawn Reichner 29
Raj, thank you for the link and I researched this as I awaited a reply, and added an additional filter that resolved that issue.  Now the code works well in a sandbox when when I go to deploy into Production I am getting this error now when deploying.....Any ideas on how to refactor the code for production, as I am confused why this error does not populate in sandbox????

Here is the updated Class and Test Class that is popping up the error below, please help! 

System.DmlException: Update failed. First exception on row 32 with id 0060g00000qnM8cAAE; first error: INVALID_CROSS_REFERENCE_KEY, invalid cross reference id: [] 
Stack Trace: Class.AMPCurrentAmountBatching.AMPCurrentAmountBatching: line 22, column 1 Class.AMPCurrentAmountBatching.execute: line 4, column 1

Class Code Updated -  
global class AMPCurrentAmountBatching Implements Schedulable {

    global void execute(SchedulableContext sc){
        AMPCurrentAmountBatching();
    }
    
    public void AMPCurrentAmountBatching(){
        
       List<Opportunity> opps = new List<Opportunity>();       
        
        For(AggregateResult objar : [SELECT OpportunityId__c Oid, SUM(Total_Booking_Amount__c) Amt
                                    FROM Zuora__Subscription__c WHERE OpportunityId__c !=null AND LastModifiedDate = LAST_N_DAYS:60
                                    GROUP BY OpportunityId__c])
        {
            Opportunity Opp = new Opportunity();
            Opp.Id = (Id)objar.get('Oid');
            Opp.Current_Value__c = (Decimal)objar.get('Amt');
            opps.add(Opp); 
        }
        
        If(opps.size()>0){
           update opps;
        }
        
        
    }
    
    
}

Test Class -

​ 
@isTest(SeeAllData=True)
public class AMPCurrentAmountBatchingTest {

    private static testMethod void tm1() {
        
        Date TODAY = Date.today();
        
        Account a = new Account();
        a.Name = 'Test Account';
        a.Status__c = 'Active';
        insert a;
        
    	Zuora__CustomerAccount__c ba = new Zuora__CustomerAccount__c();
        ba.Name = 'Test Account';
        ba.Zuora__Account__c = a.Id;
        ba.Zuora__Status__c = 'Active';
        ba.Zuora__MRR__c = 100;
        ba.Starting_CMRR__c = null;
        insert ba;
        
        Opportunity o = new Opportunity();
        o.Name = 'Test Opp';
        o.Amount = 100;
        o.AccountId = a.Id;
        o.CloseDate = TODAY;
        o.StageName = 'Initiated';
        insert o;
        
        Zuora__Subscription__c sub = new Zuora__Subscription__c();
        sub.Zuora__Account__c = a.Id;
        sub.Zuora__CustomerAccount__c = ba.Id;
        sub.Zuora__MRR__c = 100;
        sub.OpportunityId__c = String.valueOf(o.Id);
        insert sub;
        
        
         Test.startTest();
      	String CRON_EXP = '0 0 0 15 3 ? *';
        

    	String jobId = System.schedule('ScheduleApexClassTest',  CRON_EXP, new AMPCurrentAmountBatching());
    	CronTrigger ct = [SELECT Id, CronExpression, TimesTriggered, NextFireTime FROM CronTrigger WHERE id = :jobId];
    	System.assertEquals(CRON_EXP, ct.CronExpression);
    	System.assertEquals(0, ct.TimesTriggered);
        Test.stopTest();
        
        
    }
    
    
}

 
Maharajan CMaharajan C
Sorry Shawn, Am not receieving any email from Developer froum its all went to Gmail SPAM.

sub.OpportunityId__c = String.valueOf(o.Id);  // Check this field in Production if its OK. Check production did you have missed any mandatory field for insert the opportunity in test class.

Thanks,
Raj
Shawn Reichner 29Shawn Reichner 29
Raj, thanks again for the help sir!  I did check the field OpportunityId__c in production and all profiles have read and write access to this field.  I also added a debug log to the class and see that the opportuntiy is being created, so I am still stuck on why the error populates only in production when trying to deploy with the class above???  Any other ideas my friend?