The Cliffs Notes on How Salesforce Supports Multiple Currencies

When you enable multi-currency for your Salesforce organization, the following changes are made:

• A new object, CurrencyType is available - a record of this object type is created for each currency you configure via the setup interface. One CurrencyType record is tagged as the default or “corporate” currency. Each CurrencyType record has a ConversionRate attribute that defines the current conversion rate from that currency into the corporate currency. The ConversionRate for the corporate currency is set to 1.000.
• Every SObject in the organzation gets a new attribute, CurrencyIsoCode, which indicates in what currency the money attributes of the record are denominated. This is a String attribute that holds the ISO code for the currency (e.g. USD for US Dollars, EUR for Euros, etc).

The following behaviors are also introduced:

• Changing an SObject’s CurrencyIsoCode field via Apex or the API will cause Salesforce to convert the money fields on the SObject into the new currency using the appropriate conversion rate.
• Changing an SObject’s CurrencyIsoCode field via the user interface will not update the money fields on the SObject.

  Advanced Currency Management

  The solution described above, where we maintain a list of available currencies, and the exchange rates for those currencies related to a corporate currency, we might call “Standard” multi-currency in Salesforce, though it is not, to my knowledge, labeled that way by Salesforce.

This seems on cursory examination, to be a workable solution. However, on deeper inspection, some troublesome scenarios arise.

Consider an Opportunity, denominated in a currency (let’s say GBP) other than my corporate currency (say USD), and won at some point in the past. I want to know how much revenue that Opportunity represented in my corporate currency - perhaps to include in a report, or in a rollup field of some kind.

My seemingly adequate multi-currency solution cannot answer this fundamental question! I can tell you how many dollars this Opportunity would be worth today by consulting the appropriate CurrencyType record and doing some simple arithmatic, but my solution doesn’t know what the exchange rate was when the Opportunity was won, and so cannot tell me how many dollars I actually made.

To address the deficiency described above, Salesforce allows its customers to enable Advanced Currency Management in their organization. This feature introduces a new behind-the-scenes object, the DatedConversionRate. This object is used to store historical currency conversion rates - each time the conversion rate for a configured currency is established or changed, DatedConversionRate records are created and updated. These records can be queried with SOQL to retrieve the conversion rate between any configured currency and the corporate currency, for any date.

Unfortunately, the standard Apex Langauge features for currency conversions only take advantage of these historical conversion rates for a very small set of standard objects.

Converting Currencies Using Apex Language Features

Now that we understand the way Salesforce stores and manages currencies and conversion rates, we can look at how you can work with money fields and multiple currencies in Apex. The Apex language does not include classes or methods to execute currency conversions. However, money fields can be retrieved using SOQL in three different currencies, as described below.

In the Currency of the SObject

The standard SOQL query will return the money field value in the currency of the SObject’s CurrencyIsoCode field:

Opportunity o = [SELECT Id, Amount FROM Opportunity where Id = 'XXX'];

If the retrieved Opportunity’s Amount is 200 GBP, then o.Amount will be 200.00.

In the Querying User’s Configured Currency

As part of their profile, each Salesforce user can select their own currency. When they create Salesforce objects, CurrencyIsoCode is set to this configured currency. Money fields on SObjects can be displayed in the user interface in the currency of the SObject, and for convenience, in the user’s configured currency as well.

SOQL includes a special function to return money fields in the configured currency of the querying user. Considering the same 200 GBP example Opportunity as above, a user whose configured currency is Japanese Yen might execute the following query:

Opportunity o = [SELECT Id, convertCurrency(Amount) FROM Opportunity where Id = 'XXX'];

In this case, o.Amount would return something like 37554, the value of the Opportunity in Japanese Yen, using the conversion rate on the close date of the Opportunity (Advanced Currency Management is available for the Opportunity record and uses the CloseDate field to determine the conversion rate to use).

In the Organization’s Default/Corporate Currency

SOQL aggregate queries always return money fields in the default/corporate currency. So if our corporate currency is US Dollars, you can get the US Dollar value of our 200 GBP Opportunity with this query:

List<AggregateResult> arList = [SELECT Id, MAX(Amount) FROM Opportunity where Id = 'XXX'];

The expression (Decimal) arList[0].get('Amount')returns something like 252.63, the US Dollar equivalent of 200 GBP, using the appropriate historical conversion rate.

Shortcomings of Apex Language Features - A Sampling of Broken Use Cases

Need Data In a Currency Other Than SObject, User, or Corporate

After enabling Advanced Currency Management, Salesforce no longer allows administrators to define roll-up summary fields that aggregate money fields from Advanced Currency Management objects (e.g. Opportunties) to non-Advanced Currency Management obejcts (e.g. Accounts).

Your business users may still desire these fields - aggregating money fields from Opportunities to Accounts is a very common use case - and you may need to find a way to deliver these with a custom Apex solution.

The challenge is that both the Account record and related Opportunity record(s) can be denominated in any of the supported organization currencies. So you need to be able to convert any currency into any other, so that you can always convert the Opportunity money values into the Account currency before storing them on the Account record. But as we have seen above, our standard Apex language features only let us convert into three currencies - the SObject, the user, or the corporate currency. If we want something else, we are out of luck.

Opportunity Conversion Needs to Use A Different Date

Advanced Currency Management uses the CloseDate field on the Opportunity to determine the conversion rate to use. But what if your business users want to use a different date? Perhaps they want to use the CreatedDate of the Opportunity, or the LastModifiedDate of the Opportunity, or some custom date entirely that is meaningful to the business and tracked on the Opportunity?

Custom Object Needs Historical Conversion Rates

Advanced Currency Managed does not support custom objects - so you cannot use the Apex Language currency features to perform currency conversions for custom objects using historical conversion rates.

Developing a General Conversion Function

So it is clear that there are some gaps in the provided tools for working with multiple currencies in Apex. We need a general solution that can convert between any two currencies, using historical or current conversion rates, and that can be used for any SObject, including custom objects.

Conversion using Current Conversion Rates

Conversions using current conversion rates are trivial. One approach is to use a utility class with a static convert method that takes the amount, “from” currency, and “to” currency as parameters, and returns the converted amount. We can retrieve the current conversion rates for all configured currencies when the class is instatiated. The class and method would look something like this:

public with sharing class CurrencyConverter {
  private static Map<String, CurrencyType> currencyMap = new Map<String, CurrencyType>();

  static {
    for (CurrencyType ct : [
      SELECT Id, IsCorporate, IsoCode, ConversionRate, DecimalPlaces, IsActive
      FROM CurrencyType
    ]) {
      currencyMap.put(ct.IsoCode, ct);
    }
  }

  public static Decimal convert(
    Decimal value,
    String fromCurrencyCode,
    String toCurrencyCode
  ) {
    CurrencyType fromCurrency = currencyMap.get(fromCurrencyCode);
    CurrencyType toCurrency = currencyMap.get(toCurrencyCode);
    Decimal conversionRate =
      toCurrency.ConversionRate / fromCurrency.ConversionRate;
    return value * conversionRate;
  }
}

This approach is simple and relatively efficient - it uses only SOQL query per transaction, and then performs the conversion using simple arithmatic.

Conversion using Historical Conversion Rates

Considering the problem of developing a utility function that supports historical conversion rates is when this problem gets interesting. First, it is important to understand how Salesforce stores historical conversion rates. The DatedConversionRate object has the following fields:

• IsoCode - the ISO code of the currency for which the conversion rate is defined
• StartDate - the date on which the conversion rate becomes effective
• NextStartDate - the date on which the conversion rate is superseded by a new conversion rate
• ConversionRate - the conversion rate from the currency to the corporate currency

Let’s see what happens when you first configure a new currency in the Salesforce setup user interface. We’ll add Japanese Yen today (Nov 27 2023), with a conversion rate to USD of 148.44. Here is the new DatedConversionRate record that is created:

Note that the dates that Salesforce has selected ensures that the conversion rate we have provided is always effective. Now let’s change the conversion rate to 149.44 in the user interface and see what changes in our DatedConversionRate table for JPY:

Now hopefully, Salesforce’s approach here is clear. As conversion rates are updated by the administrator in the setup user interface, rows are added to DatedConversionRate, such that we always have a record to tell us the conversion rate for any date.

Now let’s consider how we might use this information to develop a general currency conversion function. The simplest solution for our convertDated function would be to add a fourth parameter for the conversion rate date, and then query the DatedConversionRate table for the conversion rates for our to and from currencies on that date. We could then use the conversion rates to perform the conversion. The function would look something like this:

public static Decimal convertDated(
  Decimal value,
  String fromCurrencyCode,
  String toCurrencyCode,
  Date conversionRateDate
) {
  DatedConversionRate fromRate = [
    SELECT ConversionRate
    FROM DatedConversionRate
    WHERE IsoCode = :fromCurrencyCode
    AND StartDate <= :conversionRateDate
    AND NextStartDate > :conversionRateDate
  ];
  DatedConversionRate toRate = [
    SELECT ConversionRate
    FROM DatedConversionRate
    WHERE IsoCode = :toCurrencyCode
    AND StartDate <= :conversionRateDate
    AND NextStartDate > :conversionRateDate
  ];
  Decimal conversionRate = toRate.ConversionRate / fromRate.ConversionRate;
  return value * conversionRate;
}

This approach is simple and effective, but it has a fatal drawback - it requires two SOQL queries per conversion! This is not a scalable solution - imagine processing a set of 200 records in a trigger context, where you need to perform two conversions for each record. That’s 400 SOQL queries, and Apex governor limts only allow 100 in the entire transaction. This solution will not work.

We need to cache the conversion rates, in a similar fashion to how we cached the current conversion rates in a map in the CurrencyConverter class, for our convert method to access without using a SOQL query. But caching the historical conversion rates from the DatedConversionRate table is not as simple as it was for the current conversion rates stored in the CurrencyType table.

Consider an organization that supports eight currencies, and updates the conversion rates for each currency daily. So that is 7 DatedConversionRate records per day (the corporate currency conversion rate never changes), or 2,555 records per year. If we need to cache the conversion rates for all currencies for the past 10 years, that is 25,550 records. As the Apex governor limits you to 50,000 records retrieved in a transaction, we are burning half of this allocation just caching conversion rates!

We need a more efficient solution. We need to cache the conversion rates for all currencies, for all dates, but we need to do so in a way that does not consume so many SOQL queries.

At this point you might be feeling a bit stuck. You might be considering some desperate, hacky solutions - perhaps using DML to update the currency for the SObject in question to our desired currency, which will force Salesforce to perform the conversion, reading the updated value, and then reverting the SObject with a second DML statement. But this is a terrible, terrible solution - it will likely cause all sorts of problems with triggers and workflows, the redundant processing will likely get you into governor limit danger anyway, and it does not solve for the user case around custom objects.

Fear not! There is an elegant solution to caching a large set of data like our historical conversion rates in a way that we can access it without running afoul of the Apex governor.

Enter Salesforce Platform Cache

Salesforce Platform Cache is a feature that allows you to cache data in a key-value store, and make it available across the organization for a configurable period of time. Most critically for our situation, cache data can be accessed from Apex without SOQL queries.

We first set up an org cache partition called ExchangeRates to store our cached conversion rates. Then we need to design the structure of our cached data. Salesforce’s guidance indicates that it is preferable to cache fewer, large items rather than many small ones, and sets the limit of each cached value to 100KB. After some experimentation, I found that I could store a year’s worth of conversion rates for eight configured currencies in a single cached value of about 36KB. So with a cache key of 2023, we store a value of a Map<String, Map<String, Decimal>> object whose structure looks like this (represented as JSON):

{
  "20230101": {
    "USD": 1.0,
    "EUR": 0.8,
    "GBP": 0.6,
    "JPY": 100.0,
    ...
  },
  "20230102": {
    "USD": 1.0,
    "EUR": 0.8,
    "GBP": 0.6,
    "JPY": 100.0,
    ...
  },
  ...
}

So to store five years worth of conversion rates, we need five cached values, one for each year.

Populating the Platform Cache

We’ll take two different approaches to populating the Platform Cache with our conversion rates. First, we want to rebuild the cache daily when our exchange rates are updated, so that we always have the most up-to-date conversion rates available for our date conversion function. Second, as a backup, we will define a class that implements the Cache.CacheBuilder interface. This class will be invoked by Salesforce whenever there is a cache miss, and will populate the cache with the appropriate conversion rates for the requested year, and then return the value to the caller.

Daily Cache Rebuild

A scheduled Apex batch class can be used to rebuild the cache daily, setting the expiration date to the next day. This class would be scheduled to run after the daily conversion rate update. It also includes a static method, getDateKey, to build the date key to retrieve the rate table for a given date from the cache value. The class might look something like the following - it takes a year as a parameter to the constructor, and then rebuilds the cache for that year:

public with sharing class ExchangeRatesCacheBatch implements Database.Batchable<SObject> {
  private String query;
  private Integer year;

  public ExchangeRatesCacheBatch(Integer year) {
    this.year = year;
  }

  public Database.QueryLocator start(Database.BatchableContext context) {
    Cache.Org.put(
      'local.ExchangeRates.' + String.valueOf(year),
      new Map<String, Map<String, Decimal>>(),
      86400
    );
    return Database.getQueryLocator(
      [
        SELECT IsoCode, StartDate, NextStartDate, ConversionRate
        FROM DatedConversionRate
        WHERE
          StartDate <= :Date.newInstance(year, 12, 31)
          AND NextStartDate > :Date.NewInstance(year, 1, 1)
      ]
    );
  }
  public void execute(
    Database.BatchableContext context,
    List<DatedConversionRate> scope
  ) {
    Map<String, Map<String, Decimal>> dateMap = (Map<String, Map<String, Decimal>>) Cache.Org.get(
      'local.ExchangeRates.' + String.valueOf(year)
    );

    Date activeDate = Date.NewInstance(year, 1, 1);
    while (activeDate < Date.newInstance(year + 1, 1, 1)) {
      Map<String, Decimal> rates = new Map<String, Decimal>();
      for (DatedConversionRate dcr : scope) {
        if (dcr.StartDate <= activeDate && dcr.NextStartDate > activeDate) {
          rates.put(dcr.IsoCode, dcr.ConversionRate);
        }
      }
      dateMap.put(getDateKey(activeDate), rates);
      activeDate = activeDate.addDays(1);
    }
    Cache.Org.put(
      'local.ExchangeRates.' + String.valueOf(year),
      dateMap,
      86400
    );
  }
  public void finish(Database.BatchableContext context) {
  }

  public static String getDateKey(Date dt) {
    Datetime dtWithTime = DateTime.newInstance(
      dt.year(),
      dt.month(),
      dt.day(),
      0,
      0,
      0
    );
    return dtWithTime.format('yyyyMMdd');
  }
}

Cache Builder Class

The following class implements the Cache.CacheBuilder interface. This class is invoked by Salesforce whenever there is a cache miss, and will populate the cache with the appropriate conversion rates for the requested year, and then return the value to the caller.

public with sharing class ExchangeRatesCache implements Cache.CacheBuilder {
  public Object doLoad(String key) {
    Integer year = Integer.valueOf(key);

    Date startDate = Date.newInstance(year, 1, 1);
    Date endDate = Date.newInstance(year, 12, 31);

    List<DatedConversionRate> dcrs = [
      SELECT IsoCode, StartDate, NextStartDate, ConversionRate
      FROM DatedConversionRate
      WHERE StartDate <= :endDate AND NextStartDate > :startDate
    ];

    Map<String, Map<String, Decimal>> dateMap = new Map<String, Map<String, Decimal>>();
    Date activeDate = startDate;
    while (activeDate < endDate.addDays(1)) {
      Map<String, Decimal> rates = new Map<String, Decimal>();
      for (DatedConversionRate dcr : dcrs) {
        if (dcr.StartDate <= activeDate && dcr.NextStartDate > activeDate) {
          rates.put(dcr.IsoCode, dcr.ConversionRate);
        }
      }
      dateMap.put(ExchangeRatesCacheBatch.getDateKey(activeDate), rates);
      activeDate = activeDate.addDays(1);
    }
    return dateMap;
  }
}

Using the Platform Cache

Now that we have designed and implemented our cache, we can update our simple convertDated function to use it instead of querying for DatedConversionRate records.

  public static Decimal convertDated(
    Decimal value,
    String fromCurrencyCode,
    String toCurrencyCode,
    Date rateDate
  ) {
    validateInput(fromCurrencyCode, toCurrencyCode);

    Cache.OrgPartition orgPart = Cache.Org.getPartition('local.ExchangeRates');
    Map<String, Map<String, Decimal>> cacheData = (Map<String, Map<String, Decimal>>) orgPart.get(
      ExchangeRatesCache.class,
      String.valueOf(rateDate.year())
    );

    Map<String, Decimal> rates = cacheData.get(
      ExchangeRatesCacheBatch.getDateKey(rateDate)
    );
    Decimal fromRate = rates.get(fromCurrencyCode);
    Decimal toRate = rates.get(toCurrencyCode);

    Decimal conversionRate = toRate / fromRate;
    return value * conversionRate;
  }

The new function retrieves rate data from the cache, passing as a parameter our CacheBuilder class to handle any cache misses. It requires no SOQL calls at all if the cache is populated, and only one SOQL call if the cache is empty to rebuild an entire year of the cache.

Conclusion

Hope you have found this peek at the ins and outs of currency conversion in Salesforce useful! Let me know if you have a better, more effective, or simpler approach for ad-hoc currency conversion in Salesforce - I would love to hear about it!