[SalesForce] Currency fields show other values on a page that in the database

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?!

Best Answer

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.

Related Topic