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.
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:
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
Anonymous
May 08, 2009
David Musgrave at Developing for Dynamics GP has info on getting the RW_ConverttoWordsandNumbers() functionAnonymous
July 07, 2009
Need to display "Sixty Dollars and fifty cents" but after using this function it shows "Sixty Dollars and 50 cents" please helpAnonymous
July 07, 2009
Hi Devesh Please see the new post with the solution to your question. http://blogs.msdn.com/developingfordynamicsgp/archive/2009/07/08/getting-rw-converttowordsandnumbers-to-show-cents-in-words.aspx DavidAnonymous
August 08, 2009
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 MohammedAnonymous
August 09, 2009
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