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
KarenCKarenC 

HELP - with aggregate function in before insert trigger!!

Hi -

 

I am using an aggregate function for the first time and need help!

I need to find the max(version__C) from the quotes table then increment it by 1.

 

I understand that the results of an aggregate returns an array of AggregateResult objects. AggregateResultis a read-only sObject and is only used for query results.

 

How can I increment it.  I tried converting it to an integer and then adding 1 - but I am getting a run time error of:

 

System.NullPointerException: Attempt to de-reference a null object: Trigger.quoteversioning: line 16, column 34

 

It points to the "i" variable in the statement : insertedquote.version__C = i + 1;

 

Thanks for your help!!!

 

trigger quoteversioning on Quote (before insert) {

integer i;

for (quote insertedquote : system.Trigger.new)
    {
    if (insertedquote.revision_chkbox__c == true)
      {
      AggregateResult[] groupedresults = [select max(version__c) maxversion from quote
                        where quotenumber = :insertedquote.quotenumber
                        and opportunityid = :insertedquote.opportunityid];
      object maxrev = groupedresults[0].get('maxversion');
      
      i=integer.valueof(maxrev);
         
      insertedquote.version__C = i + 1;
      }
    }
}
Starz26Starz26

try

 

trigger quoteversioning on Quote (before insert) {

integer i;

for (quote insertedquote : Trigger.new)
    {
    if (insertedquote.revision_chkbox__c == true)
      {
      AggregateResult[] groupedresults = [select max(version__c) maxversion from quote
                        where opportunity = :insertedquote.opportunity;
      i = {Integer)groupedresults.get('maxVersion') + 1;
      
         
      insertedquote.version__c = i;
      }
    }
}

 You cannot use QuoteNumber as a parameter as that is the autonumber field for the current quote. Question: Whay use a version when QuoteNumber is effectivly the version of the quote? If you are basing the version on another field other than opportunity ID like a second version of the quote for a Product A and the opportunity has product A and B, then you will have to add the product or whatever to the query to furthur filter the resilts.

 

The code I just put up will get the MaxVersion for all existing quotes related to that opportunity and increment it by one. Then it will set the version__c field to that value. It may require a bit of debugging
KarenCKarenC

Thanks for your help!!

 

I tried your code, howver, I am still getting the same error:

 

System.NullPointerException: Attempt to de-reference a null object: Trigger.quoteversioning: line 12, column 11

 

line 12 is the following : i = {Integer)groupedresults.get('maxVersion') + 1;

 

Any thoughts?

 

 
SteveBowerSteveBower

You may be making this harder than it needs to be.  You don't really need Aggregate functions because you're not doing any grouping.   You could suffice with:

 

trigger quoteversioning on Quote (before insert) {
    Quote q;
    for (quote insertedquote : Trigger.new) {
        if (insertedquote.revision_chkbox__c == true) {
            q = [select version__c from quote
                where quotenumber = :insertedquote.quotenumber and
                      opportunityid = :insertedquote.opportunityid
                order by version__c desc
                limit 1];
            insertedquote.version__c = q.version__c + 1;
        }
    }
}

 

Of course, the big problem here is that your trigger isn't remotely Bulk safe... you're doing a Select inside of the loop for Trigger.new.   Also, if there are multiple quotes in the trigger with the same quoteNumber and opportunityId then the version will be wrong. There are ways around these issues, but that's a more complex problem so ask if you need that help.  

 

Best, Steve.

 

 

 
KarenCKarenC

Hi Steve - 

 

thanks for your input!

 

Yes, I don't think I can implement your suggestion as I do in fact need to find the highest value of a custom field (revision__c) within a quote number, within the opportunity.  I then need to increment that custom field by 1 and save!

 

It doesn't see like this should be so hard...

 

Please advise!!

 

I appreciate your help.

KC


Starz26Starz26

The above solution would give you the highest value for version__c. the Order BY version__c DESC LIMIT 1 does that for you

 

also, you are getting null pointer because the aggregate function did not return any records. You will need to check for that as if this is the first version that will happen.