Last week, I discussed an issue where Currency values returned by VBA to a report are multiplied by 10 or 100. To workaround the issue we used string calculated fields rather than currency calculated fields on the report and formatted our values with the FormatCurrency() or FormatNumber() functions (depending on if we wanted the currency symbol or not).
A comment on that post asked how do you handle multicurrency. The above method works great for functional currency, but what if you want to to show a multicurrency value that uses a different currency symbol. Good question.
The quick and nasty solution is to use FormatNumber() and drop the currency symbols entirely and then add the Currency ID onto the report to say "These numbers with no currency symbol are really XX currency". Not a very nice solution.
There must be a nicer way.... Well after a bit of research I found there was.
Just before I explain the technique, I want to highlight the following Knowledge Base (KB) article which can be used to overcome errors when currency symbols interfere with calculations.
If you have any issues reading the values of multicurrency fields from a report, use this KB to strip out the non-numeric characters to get the numeric value.
Note: My testing with Microsoft Dynamics GP 2010 showed that a currency field displayed as C$1,000.00 in the report was brought into VBA as a currency value of 1000.00 and not a string value of "C$1,000.00". So the workaround in the KB article might not be needed anymore.
I started off with a plan to read a multicurrency field from the report and this would provide a string representation of what the currency symbol is. The note above mentions that this does not work as the currency field is now returned to VBA as currency data and so I cannot get the formatted field.
After some searching, I found the Report Writer function rw_FormatCurrencyToString() in the Purchasing Series. This RW function takes two parameters, a Currency Index and a currency value, and outputs a string with the formatted currency value. Perfect. Just what I needed.
So, I created a string calculated field defined as follows:
FUNCTION_SCRIPT( rw_FormatCurrencyToString Currency Index -1000.00000 )
I used the currency constant of -1000.00000 so that I would be able to see if the thousands were grouped and how a negative value was represented.
Adding this field to the report and making it invisible (via Tools >> Field Options, Ctrl-F or double clicking). I added this now hidden field and the other required fields to VBA and started working on the code to handle the currency formatting.
The VBA code uses a number of "Template" variables to describe the format. What is the prefix? What is the suffix? How many decimal places? Is a negative value shown with parenthesis? Are the negative symbols shown after the suffix or before the prefix. Is the minus sign before or after the number? Are thousands grouped?
The ReadTemplateprocedure then analyzes the template as provided by the rw_FormatCurrencyToString() RW Function and sets the "Template" variables accordingly.
Then when I want to output a currency value formatted to the same template, I can call the FormatTemplate function.
To demonstrate the techniques I have updated the custom report from the previous post. Below is the code used and the output produced.
Report with Multicurrency Output
Value in VBA Currency Field Formatted String
123.00 $123.00 $123.00
123.40 $1,234.00 $123.40
123.45 $12,345.00 $123.45
Multicurrency Z-C$ Canadian Dollars
1000.00 + 123.00 C$1,123.00 C$1,123.00
1000.00 + 123.40 C$11,234.00 C$1,123.40
1000.00 + 123.45 C$112,345.00 C$1,123.45
NOTE: The package with the Multicurrency version of the VBA Currency Test custom report is attached to the bottom of this post.
There might be a few currency format configurations that the VBA code cannot handle, for example: grouping thousands using spaces. It is not perfect, but it can handle almost everything.
Hope this is useful.