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

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

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

POP Purchase Order Other Form.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

9 thoughts on “RW – Getting RW_ConvertToWordsAndNumbers() to work with multi-currency

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

    Like

  2. 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

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.