web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Rolling up foreign cur...
Microsoft Dynamics CRM (Archived)

Rolling up foreign currency amounts

(0) ShareShare
ReportReport
Posted on by awalters 3,079

I have a parent/child relationship on two custom entities, and I want to do a rollup on the parent from the currency amounts of the children.  Our base currency is CAD, and let's say the children in this case are all USD.  Child record 1 is created, with a USD amount of $1000, and at the time the exchange rate is .71, so the CAD is $1408.45.  Later, the exchange rate changes to .75, and we update it accordingly.  Child record 1 does not change (and we wouldn't want it to, since that's the payment we received at the exchange rate we received).  Now, we create child record 2, also with a USD amount of $1000.  This time, the CAD amount is $1333.33.

If I do a rollup on these, and my parent record is set to CAD, then I get $2741.78 as both the amount and the base amount.  If I set the parent record to USD, it seems to do the rollup at the base amount, so it's still $2741.78, and then translates the US amount from that at the current exchange rate, so the USD amount rollup shows as $2056.34.  But I didn't receive $2056.34 USD, I received $2000 USD.

Is there any way to do this rollup on the amounts actually received in the transaction currency, as well as the amounts actually received in the base currency?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Wayne Walton Profile Picture
    Wayne Walton 13,730 on at

    I don't think you can do what you want with the out of the box rollups.  In fact, I would recommend submitting this as a bug to Microsoft, since it doesn't look like desirable behavior.  If you make a custom rollup, though, you could determine when to use the Base currency and when to use the display currency programmatically.  

  • Suggested answer
    ScottDurow Profile Picture
    ScottDurow 21 on at

    Hi Allison,

    The rollup should always be performed in the base currency and then converted to the currency on the parent record.

    The only time I can think of that you would get 2000 USD in the parent record is that if you had updated the first record so that the base currency value would be recalculated using the new exchange rate so that the base conversion of the child records is consistent with the total conversion back again to USD.

    Everytime you edit the child record amount (any currency field on that record) all the values will be re-calculated using the most recent exchange rate.

    The only way of avoiding this is to store values as decimal values without using currency amounts - but then you loose the ability to report/sum in multiple exchange rates. Perhaps a combination would work?

    Hope this helps

  • awalters Profile Picture
    awalters 3,079 on at

    Wayne - when you reference a custom rollup, do you mean something done through a plugin, or something else?

    Thanks!

  • awalters Profile Picture
    awalters 3,079 on at

    Scott - thanks for confirming my observations then, even if they weren't what I was hoping to see.  :-)  What would a combination look like, do you think?  We'd originally looked at using decimals for other reasons - we handle our exchange rates differently than OOTB, but I ended up writing a plugin to deal with that because we wanted the ability to handle the exchange calcs automatically, as well as properly format each currency type.  I'm not certain how we would use both together, unless we had everything calc into hidden decimal fields and then just put it into a currency field for front end/formatting/etc...?  We'd have a lot of duplication and possibility for confusion that way, though.  But if it's the only way we'll have to figure out how to mitigate that.

    Thanks!

  • awalters Profile Picture
    awalters 3,079 on at

    Trying to figure out what a custom plugin would look like for this.  One thought I'd had was that maybe I could read each of the child records and calculate a weighted average exchange rate, then override the value being passed to the calc from RetrieveExchangeRate (just this last piece is how we're handling exchange rates anyway at the moment).  But I'm not sure where to call this from or how to attach it, since I'm not sure of the sequence of events/messages called/etc... when a rollup is being done.  Still poking away, but that's my current thought.

  • awalters Profile Picture
    awalters 3,079 on at

    Ugh - getting further on this, but not there yet and a bit stuck.  I have a plugin (created before this) attached to the RetrieveExchangeRate message that overrides the exchange rate with one in a custom exchange rate field on the record, if that field exists.  That part works fine for any of the entities.

    Then I have a custom workflow activity that I'm calling when one of the child records is added or changed.  This workflow finds the ID of the parent record, and then calculates a weighted average exchange rate across all of them.

    My thought was then okay, if I just put this calculated exchange rate into the new_exchangerate field (like I'm successfully able to do manually on screen), then when that workflow activity does its final step of triggering the rollup re-calc, it'll get my weighted exchange rate, and all will work as desired.

    Except...it doesn't seem to fit together that way.  Triggering a rollup through CalculateRollupFieldRequest doesn't actually seem to call RetrieveExchangeRate at all, so my custom code there doesn't take over.  :-(  Anyone have any idea where/how the exchange gets calc'd on a rollup, if not via the RetrieveExchangeRate message?  Feels like that's the last piece I'm missing...

  • ScottDurow Profile Picture
    ScottDurow 21 on at

    Hi Allison!

    Having spent many an hour trying to fiddle around with the exchange rate mechanism I can only conclude that it's best left as -is and create a parallel calculation using decimal fields. You would need to take the money amount and copy into decimal fields and then roll that field up - then you can use whatever conversion you need.

    Hope this helps

  • Verified answer
    awalters Profile Picture
    awalters 3,079 on at

    Since this is a non-standard type of rollup, I ended up thinking more about Wayne's suggestion to do a custom rollup.  I wasn't 100% sure what was meant, at first, but I believe he meant to do the rolling up myself in a workflow activity and just put the results into a currency field, rather than letting the system roll it up.  I already need to calc the total amount manually to determine the weighted exchange rate, so I just added a regular currency field instead of a rollup to the parent record, and in my workflow activity triggered by the child I calc the weighted average exchange and the total amount in transaction currency, and put those both directly into the parent record.  This then automatically calculates the base total such that the total of the transaction currency and the total of the base currency match the totals of the child records.

    I need to spend some time putting in error checking and genericizing it (taking parameters instead of hard coding field/entity names, ideally), but it definitely seems to work!

    The best part about it is that all of the formatting, rounding, etc... all work properly for various currencies, which wouldn't be the case if we switched them all to decimal fields.  I'm not sure it's the best solution for everyone, so I'll mark that option as an answer too, but I think this should end up working quite well for what we need.

    Thanks to Wayne and Scott!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans