4

As a child (master-detail) to my opportunity object, I have a custom object 'Transactions'. Each transaction has its own interest rate, transaction date, and amount. The opportunity has a term (in months and years - ensured by workflow rule), and interest period (in months).

On the opportunity, I would like to do three things:

  1. I would like to sum the total transaction amounts on the opportunity (done)
  2. I would like to calculate the weighted average interest rate of the opportunity
  3. I would like to calculate the total accrued interest on the opportunity

1 is already solved because the amount field can be summed via a rollup on the opportunity

2 is proving problematic because there is no 'weighted average' formula on rollups. What I could do is calculate the (transaction.rate)*(transaction.amount) on each transaction, then create two rollups on the opportunity; one as above to sum amounts, and a second to sum the weighted rates from the transactions. I then create a THIRD formula field on the opportunity to divide the two fields. This feels clunky...is there an easier way?

3 is extremely problematic. According to the docs, I cannot do a rollup summary (even a sum) on a formula that changes on the fly - i.e. derived using a NOW() or TODAY() function. My "Accrued Interest to Date" field is derived based on the difference between TODAY() and another date, thus not allowing me to sum it all up. I've seen trigger/apex solutions to this, but I was wondering if there was a point and click method for this? Perhaps a field that updates on a daily basis based on a workflow...therefore not triggering the formula rollup exclusion?

Thank you!

Improve this question
1

This reads as though a trigger may be required, to perform these calculations either immediately post a transaction being added/removed/edited, or perhaps as a batched process to run on a regular interval depending on the requirement.

Improve this answer
  • Which do you think would be simpler: Creating a trigger that updates the transaction value such that I can simply roll it up onto the opportunity? OR Creating a trigger that scrapes the value off of the child objects, sums it, then dumps it onto the opportunity? – krewllobster Oct 20 '14 at 13:44
  • Re #2 - based on the information provided, I'd be going the route of adding a trigger that fires after insert/update/delete of transaction records, that would recalculate your weighted average across all affected parent opportunities. This way you have real time weighted average information on all opportunities. Re #3 - have you considered using a workflow rule to copy your formula field value over to a numerical field every time the transaction record is edited, and then rolling that up onto your opportunity? This is a common way of rolling formula field values up onto a parent record. – CloudHugger Oct 20 '14 at 21:18
  • Isn't the issue with a workflow rule that it will only fire on create/edit? That's the only reason I didn't do it this way...I couldn't think how to get the workflow rule to fire every day. I also don't know if that would have a negative impact on any internal SF limits? – krewllobster Oct 21 '14 at 19:35
  • Create a workflow rule on the transaction object to fire on create/edit, then a workflow field update action to copy the formula over to number field whenever values on the transaction record are changed or created. The rollup field on the opportunity would be aggregating or averaging the transaction number field which your updated transaction record just updated via workflow. Opportunity rollup then gets updated to reflect the change to the child transaction records. – CloudHugger Oct 22 '14 at 0:51
  • Yep, get that part. However, the accrued interest field on the transactions should increment every day. Right now it is along the lines of (txn.rate)*(txn.Amount)*(today()-txn.date). Because of the inclusion of today(), it will increment on a daily basis. Can I trigger the field update every day? – krewllobster Oct 22 '14 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.