You need to sign in to do that
Don't have an account?
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; } } } |
try
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
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?
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:
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.
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
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.