+ Start a Discussion
Jennifer MolskeJennifer Molske 

Trigger on TotalAmount of Order

Hi guys,

I've got a difficult problem on which I am working a few hours but still wasn't able to solve.

In our organisation we've got lots of orders with different order items. I cannot find a custom implementation for calculating the TotalAmount of an order, so I assume that it is calculated by Salesforce itself and sums up the TotalPrice fields of the order items of an order. We've also implemented a custom field (let's call it BillingPercent) which holds a percent value that indicates how  much percent of the TotalAmount of the order is already billed.

If I insert, update or remove some of the order items of an order the TotalAmount of this order changes. So now, every time the TotalAmount changes, I want to recalculate the value of my custom field BillingPercent, so that it adapts to the new TotalAmount.

I tried the following:
- I tried to add this functionality to my onBeforeUpdate Trigger for Orders, so that I can use the old and new TotalAmount values of the order to calculate the new BillingPercent value. Unfortunately doesn't get called if I insert, update or delete some of my order items.
- I implemented a new onBeforeInsert/Update/Delete-Trigger on OrderItems in which I was able to calculate the new BillingPercent value. This works fine for just updating existing order items, but if I insert or delete an new order item, the trigger crashes with the following message:

first error: SELF_REFERENCE_FROM_TRIGGER

The reason why the trigger crashes is obvious: I delete an order item, the BillingPercent value of the order is updated and because of this the UpdateOrder trigger gets called. The UpdateOrder trigger itself has the function to add all the default order items we specified in another table. 

Delete (default) order item from order --> UpdateOrderItem.trigger is called --> BillingPercent is calculated --> update order --> UpdateOrder trigger is called --> UpdateOrder trigger updates the order items and so the UpdateOrderItem.trigger is called again and crashes because the order items is already referenced in the trigger.

Anybody got an idea how to solve the problem and achieve the simple recalculation of the BillingPercent value after the TotalAmount value changes? I'm not fixed on using triggers, I already thought about using a workflow rule but I don't know if this could be the right way. Maybe there are some other possibilities how to achieve the desired behaviour.

Thanks for your help :)

Best Regards,
Jenny
Best Answer chosen by Jennifer Molske
RaidanRaidan
The AFTER trigger on the Order Item is to update the parent Order record. You will need this trigger to detect the change on the order items (and we know that this will also change the TotalAmount field in the parent record). If you need the old value you can always create a new field in the Order object, say Last Amount, and keep the value up to date with a trigger or a workflow.

All Answers

RaidanRaidan
Hi Jenny,

You should create a trigger for the Order Product and not the Order object, because you want to recalculate the field when the order item is changed (created/updated/deleted). In this case your trigger should be an AFTER insert, update, and delete. You can query the order total amount and recalculate the billing percent.
Jennifer MolskeJennifer Molske

Hi Raidan,
thanks for your fast answer! :)

Yes, I already thought about a similar solution you mentioned, but unfortunately we really need this update order trigger, to keep every order up to date. 
And additionally I need the old value of the TotalAmount to recalculate the BillingPercent with this formula:
newBillingPercent = (oldTotalAmount * BillingPercent) / newTotalAmount.
So the AFTER insert, update, delete trigger doesn't fit my needs because I am only able to query the updated total amount of an order. :(

I'm dreaming of a possibility to get into the onChangeEvent of the TotalAmount field of an order where I can place my code, but I think this is not possible, isn't it? :D

RaidanRaidan
The AFTER trigger on the Order Item is to update the parent Order record. You will need this trigger to detect the change on the order items (and we know that this will also change the TotalAmount field in the parent record). If you need the old value you can always create a new field in the Order object, say Last Amount, and keep the value up to date with a trigger or a workflow.
This was selected as the best answer
Jennifer MolskeJennifer Molske
Hi Raidan,

okay. I've also thought about creating a new field LastAmount in the Order object as a compromise. But I was still hoping that there might be a smarter solution for my problem. Now I will give your suggestion a try, thanks for your help :)
RaidanRaidan
You're welcome! Don't forget to mark the answer to help others who have the same question. Thanks!