Number to Words in Report Writer

By David Musgrave

One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions) was the RW_ConvertToWordsAndNumbers() function to convert a currency amount into words. After the code was added, it was realised that Report Writer calculated fields of return type string are limited to 80 characters. This means that if your amount in words is longer than 80 characters, only the first 80 characters will be returned and the rest will be truncated and lost.

This example using the "Check with Stub on Top" report was created to provide a method of using Visual Basic for Applications (VBA) to overcome this 80 character limitation. The code uses the Continuum Integration Library to execute Dexterity sanScript code and also uses the Dynamic User Object Store (DUOS) to allow the Dexterity code to return its results to VBA. The DUOS is used because the DUOS table (SY90000) is easily visible to Dexterity as the SY_User_Object_Store table and to VBA as the DUOS objects.

The VBA code creates a Collection and creates 3 blank DUOS records to store 3 string properties. Then the code uses pass through Dexterity to call the RW_ConvertToWordsAndNumbers() function and then uses the RW_ParseString() function to split the returned result into 3 lines of 50 characters which are then stored into the SY_User_Object_Store table. When the code returns to VBA, it reads the DUOS records and displays the results to the report and finally removes the records in the DUOS.

NOTE: This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Example code for v8.0, v9.0 & v10.0 is found under the following link:
http://blogs.msdn.com/developingfordynamicsgp/attachment/8968837.ashx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/