Taking Multi-Currency in Salesforce.com to the Next Level

mgsmith | Sunday, January 25th, 2009 | 12 Comments »

Camtasia

Note: Post updated 28 Jan 2009 to include the trigger code discussed.

As SalesForce prepares the Spring ’09 release, I wanted to touch on what I think is an area they need to focus on – Currency Management.

In my opinion one of the biggest missing components of SalesForce.com has been around Currency Management, specifically around viewing amounts in different currencies using the correct exchange rate. Though SalesForce.com supports multi-currency and even added dated exchange rates in the Summer ’07, there are some severe limitations around this feature that can make it difficult for organizations to utilize Salesforce.com for more financial-type reporting.

From the SalesForce.com help on the advanced currency features:

Advanced Currency Management Considerations

  • Dated exchange rates are used for opportunities, opportunity products, opportunity product schedules, campaign opportunity fields, and reports related to these objects and fields. Dated exchange rates are not used in forecasting, currency fields in other objects, or currency fields in other types of reports.
  • If you enable advanced currency management, you cannot create roll-up summary fields that calculate currency on the opportunity object. All existing currency-related roll-up summary fields on the opportunity object will be disabled and their values will no longer be calculated. If your organization enables advanced currency management, you should delete any currency roll-up summary fields using opportunities and accounts or opportunities and custom objects.
  • Campaign opportunity fields use dated exchange rates when calculating the amount in the campaign currency, but are not used when converting those amounts to the user currency.
  • Cross-object formulas always use the static conversion rate for currency conversion.

The two big problems with the above are:

  1. Dated exchange rates only used on limited objects; specifically no custom objects.
  2. Roll-ups on the Opportunity currency fields no longer work.

If a company wanted to leverage the power of the SalesForce.com platform to use for a system that required more accurate currency reporting, such as a Project Accounting system, these are major limitations. Luckily, SalesForce.com does a great job listening to their customers. Searching through the posted ideas, I found some roughly similar thoughts, but nothing that specifically addressed dated exchange rates on custom objects. My long term preference for this is for SalesForce.com to add a Formula Function to convert any value to any currency using an effective date: ConvertCurrency(Amount, From_ISOCode, To_ISOCode, Effective_Date). I posted this idea on the Force.com Ideas board. If you agree with my recommendation, please review and promote my idea.

http://ideas.salesforce.com/article/show/10095125/Support_dated_exchange_rates_in_custom_objects

While we wait for SalesForce.com to address these issues in their base functionality, there is a way to work around this using Apex triggers. The general idea is to create converted currency field(s) on the necessary objects and use the trigger to update the values on these. I’ll avoid using code to keep the post short, but tThe functionality would work as follows. The trigger code can be found at the end of this post:

  • Create an Amount_XXX (where Amount is a field name and XXX is the currency) field on objects where you need to be able to roll-up or display amounts in XXX currency using the dated exchange rate. For example, Amount_USD would contain the Amount field converted USD.
  • Create an Insert/Update Trigger on the object to set this field value based on a specific date field on the object. The trigger would lookup the exchange rate based on a specific date field on the object, do the conversion and then update the Amount_XXX field

The key downside to this methodology is that you’d need to have one custom field (and field-specific trigger code) per field and currency that requires conversion. However, if you really must be able to roll-up, display, and/or report on a custom object using dated exchange rates this is a viable work-around.

Fixing the inherent problems with multi-currency would enable vendors to develop powerful financials related applications using the Force.com API. I’m not necessarily suggesting that the full financial software (AR, AP, GL) be moved to the SaaS model. That has its own set of potential problems – especially around auditing and security. What I am noting is that there are other uses for strong financial capabilities outside of the standard AR/AP/GL model. Even if you wanted to do something simple like generate Invoices from sold SalesForce.com Opportunities, not being able to track the exact amount invoiced in the correct currency is a big concern.

trigger trg_Invoice on Custom_Invoice__c (before insert, before update) {

// ----------------------------------------------------------------------
// 26 Jan 2009: M.Smith
// http://www.sfcnmore.com/
//
// Update a custom currency converted field using dated exchange rages
// This works around limitations with dated exchange rates on custom objects
// ----------------------------------------------------------------------

/* ......................................................................
* The following variables should be filled in for the code to work properly
* The Trigger is designed to operate generically based on the values set below
* Note: The nDecimalPlaces variable must be adjusted for the object & field as well
* ....................................................................... */
String ToCurrencyCode = 'USD';
String FIELD_CURRENCYCODE = 'CurrencyIsoCode';
String FIELD_AMOUNT = 'Invoice_Amount__c';
String FIELD_DATE = 'Invoice_Date__c';
String FIELD_CONVERTED_AMOUNT = 'Invoice_Amount_USD__c';
// --- The following two need to be set to the actual object (table) for trigger
// Have to adjust the Object and Fieldname below in order to get the decimal places.
Integer nDecimalPlaces =
    Custom_Invoice__c.Invoice_Amount_USD__c.getDescribe().getScale();
* ....................................................................... */

try {

      // A list of the Currency codes to retrieve from the DatedConversionRates table
      List<String> CurrCodes = new List<String>();
      // A list of item numbers to process from the Trigger.New[] collection
      List<Integer> TriggerItem = new List<Integer>();

      // Trigger.new is a list of the 'Objects' that were updated
      // This loop iterates over the list, and adds any that have a
      // change in the 3 key fields
      for (Integer i = 0; i < Trigger.new.size(); i++) {
        // Check to see if any of the following fields have changed:
        //  - AMOUNT field
        //  - DATE field (the date used for currency conversion)
        // If either has changed (of if this is a new record), remember
        // this record ID
        if (Trigger.IsInsert) {
           // Build a list of objects to be processed
           TriggerItem.add (i);
           CurrCodes.add( (string)Trigger.new[i].Get(FIELD_CURRENCYCODE));
        } else {
           if ((Trigger.old[i].Get(FIELD_AMOUNT) != Trigger.new[i].Get(FIELD_AMOUNT))
           || ( Trigger.old[i].Get(FIELD_DATE) != Trigger.new[i].Get(FIELD_DATE))
           || ( Trigger.old[i].Get(FIELD_CURRENCYCODE) != Trigger.new[i].Get(FIELD_CURRENCYCODE)) )
           {
              // Build a list of objects to be processed
              TriggerItem.add (i);
              CurrCodes.add( (string)Trigger.new[i].Get(FIELD_CURRENCYCODE));
           }
        }
      }

      // To allow this to work for BULK operations (> 20 records), query all of
      // the conversion rates for the currencies that are used in the records being
      // processed. This acts as a Cache to avoid querying the table for each record
      // being inserted/updated. Loop through these in memory each time to find the
      // correct rate to use
      List<DatedConversionRate> = [SELECT ISOCode, ConversionRate,
          StartDate, NextStartDate FROM DatedConversionRate
          WHERE ISOCode in :CurrCodes];

      // For each record being inserted/updated
      for ( integer i : TriggerItem)
      {
         // Retrieve the proper DatedConversionRate based on the Date fields value
         // field. Change this field name to adjust to the appropriate Custom object.
         date dDateFieldValue = (date)Trigger.new[i].Get(FIELD_DATE);
         double nRate = -1;
         for (DatedConversionRate exchRate : ExchRateCache) {
            // Looping through the cached DatedConversionRate object
            if (exchRate.ISOCode == Trigger.new[i].Get(FIELD_CURRENCYCODE)
            && exchRate.StartDate <= dDateFieldValue
            && exchRate.NextStartDate > dDateFieldValue) {
               // Look for a match for CURRENCYCODE and within the Date Ranges
               nRate = exchRate.ConversionRate;
               break;
            }
         }

         System.assertNotEquals(nRate, -1, 'A Rate was not found for the ' +
         dDateFieldValue + ' & ' + Trigger.new[i].Get(FIELD_CURRENCYCODE));
         /* System.Debug('Exchange Rate = ' + nRate); */

         // Convert the AMOUNT field to the Target Currency
         // Use .setScale() to set the decimal places accordingly
         Decimal nOriginalAmt =
            ((Decimal)Trigger.new[i].Get(FIELD_AMOUNT)).setScale(nDecimalPlaces);
         Decimal nAmount = (nOriginalAmt / nRate).setScale(nDecimalPlaces);

         // Update the field (BEFORE INSERT/UPDATE ONLY)
         Trigger.new[i].put(FIELD_CONVERTED_AMOUNT, nAmount);

      }
  } catch (Exception e) {
      // Log any errors in the debug logs
      System.Debug('Error Detected...Logic Skipped: ' + e.getMessage()
         + '/' + e.getCause());
  }

}

12 Comments

  1. Ron says:

    Any chance you have the test code already written?

  2. mgsmith says:

    Ron,

    There should be a test class in the Zip file.

    Best regards,

    Mike

  3. Dmitri says:

    Mike, I am not sure I see the Zip file. I’d love to get the copy of the test class as well.

    Thanks,
    Dmitri

  4. Dmitri says:

    Any specific reason you chose to go this route, rather than just store and updte a custom field Exchange Rate on the Order Record and do all conversions via formula fields?

  5. mgsmith says:

    Dmitri,

    You’re correct. I could have setup a Trigger to store the Exchange Rate(s) in the object and used formulas for the values. The real caveat for this is you’d have to be sure that the Exchange Rate value never changes once it’s stored in the record, or build logic into the Triggers to handle that. If you’re talking about records in the future (like an Opportunity), the record rate will change every time the system rates are updated until the Close Date has passed.

    Also, the Zip file is in the Source Code tab.

    Best Regards,

    Mike

  6. Harshesh says:

    What will be the type of field Amount_XXX?

  7. mgsmith says:

    Harshsesh,

    In that example, Amount_XXX would probably be a numeric field (versus Currency).

    Best Regards,

    Mike

  8. tomomi says:

    I cannot see a code why?

  9. MB says:

    Any chance the trigger code is still available?

  10. mgsmith says:

    I fixed the code issues. Not sure what happened, but it’s there now.

    Best Regards,

    Mike

  11. Jazmin Us says:

    Passed by your post and decided to share it on my blog so my followers can see it too. I used the same title, “Force2b Technology Consultant » Blog Archive » Taking Multi-Currency in Salesforce.com to the Next Level”

  12. Iwen says:

    Nice work Mike!

    Any chance of giving me a pointer on how to set it up for multiple currencies? I.e. I need both a USD and EUR translation. Just starting with Apex so I’m having a little trouble understanding exactly what the code does. It seems that if I change the corporate currency, that influences the result of my FIELD_CONVERTED_AMOUNT. I.e. if corporate currency is USD, the result will be in USD, if it’s EUR, the result will be in EUR. The ToCurrencyCode in the trigger does not seem to influence the result, not in my sandbox at least…

    Thanks and best regards,

    Iwen

Leave a Reply