Apex Code Development You need to sign in to do that
Don't have an account?
trigger a SOQL query to update a field with a SUM
I am trying to trigger a field update here's the code I have so far. Won_Nov_Outings__C is a currency data field. I can save the trigger, but when i update an opportunity i get this error:
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger testsum2 caused an unexpected exception, contact your administrator: testsum2: execution of AfterUpdate caused by: System.SObjectException: Invalid field sum for AggregateResult: Trigger.testsum2: line 7, column 1
Here is my code:
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger testsum2 caused an unexpected exception, contact your administrator: testsum2: execution of AfterUpdate caused by: System.SObjectException: Invalid field sum for AggregateResult: Trigger.testsum2: line 7, column 1
Here is my code:
trigger testsum on Opportunity (after update) {
AggregateResult[] groupedResults = [
Select SUM (Amount) from Opportunity WHERE Opportunity_Type__c = 'Outing' and Event_Year__C = '2017' AND Event_Month__c = '11_November' AND Probability < 90];
for (Opportunity obj : Trigger.new)
{
double sum =
double.valueOf(groupedResults[0].get('sum'));
obj.Won_Nov_Outings__c =
decimal.valueOf(sum);
}
}
Can you tell what is your requirement in depth?
Above trigger is written for the After Update so you need to put the records inside the list and then put the DML on them.
The reason for the issue is, you have not specified the alias for expression SUM(Amount) . If you dont specify an alias for your aggregate expressions then it will be accessible by using system defined alias like below.
groupedResults[0].get('expr0');I have tried with the it as before update and still get the same error, this copy was just my latest test. I"m trying to run this soql querry where it will update a filed with a sum of the amount with some WHERE filters.
trigger testsum on Opportunity (after update) { AggregateResult[] groupedResults = [ Select SUM (Amount) totalAmount from Opportunity WHERE Opportunity_Type__c = 'Outing' and Event_Year__C = '2017' AND Event_Month__c = '11_November' AND Probability < 90]; for (Opportunity obj : Trigger.new) { double sum = double.valueOf(groupedResults[0].get('totalAmount')); obj.Won_Nov_Outings__c = decimal.valueOf(sum); } }It looks like whatever you are trying to do (Which is still unclear to me) is on the opportunity object and answer is also coming from an opportunity object. This looks better fit for something which can ne done via standard functionalities.
I tried this, and still getting the error:
trigger testsum on Opportunity (before update) { AggregateResult[] groupedResults = [ Select SUM (Amount) sum from Opportunity WHERE Opportunity_Type__c = 'Outing' and Event_Year__C = '2017' AND Event_Month__c = '11_November' AND Probability < 90]; for (Opportunity obj : Trigger.new) { double sum = double.valueOf(groupedResults[0].get('expr0')); obj.Won_Nov_Outings__c = decimal.valueOf(sum); } }I mean do you have something like Parent Opportunity and Child Opportunities etc.. going on?
I wanted to use the update on the opportunity to trigger the query. If I can put it on a custom object that would be good, but i'm not sure how to do that.
e.g., If there are multiple opportunities respect to one account then you should have Opportunity as seperate object
If there are multiple Events for one opportunity then you should have seperate object for an event.
Try to see what are you tracking respect to each opportunity and then I can help you decide do you need seperate object or you can combine information as just a seperate section on opportunity page layout.
Hope this helps!
Opportunity is a parent object. Golf_Courses__c is your child object.
Means one opportunity can have multiple records of Golf_Courses__c object correct?!
Now, there is a field on Golf_Course__c object named Money__c (or something) that you want to sum it up into the Amount field on opportunity. This is what I am picturing so far..!
Sorry for the confusion. I actually gave you 2 options either fix the SOQL query or fix the param name in .get(). You implemented both and it fails.
This code should work.
trigger testsum on Opportunity (before update) { AggregateResult[] groupedResults = [ Select SUM (Amount) sum from Opportunity WHERE Opportunity_Type__c = 'Outing' and Event_Year__C = '2017' AND Event_Month__c = '11_November' AND Probability < 90]; for (Opportunity obj : Trigger.new) { double sum = double.valueOf(groupedResults[0].get('sum')); obj.Won_Nov_Outings__c = decimal.valueOf(sum); } }Close we have an account like Ford, they could have several opportunities, which we define as different record types, the two main being Outings and Banquets. We define all the courses and one if available through a picklist on each opportunity. On the course custom object i would love to have the data summed for that course based on the month of the event and stage it is in (won, pipeline or lost) I don't want to have to update the course object to activate the trigger however. This data then feeds a Conga report that is distributed weekly.
I tried it both ways and still got the error. Keep in mind the apex trigger saves just fine, it's when i go to an opportunity, edit, then save that i get the error at the top of the opportunity.
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger testsum2 caused an unexpected exception, contact your administrator: testsum2: execution of AfterUpdate caused by: System.SObjectException: Invalid field sum for AggregateResult: Trigger.testsum2: line 7, column 1
I am sure the error is just because of the wrong key used on AggregateResult but I dont know why it is not working in your org.
Can you try executing the below code in dev console and see on which line it fails? It should fail on second debug line.
AggregateResult[] groupedResults = [Select SUM (Amount) sum from Opportunity WHERE Opportunity_Type__c = 'Outing' and Event_Year__C = '2017' AND Event_Month__c = '11_November' AND Probability < 90]; system.debug(double.valueOf(groupedResults[0].get('sum'))); system.debug(double.valueOf(groupedResults[0].get('expr0')));Line: 4, Column: 1
System.SObjectException: Invalid field expr0 for AggregateResult