You need to sign in to do that
Don't have an account?
Shawn 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
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; } }
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
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
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
First time I am seeing this message, any idea how to refactor the code to get past this?
Shawn
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 -
Test Class -
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