Displaying Currencies in a SFDC Email Template

Glenn Weinstein

A few weeks ago I posted here about displaying currencies in a SFDC formula field or Visualforce page.  The key challenge is preserving the comma separators and ensuring exactly 2 decimal places, so that, for example, you see USD 2,978.10 and not USD 2978.1.  To do this, I wrote an elaborate formula, which could be used in defining a custom field to go alongside your original currency field.

I’ve now extended the concept in two important ways:

• I want to display currencies in SFDC email templates (specifically Visualforce templates).  Emails just don’t look right when you are talking about currency and display “2978.1”. 
• I don’t want to have to add a custom formula field for every currency field.  I’d rather have some kind of generic solution that I can use ad-hoc when creating new email templates, without having to modify the data model.

I actually wish SFDC had a built-in formula function for this, like Javascript’s toFixed() function, that let you specify exactly how many decimal places to display.  But until they add something like that, we’ll need to resort to some trickery.

The approach is actually fairly simple – wrap my elaborate SFDC formula into a Visualforce custom component.  I’ve named it asCurrency.  To use it in a Visualforce email template, all I have to do is wrap my currency field in an asCurrency tag:

<c:asCurrency amount="{!relatedTo.Some_Currency_Field__c}" />

That’s reasonably clean.  I’m happy enough for now!

Here’s the source code for the Visualforce custom component:

<apex:component access="global">

  <!--

  (c) 2010 Appirio

  Render a field as currency, that is, with exactly two decimal places, and comma separators for 3-digit groupings.

  (Yes, I realize not all global currencies should be rendered this way.  But for our purposes, it works.)

  This is needed because, frustratingly, Visualforce can't do this natively - it tends to render Currency field with
  the minimal decimal places, e.g. USD 1,200.20 is rendered as USD 1200.2, which looks really funny.

  Note that the "amount" attribute is typed as "Decimal", because SFDC barks at "Currency" - if you actually
  try to pass in a Currency field, the compiler claims the field is "BigDecimal" (a non-existent Apex type...
  sigh) but it will accept Currency fields if we type the attribute as "Decimal".  Weird, but.... whatever works.

  2/21/10     Glenn Weinstein     Original

  -->

<apex:attribute name="amount"
    type="Decimal"
    description="The value to be rendered as currency."  
  />

  {!IF(
    amount >= 1000000,
    TEXT(FLOOR(amount / 1000000)) & ",",
    "") &
  IF(
    amount >= 1000,
    RIGHT(TEXT(FLOOR(amount / 1000)), 3) & ",",
    "") &
  RIGHT(TEXT(FLOOR(amount)), 3) & "." &
  IF(
    MOD(amount, 1) * 100 < 10,
    "0" & TEXT(ROUND(MOD(amount, 1), 2) * 100),
    TEXT(MIN(ROUND(MOD(amount, 1), 2) * 100, 99))
  )}

</apex:component>

Be sure to name your custom component “asCurrency”.  Note, we need the access=”global” bit (in the top-level apex:component tag) in order for our component to be available to Visualforce email templates.