8

I am confused by the behaviour of Currency fields in Multi-Currency environments. My app does a calculation on day X and stores its result in a record.

The custom page which displays a SUM of those values should always display the same as a database query. But...it does not. If I change Exchanges Rates those values change.

Was it wrong to store currency values in a Currency field, am I querying incorrectly or do I need to display a values that is manually calculated?

How does Multi-Currency and Currency fields work together? When are currency values converted and which conversion rate is taken into account?

I am confused?!

2
  • 1
    Are you using dated exchange rates / advanced multi currency ? Commented Jan 22, 2014 at 14:21
  • @Sdry: No we don't use that. Commented Jan 22, 2014 at 14:43

2 Answers 2

3

It's not really an answer to original Q but it's too big to fit into comment. And you've made a follow-up comment asking about it so here goes...

(note: I'm lucky and I didn't had - to date - to work in an org with dated exchange rates. Added layer of complexity lurks there)

If you're using a rollup summary of currency fields - magic is done for you and all is fine.

If you're calculating the summary manually in a trigger or batch job - roughly speaking you have 3 ways to choose from:

Naive:

SELECT AccountId, CurrencyIsoCode, Amount
FROM Opportunity

This is the worst way. You either ignore the currency and add apples to oranges or you have to manually examine CurrencyIsoCode and convert in each row. Your rounding errors will accumulate, good luck with that.

Convert to user's currency

SELECT Id, AccountId, convertCurrency(Amount)
FROM Opportunity

Then you loop and make a fairly straightforward summary. Maybe you don't add apples to oranges, maybe you're adding bananas - but they're all bananas so who cares. You'll perform only 1 currency conversion on the total result and all will be fine in the world.

You might consume lots of script statements to roll them up but convertCurrency is great to "just get stuff done" when all you want is to display something in Visualforce in the way the user prefers.

Convert to organisation's currency

SELECT AccountId, SUM(Amount)
FROM Opportunity
GROUP BY AccountId

Similar to previous. Bit more fuss to access AggregateResult but less code statements. Same amount of SOQL rows is wasted (the 50K limit) but GROUP BY has extra limit of up to 2000 rows in resulting set - so if you're recalculating it in batch you might have to tune down the batch size. Didn't test it but at least in theory should perform better as you throw the heavy lifting task at DB (less memory used, less network traffic etc).


As for the conversion rates (applicable to all methods although in scenario 2 and 3 you'll multiply only once) - I use something like that:

Map<String, Decimal> conversionRates = new Map<String, Decimal>();
for(CurrencyType ct : [SELECT IsoCode, ConversionRate FROM CurrencyType]){
  conversionRates.put(ct.IsoCode, ct.ConversionRate);
}

Then I just access the map using parent's (so for me - Account's) CurrencyIsoCode. Most of my code is using the #3 method so it's a straightforward multiplication. In worst case scenario for #2 you'd have to go user's currency -> org's currency and then org's currency -> account's currency.

3

From your question, I conclude you don't use the advanced multi currency setup. With the normal currency setup, what you store is the numerical value of the currency field and a separate currency code. If you sum a currency field, a conversion to some common currency will be done behind the scenes. If all your objects being summed up have the same currency, then a sum should not be affected by a currency rate change, but in the common case of having many currencies, the sum will be changed over time when the conversion rates are changed since what is stored is in the "local" (for that object) currency and a sum will inevitably require a conversion to some common currency.

2
  • Great Answer! Which is the best spot to tackle that? Should or can I do this conversion in the SOQL already? Or can I do it in the view layer by using a Visualforce construct?. Commented Jan 22, 2014 at 14:48
  • n.b Note that advanced currency mgt works great on summing currency fields on Opportunity (using closeDate as the key to the dated exchange rate lookup) but not on custom objects (no way to tell SFDC which <date> field on the custom object is the lookup key to the dated exchange rates). Thus, when using custom objects+currency fields and advanced currency mgt, the most recent exchange rates are always used for the conversion to the corporate currency.
    – cropredy
    Commented Jan 22, 2014 at 19:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .