+ Start a Discussion
Josh SchwerdtfegerJosh Schwerdtfeger 

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:
 
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);
            }
    }

 
HARSHIL U PARIKHHARSHIL U PARIKH
Hello Josh,

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.
 
Asif Ali MAsif Ali M
Use the below query to fix the issue.
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

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');

 
Josh SchwerdtfegerJosh Schwerdtfeger
Hi Harshil,
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.  
 
SalesFORCE_enFORCErSalesFORCE_enFORCEr
I think what you should do is something ​like this
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);
            }
    }
HARSHIL U PARIKHHARSHIL U PARIKH
Look you are updating opportunity record by putting query on an opportunity object.

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.
Josh SchwerdtfegerJosh Schwerdtfeger
Asif,
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);
            }
    }

 
HARSHIL U PARIKHHARSHIL U PARIKH
You have a field named Won_Nov_Outings__c on opportunity object and you are trying to sum Amount fields from some opportunities correct?

I mean do you have something like Parent Opportunity and Child Opportunities etc.. going on?
Josh SchwerdtfegerJosh Schwerdtfeger
Harshi,
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.
 
Josh SchwerdtfegerJosh Schwerdtfeger
I have a related custom object which is our golf courses, what i am trying to get is s sum of Won revenue per month per course, as well as pipeline revenue.  I was thinking that i had to put it on the opportunity because that is where the amount field is, plus when they are updated it would trigger the query and refresh the revenue.  Am I missing a better way to do this?
HARSHIL U PARIKHHARSHIL U PARIKH
In my opinion, if something is occuring multiple times respect to something then you should have a custom object for 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!
HARSHIL U PARIKHHARSHIL U PARIKH
Ok so this is your data model:

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..!
Asif Ali MAsif Ali M
Hi Josh,
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);
            }
    }


 
Josh SchwerdtfegerJosh Schwerdtfeger
H,
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.
 
HARSHIL U PARIKHHARSHIL U PARIKH
Let's say if this trigger does works. But how would you know by looking at the opportunity record which opportunities made that sum happened?? I mean there is no child/Parent object relationship here.
Josh SchwerdtfegerJosh Schwerdtfeger
Asif,
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
Asif Ali MAsif Ali M
This is a run time error so Trigger is saved without any issues.
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')));

 
Josh SchwerdtfegerJosh Schwerdtfeger
Asif,  This is what i get when i drop it in the execute anonomous window:
Line: 4, Column: 1
System.SObjectException: Invalid field expr0 for AggregateResult
Asif Ali MAsif Ali M
that means get('sum') woks fine.  Do you have any other code in the Trigger file? Where are you updating your opportunities?
Josh SchwerdtfegerJosh Schwerdtfeger
This is the only code in the trigger.  Not sure what you mean where i'm updating the opportunity, I'm just picking a random one, then hitting edit, then save, as a user would.  i guess.
Josh SchwerdtfegerJosh Schwerdtfeger
Ok, i was able to get it to work but moving the field i wanted to update onto the Course__C object.  Now, I have another question which i may post another thread about and that is in my WHERE statement i list the CourseID__C, but i'd like to have this passed on form the object it's self instead of hard coding it.  We have 140+ courses, and i'd like this field update to exist on each one but run only the query for the results to be unique to that courseID.