web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / How to show cheque amo...
Microsoft Dynamics GP (Archived)

How to show cheque amount in words for cents

(0) ShareShare
ReportReport
Posted on by Wong Henry 1,070

I would like to know how can i show the cheque amount for cents to be shown in words instead of numbers.

I am using the report writer function RW_ConvertToWordsAndNumbers(). However, the results is showing me,

Example:- 120.50 shown as "one hundred twenty dollars and 50 cents"

I want it to show as "one hundred twentry dollars and fifty cents" in report writer.

Appreciate if anyone could tell me how to get the above result using report writer.

Thank you.

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    I have the same issue, any resolutions?

  • Verified answer
    Congruent Dynamics Profile Picture
    Congruent Dynamics 735 on at

    We can do this by creating new calculated fields. For more details, please have a look on the below link

    congruentdynamics.blogspot.in/.../converting-dollars-and-cents-to-words.html

  • Daniel Duodu-Poku Profile Picture
    Daniel Duodu-Poku 5 on at

    Sample below provides a solution for converting dollars and cents currency value into words using VBA/Modifier to overcome the 80 character limit on string calculated fields. The solution combines two ideas owned by David Musgrave (Australia) Microsoft Dynamics GP Asia Pacific support team Escalation Engineer.

    <Component Name="Check With Stub on Top" ProductId="0" Object="VBAReport" >

    VBAReport "Check With Stub on Top"

    {

    Code "Option Explicit

    Const MaxLines As Integer = 3

    Const MaxChars As Integer = 50 ' Cannot be longer than 80

    Private Sub Report_BeforeAF(ByVal Level As Integer, SuppressBand As Boolean)

       Dim PrintCollection As DUOSObjects

       Dim PrintObject As DUOSObject

       'Dim CompilerApp As New Dynamics.Application

       Dim CompilerApp As Object

       Dim CompilerMessage As String

       Dim CompilerError As Integer

       Dim Commands As String

       ' Create link without having reference marked

       Set CompilerApp = CreateObject(\"Dynamics.Application\")

       Select Case Level

           Case 1

               Set PrintCollection = DUOSObjectsGet(\"Cheque Printing\")

               Set PrintObject = PrintCollection.Item(Trim(CStr(DocumentNumberLASTF1)))

               PrintObject.Properties(\"String 1\") = \"\"

               PrintObject.Properties(\"String 2\") = \"\"

               PrintObject.Properties(\"String 3\") = \"\"

               Commands = \"\"

               Commands = Commands & \"local currency l_dollarpart; \" & vbCrLf

               Commands = Commands & \"local currency l_centpart; \" & vbCrLf

               Commands = Commands & \"local string l_dollarwords; \" & vbCrLf

               Commands = Commands & \"local string l_centwords; \" & vbCrLf

               Commands = Commands & \"local string l_dollarwordsonly; \" & vbCrLf

               Commands = Commands & \"local integer l_centwordslen; \" & vbCrLf

               Commands = Commands & \"local string l_centwordsonly; \" & vbCrLf

               Commands = Commands & \"local string l_centwordstrim; \" & vbCrLf

               Commands = Commands & \"local integer l_rightwordmark; \" & vbCrLf

               Commands = Commands & \"local integer l_rightwordstart; \" & vbCrLf

               Commands = Commands & \"local string l_rightword; \" & vbCrLf

               Commands = Commands & \"local string l_getresult; \" & vbCrLf

               Commands = Commands & \"local string l_strings[3]; \" & vbCrLf

               Commands = Commands & \"local integer i; \" & vbCrLf

               Commands = Commands & \"l_dollarpart = RW_Truncate(\" & CCur(CheckTotalLASTF1) & \",0,0); \" & vbCrLf

               Commands = Commands & \"l_centpart = (\" & CCur(CheckTotalLASTF1) & \" -  l_dollarpart)  *  100.00000 ; \" & vbCrLf

               Commands = Commands & \"l_dollarwords = RW_ConvertToWordsAndNumbers(((l_dollarpart)), \"\"\" & Trim(CStr(CurrencyIDLASTF1)) & \"\"\",0); \" & vbCrLf

               Commands = Commands & \"l_centwords = RW_ConvertToWordsAndNumbers(((l_centpart)), \"\"_\"\",0); \" & vbCrLf

               Commands = Commands & \"l_centwordslen = RW_Length((l_centwords))- 4; \" & vbCrLf

               Commands = Commands & \"l_centwordsonly = RW_Left((l_centwords), l_centwordslen); \" & vbCrLf

               Commands = Commands & \"l_centwordstrim = RW_Trim((l_centwordsonly), 3,\"\" \"\"); \" & vbCrLf

               Commands = Commands & \"l_rightwordmark = RW_Pos((l_dollarwords) ,\"\"00\"\",1) - 1; \" & vbCrLf

               Commands = Commands & \"l_rightwordstart = ((l_rightwordmark)) + 3; \" & vbCrLf

               Commands = Commands & \"l_rightword = RW_Substring((l_dollarwords) ,(l_rightwordstart),20); \" & vbCrLf

               Commands = Commands & \"l_dollarwordsonly = RW_Left((l_dollarwords),(l_rightwordmark)); \" & vbCrLf

               Commands = Commands & \"l_getresult = (l_dollarwordsonly) + (l_centwordstrim) + (l_rightword) ; \" & vbCrLf

               Commands = Commands & \"for i = 1 to \" & Str(MaxLines) & \" do \" & vbCrLf

               Commands = Commands & \"  l_strings[i] = RW_ParseString(l_getresult, \" & Str(MaxChars) & \", i); \" & vbCrLf

               Commands = Commands & \"  clear table SY_User_Object_Store; \" & vbCrLf

               Commands = Commands & \"  'ObjectType' of table SY_User_Object_Store = \"\"Cheque Printing\"\"; \" & vbCrLf

               Commands = Commands & \"  'ObjectID' of table SY_User_Object_Store = \"\"\" & Trim(CStr(DocumentNumberLASTF1)) & \"\"\"; \" & vbCrLf

               Commands = Commands & \"  'PropertyName' of table SY_User_Object_Store = \"\"String \"\" + str(i); \" & vbCrLf

               Commands = Commands & \"  change table SY_User_Object_Store; \" & vbCrLf

               Commands = Commands & \"  if err() <> MISSING then \" & vbCrLf

               Commands = Commands & \"    'PropertyValue' of table SY_User_Object_Store = l_strings[i]; \" & vbCrLf

               Commands = Commands & \"    save table SY_User_Object_Store; \" & vbCrLf

               Commands = Commands & \"    check error; \" & vbCrLf

               Commands = Commands & \"  else \" & vbCrLf

               Commands = Commands & \"    release table SY_User_Object_Store; \" & vbCrLf

               Commands = Commands & \"  end if; \" & vbCrLf

               Commands = Commands & \"end for; \" & vbCrLf

               ' Execute SanScript

               CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)

               If CompilerError <> 0 Then

                   MsgBox CompilerMessage

               Else

                   Set PrintCollection = DUOSObjectsGet(\"Cheque Printing\")

                   Set PrintObject = PrintCollection.Item(Trim(CStr(DocumentNumberLASTF1)))

                   String1LASTF1 = CStr(PrintObject.Properties(\"String 1\"))

                   String2LASTF1 = CStr(PrintObject.Properties(\"String 2\"))

                   String3LASTF1 = CStr(PrintObject.Properties(\"String 3\"))

               End If

               Set PrintCollection = DUOSObjectsGet(\"Cheque Printing\")

               PrintCollection.Remove (Trim(CStr(DocumentNumberLASTF1)))

           Case Else

       End Select

    End Sub

    "

    EventMode "1"

    Fields

    {

    ReportField "CheckTotalLASTF1"

    {

    AccumulatorType "11"

    ArrayIndex "0"

    BandLevel "1"

    BandType "4"

    FieldName "Check Total"

    TableName "PM_Payment_WORK"

    }

    ReportField "CurrencyIDLASTF1"

    {

    AccumulatorType "11"

    ArrayIndex "0"

    BandLevel "1"

    BandType "4"

    FieldName "Currency ID"

    TableName "PM_Payment_WORK"

    }

    ReportField "DocumentNumberLASTF1"

    {

    AccumulatorType "11"

    ArrayIndex "0"

    BandLevel "1"

    BandType "4"

    FieldName "Document Number"

    TableName "PM_Payment_WORK"

    }

    ReportField "String1LASTF1"

    {

    AccumulatorType "11"

    ArrayIndex "0"

    BandLevel "1"

    BandType "4"

    FieldName "String 1"

    TableName ""

    }

    ReportField "String2LASTF1"

    {

    AccumulatorType "11"

    ArrayIndex "0"

    BandLevel "1"

    BandType "4"

    FieldName "String 2"

    TableName ""

    }

    ReportField "String3LASTF1"

    {

    AccumulatorType "11"

    ArrayIndex "0"

    BandLevel "1"

    BandType "4"

    FieldName "String 3"

    TableName ""

    }

    }

    Name "CheckWithStubonTop"

    }

    </Component>

    Someone should find this useful. skype id is danielduodupoku

  • spena Profile Picture
    spena 1,501 on at

    Hi,

    Is there a way someone could provide screenshots of how to setup the calculated field in report writer?  I can't seem to get my calculated field to run without error.

    Thanks!

    Steve

  • Suggested answer
    soma Profile Picture
    soma 24,410 on at

    Have a look on below screenshot.

    1. In tool box, Choose calculated fields and click new button.

    2. Calculated Field Definition window, enter calculated field name, result type(String, Integer, Currency,.etc), Expression Type as Calculated and add the condition then click OK.

    3. Drag the calculated filed into report layout and save the report then print. 

    Hope this helps!!!

  • spena Profile Picture
    spena 1,501 on at

    Hi There,

    I was actually looking for a screenshot of the complete calculated expression with the actual fields.  I need to see exactly what function is used and what fields from the appropriate PM table should be used.  This is for the Check Stub on Top Bottom - TEXT.

    Thanks!

    Steve

  • Suggested answer
    soma Profile Picture
    soma 24,410 on at

    This is difficult to show more calculated fields.

    Create the below calculated fields in Report Writer and drag the final calculated field (12th) to your report.

    1. amount = Pass your amount here
    2. dollars = RW_Truncate (amount, 0, 0)
    3. cents = (amount - dollars) * 100.0
    4. dollarstr = RW_ConvertToWordsAndNumbers (dollars, 0, 0)
    5. centposition = RW_Pos (dollarstr, "00", 1)
    6. amountinwrd1= RW_Left (dollarstr, centposition - 1)
    7. amountinwrd2 = RW_Substring (dollarstr, centposition  + 2, 20)
    8. centinwrd1 = RW_ConvertToWordsAndNumbers (cents ,"_", 0)
    9. length = RW_Length (centinwrd1)
    10. centinwrd2  = RW_Left (centinwrd1, length - 4)
    11. amountinwrd3 = RW_Trim (centinwrd2 , 3, " ")
    12. resultinwords = amountinwrd1+ amountinwrd3+amountinwrd2

    Hope this helps!!!

  • Romryan Profile Picture
    Romryan on at

    hi Soma,

    I have followed the procedure but I have an issue with calculated field 6,7 and 10.I think i.e. centposition - 1 in calculated field 6.

    Also on all calculated fields,could you indicate the Result Type i.e. string,currency,integer etc.

    Again when do we use #?.

    Thanks

    RomRyan

  • Suggested answer
    soma Profile Picture
    soma 24,410 on at

    RomRyan, I think no error will occurs for those(6,7 &10) calculated fields. The issue may be related to Result Type. Please have a look on the Result Types as below.

    1. amount = Pass your amount here ---(currency)
    2. dollars = RW_Truncate (amount, 0, 0) ---(currency)
    3. cents = (amount - dollars) * 100.0 ---(currency)
    4. dollarstr = RW_ConvertToWordsAndNumbers (dollars, 0, 0) ---(string)
    5. centposition = RW_Pos (dollarstr, "00", 1) ---(integer)
    6. amountinwrd1= RW_Left (dollarstr, centposition - 1) ---(string)
    7. amountinwrd2 = RW_Substring (dollarstr, centposition  + 2, 20) ---(string)
    8. centinwrd1 = RW_ConvertToWordsAndNumbers (cents ,"_", 0) ---(string)
    9. length = RW_Length (centinwrd1) ---(integer)
    10. centinwrd2  = RW_Left (centinwrd1, length - 4) ---(string)
    11. amountinwrd3 = RW_Trim (centinwrd2 , 3, " ") ---(string)
    12. resultinwords = amountinwrd1+ amountinwrd3+amountinwrd2 ---(string)

    Please let me know if your need any help regarding the same.

    Hope this helps!!!

  • Community Member Profile Picture
    Community Member on at

    Hi Soma,

    I guess, In the Step 7 (centposition  + 2)  &  Step 10(length - 4)  cannot assign directly to amountinwrd2 & centinwrd2. i face the error initially. i assign (centposition  + 2) to newcentposition   and apply to your mentioned step as

    amountinwrd2 = RW_Substring (dollarstr, newcentposition, 20)  

    now amountinwrd2 works.  similarly for (length - 4) to new calculated field.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
mtabor Profile Picture

mtabor 1

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans