RW - Getting RW_ConvertToWordsAndNumbers() to work with multi-currency
I recently had a support case where the partner consultant was trying to use the RW_ConvertToWordsAndNumbers() report writer user defined function to display the Purchase Order total in words at the bottom of the document.
They were using the POP Purchase Order Other Form (but this method works for the POP Purchase Order Blank Form as well) and had created a calculated field using the RW_ConvertToWordsAndNumbers() function similar to one shown below:
| Name: |
(c) ConvertToWordsAndNumbers |
| Result Type: |
String |
| Expression Type: |
Calculated |
| Calculated: |
FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbers cyPrintTotal POP_PO.Currency ID 0) |
The problem was that even though the amount correctly changed when the originating or functional multi-currency view was selected, the wording always used the originating currency's terms.
For example: For a system with a functional currency of US Dollars (Z-US$) and a transaction with an originating currency of UK Pounds (Z-UK), the string returned was always in the form "X Pounds and Y Pence". The amounts were correct, by the terminology did not change.
Solution for Purchase Order Processing (POP) Forms
The problem is that the POP_PO.Currency ID field from the Purchase Order Work table is a constant value containing the originating Currency ID for the transaction. What is needed is a field that will have the actual Currency ID for the currency view being printed.
Looking at other calculated fields on the report I could see that the calculated field nLegend4 was being used to indicate which currency view was being printed. nLegend4 is an integer representation of the Legend 4 field and is used to indicate which view is being used 1 = Functional, 2 = Originating.
Getting the Functional Currency ID is simple as it is always available from Function Currency of Globals. Getting the Originating Currency ID is slightly more complex. Our calculated fields will be using the field Display Type of Data. If we were to use Data for the Currency ID field from the Purchase Order Work table in a footer section, it will already be pointing at the next record. So we need to use Display Type of Last for the Currency ID field. The report already has a hidden Currency ID field with Display Type of Last in the Report Footer section so we can re-use that field.
So, we just need a calculated field which will display the correct Currency ID based on the value in the Legend 4 field. We can then use this field with the report writer function to get the desired results:
| Name: |
(c) Currency ID |
| Result Type: |
String |
| Expression Type: |
Conditional |
| Conditional: |
nLegend4 = 1 |
| True Case: |
Functional Currency of Globals |
| False Case: |
RF_LAST Currency ID |
So the updated calculated field from before is:
| Name: |
(c) ConvertToWordsAndNumbers |
| Result Type: |
String |
| Expression Type: |
Calculated |
| Calculated: |
FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbers cyPrintTotal (c) Currency ID 0) |
Now when the report is printed both the about and the currency terminology will change depending on the currency view used.
Solution for Sales Order Processing (SOP) Forms
An almost identical question was asked on the Partner Forum, but this time it was about the SOP Blank Invoice Form. To make this work for the SOP Blank Invoice Form requires the same technique. First you will need to drag the Currency ID field from the Sales Transaction Work field into the Report Footer (RF) section. By default the field will be set to Data type of Last.
Then build the exact same calculated fields as decribed above but change the fields used as shown in the table below:
| Field |
POP Forms |
SOP Forms |
| Currency View |
nLegend4 |
(C) Force Functional |
| Document Total |
cyPrintTotal |
F/O Document Amount |
For more detailed steps please see the following Knowledge Base (KB) article.
How to modify the SOP Blank Invoice Form to display the total amount of the invoice in words in Microsoft Dynamics GP and in Microsoft Great Plains (KB 917473) 
Note: This article has now been updated to pass in a Currency ID, instead passing a blank "" string. Passing a blank currency ID to the RW function meant it was always using terminology (Dollars/Cents, Pounds/Pence, etc.) from the functional currency regardless of the currency view for the document.
More Information
For more information on using the RW_ConvertToWordsAndNumbers() function and other Report Writer functions please see the following posts:
Hybrid - Cheque Amount in Words Example
Using the built-in Report Writer Functions
An example package of the v10.0 POP Purchase Order Other Form is attached at the bottom of the article.
Hope you found this example useful.
David
08-Jul-2009: Posted follow up article: RW - Getting RW_ConvertToWordsAndNumbers() to show cents in words.
