RW – Getting RW_ConvertToWordsAndNumbers() to work with multi-currency


David MeegoI 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) Secure Link 

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.

05-Jul-2010: Also look at new function in the post: Announcing Report Writer Function RW_ConvertToWordsAndNumbersParse.

POP Purchase Order Other Form.zip

Comments (5)

  1. David Musgrave at Developing for Dynamics GP has info on getting the RW_ConverttoWordsandNumbers() function

  2. Devesh says:

    Need to display "Sixty Dollars and fifty cents" but after using this function it shows "Sixty Dollars and 50 cents" please help

  3. Mohammed says:

    Hi David

    This very useful since these many days we customized thru VBA. Hope you that if the retuned char is more than 80 char it is not showing the whole words e.g. 7,777,777.77

    I hear that there was one function is available for this to split into 2 strings eventhough I check with the SDK doc I can’t get the desired result will you please help me on this

    Thanks

    Mohammed

  4. David Musgrave says:

    Hi Mohammed

    The link for the VBA example of how to work around the 80 character limit for string calculated fields is provided in this post.

    Hybrid – Cheque Amount in Words Example

    David

Skip to main content