+ Start a Discussion
Big EarsBig Ears 

Opportunities & Opportunity Products - Apex Triggers and Roll up Formula Fields

Hey guys.
 
I have a Before Update trigger on the Opportunity Object that runs an Apex class which:
 
1. Checks if the Opportunity Status has been changed to a particular stage.
If it has, it then:
2. Checks the Opportunity Products (specifically if there are certain families of products in there)
If Opportunity Products from a specific family (A) are there, without a particular Opportunity Product from another family (B):
3. It automatically works out how much the Opportunity Product from family (B) should cost, given the overall sale cost of the Opportunity
4. Reduces the overall cost of the products of family (A) by the cost of the product from family (B)
5. Updates the Products from Family (A)
6. Inserts the product from Family (B)
 
At the end of the process, the overall Opportunity sale price remains the same, with the new product inserted. It was working a treat, until I added some roll-up formula fields that gather information about the products (An overall sales cost, an overall list price and a formula working out the overall Reduction against List Price)
 
When I tried that, I got the following error:
 
Code:
Apex trigger MyOpportunityUpdate caused an unexpected exception, contact your administrator: 
MyOpportunityUpdate: execution of BeforeUpdate caused by: System.DmlException: Update failed. 
First exception on row 0 with id 00oS0000000KsjwIAC; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, 
Object (id = 006S0000002JnAw) is currently being updated in trigger 
MyOpportunityUpdate: [Id]: Class.MyRFFCreator.CreateRFF: line 35, column 29

 
Interestingly, the error I am getting implies that I've defined a validation formula that's upsetting the system. However, there are no validation rules that get their information from any of the fields being affected by the update. Also, the error arises when I try to update the Opportunity Product from family (A), having reduced it by the appropriate amount.
 
The section of the APEX class that's causing the error is as follows:
Code:
for (OpportunityLineItem cli : FamilyA){
 OpportunityLineItem LineItemToChange = [select id, UnitPrice, Quantity, TotalPrice, (select id, ScheduleDate, Revenue from OpportunityLineItemSchedules order by ScheduleDate asc nulls last limit 1)from OpportunityLineItem where id = :cli.id];
 OpportunityLineItemSchedule LineItemScheduleToChange = LineItemToChange.OpportunityLineItemSchedules;
 LineItemScheduleToChange.Revenue = LineItemScheduleToChange.Revenue - NewRFF.UnitPrice;
 LineItemScheduleToChange.Description = 'This has been auto-edited by Apex';
 update LineItemScheduleToChange; THIS LINE CAUSES THE ERROR           
} 

 I'm really confused.
 
Is the problem that changing a formula field counts as an update, and therefore the trigger is being recursively triggered?
Or that by altering the value of the revenue arising from the Opportunity Product, I'm causing a validation problem with the roll-up field? That would surprise me, as I've been able to add/remove and change Opportunity Products and the Roll-up formula fields deal with it with ease.
 
Or am I just making an obvious error and asking Salesforce/Apex to do something which it isn't able to?
 
Thank you for all your help, in advance. I hope I've been clear enough.
jrotensteinjrotenstein
While I haven't delved too deeply into your code, I notice that your trigger is doing some strange stuff:
Code:
OpportunityLineItem LineItemToChange = [select ...];
OpportunityLineItemSchedule LineItemScheduleToChange = LineItemToChange.OpportunityLineItemSchedules;
...
update LineItemScheduleToChange; THIS LINE CAUSES THE ERROR

Your SELECT statement is not returning a field called OpportunityLineItemSchedules, so trying to update it will probably be quite hard. Nor have I ever heard of such a field, so might it be a custom field requiring __c or __r handling?

The error message says that the object is currently being updated in a trigger, which suggests that you are calling UPDATE on an object (an Opportunity?) that is already the subject of your trigger. Indeed the Id displayed starts with '006', which indicates an Opportunity record. This also suggests that you are not updating the object you think you are.

Might I suggest testing the SELECT statement in a SOQL tool to see if you're getting what you expect?
Big EarsBig Ears
John,
 
Thanks for your reply. Unfortunately, the SELECT statement is returning both an Opportunity Line Item and its child object OpportunityLineItemSchedules (a standard Object that allows you to split out the revenue from an item into seperate chunks) correctly, so I don't believe that is where the problem lies. Additionally, the update functionality was working fine until the addition of the roll-up fields in the Opportunity object.
 
However, your second paragraph has got me assuming that the change in value of the Roll up fields (when altering the Opportunity Products) is counting as an update and re-tripping the trigger. Is there any way around it?
 
Thanks for your help.
jrotensteinjrotenstein
It might be a good case for using @future to make the update happen outside of the Trigger.
Big EarsBig Ears

Thanks John,

Unfortunately, introducing "@future" into the class itself means that I get an error of: Unsupported Parameter type LIST:SOBJECT:Opportunity

I'm a complete dunce around @future, and haven't found the documentation to be useful, unfortunately. Also, the Opportunity Roll up fields aren't referenced in the trigger or apex class (because they're automatic) so I can't ask Salesforce to update those with the @future key word, surely?

Is there any way of just telling Salesforce to ignore any update triggers that are activated by a change in the Roll up fields? If I'm honest, I'm surprised that they're tripping the trigger at all.

Any additional help is greatly appreciated. You will be owed good karma and excellent beer.

Andy

jrotensteinjrotenstein
Another possibility is to use after update, since the Opportunity is not "being updated" while that is executing.

Might not work, but worth a try.

I don't think you can definitively say that the error is being caused by roll-up fields.

Oh, and it could be worth adding Description to your select statement since you are setting it but not retrieving it.
Big EarsBig Ears

Dear John,

Edited: Thank you for your suggestions! It's actually begun working, using an after trigger and a version of the "Singleton Pattern" making the system recognise that updates due to the Roll-Up fields can be ignored.

Interestingly, however, when I lower the revenue amount in the OpportunityLineItemSchedules and then run an update call, the reduction is placed in a second schedule line, below the first i.e.:

Schedule DateQuantityRevenue
09/11/200850675

Becomes:

Schedule DateQuantityRevenue
09/11/20080675
09/11/200850-45

Which, overall, gives the correct answer, but will make reporting tricky.
 
Do you know if that's because the trigger is an "after update" rather than before?
 
 
EDITED TO ADD: Fixed. If anybody wants to know the how's/why's, please feel free to get in touch via this message board.
 
Thanks to John for his help.
 
 
Andy


Message Edited by Big Ears on 11-13-2008 09:44 AM

Message Edited by Big Ears on 11-24-2008 03:55 AM
sparktestsparktest

I have a related issue, with regards to creating a list in a trigger, and if that new insert list has more than 100 records, call a @future method to do the update.  I am having trouble figuring out the syntax to send the list to the method from the trigger...I believe.

Here is the post (towards the end)

http://community.salesforce.com/sforce/board/message?board.id=apex&thread.id=6756&page=2

SsuazoSsuazo

Need help with the following code..... I keep getting an error sayin my xzipcode field is an invalid token

 

trigger LeadAssignmentTrigger on Lead (before insert)
{
    List<Lead> leadsToUpdate = new List<Lead>();

    for (Lead lead : Trigger.new)
    {     
      if (lead.PostalCode != NULL)
      {
          // Find the state for the current zip code
          List <ZipCode__c> zip = [select State__c from ZipCode__c
                                   WHERE lead.PostalCode.startsWith xZipCode__c = TRUE];  
          // if you found one
          if (zip.size() > 0)
          {    
              //assign the ZipCode to the correct Stae      
              lead.State = zip[0].State__c;
              leadsToUpdate.add(lead);
          }
       }     
     }
    }

jrotensteinjrotenstein

Within a SOQL Where statement, the first expression must be a field and the second a value, eg:

 

  • a = 2
  • name = 'fred'
In your statement, you've got a mixture of values:
WHERE lead.PostalCode.startsWith xZipCode__c = TRUE
Unfortunately you can't put functions (eg startsWith) on the left.
I'd suggest using:
WHERE xZipCode__c = :lead.PostalCode
If you're trying to test a situation where lead.PostalCode (eg 9021077) can be longer than xZipCode__c (eg 90210) then you're out of luck -- you need the SOQL expression on the left and the variable on the right.
You could do:
WHERE xZipCode__c like :lead.PostalCode + '%'
but that is checking the wrong thing -- where xZipCode__c can be longer than lead.PostalCode.

 

SsuazoSsuazo

Does anyone happen to have any idea on how to write a trigger to prevent duplicate accounts in trigger or have any source code they would like to share? It would be greatly appreciated