Button Script to Add Comment Line to GD_Lines Table in SO Invoice Data Entry

SOLVED

From the Payment tab in SO Invoice Data Entry I have added a button with the caption "CC Receipt". Upon a user clicking this button I would like a comment (Item code /C) added to the next line number with details from the credit card.

First I actually attempted to do this through Crystal Reports, but I was running into issues with tables breaking things depending on if the invoice was posted (SO_InvoiceHistory) or not (SO_InvoiceDetails) as for whatever reason CC transaction details wasn't included in the SO_InvoiceWrk table.

So then I attempted to place the text for the comment into the user's clipboard, but I was unsuccessful in creating the required objects on the Sage server that would work with VBScript. Which brings me here:

How can I add a comment item type to the next line of the GD_Lines table in the Lines tab (keep in mind the user will be in the Payment tab upon clicking the button script)? Here's what I have so far:

Dim CardNo:    CardNo = Right(GetProp("LAST4UNENCRYPTEDCREDITCARDNOS", "Value$"), 4)
Dim CardType:  CardType = GetProp("ML_CARDTYPE", "Value$")

Dim isOkayToProceed: isOkayToProceed = True

Select Case CardType
Case "Visa", "MasterCard"
    CardNo = "XXXX-XXXX-XXXX-" & CardNo
Case "AMEX"
    CardNo = "XXXX-XXXXXX-X" & CardNo
Case Else
    Msg "This card type (" & CardType & ") is not set up!"
    isOkayToProceed = False
End Select

If isOkayToProceed Then

    Select Case CardType
    Case "Visa"
        CardType = "VISA CC #            "
    Case "AMEX"
        CardType = "AMEX CC #            "
    Case "MasterCard"
        CardType = "MSTR CC #            "
    Case "DISC"
        CardType = "Discover CC #        "
    Case Else
        CardType = vbNullString
    End Select
    
    Dim PmtAmt:    PmtAmt = CCur(GetProp("ML_CC_BALANCE", "Value"))
    Dim AuthNo:    AuthNo = GetProp("CREDITCARDAUTHORIZATIONNO", "Value$")
    Dim AuthAmt:   AuthAmt = GetProp("TRANSACTIONAMT", "Value")
    Dim AuthDate:  AuthDate = CDate(GetProp("AUTHORIZATIONDATE", "Value"))
    Dim AuthTime:  AuthTime = GetProp("AUTHORIZATIONTIME", "Value")
    Dim TransID:   TransID = GetProp("CREDITCARDTRANSACTIONID", "Value$")
    
    Dim CommentText
    CommentText = _
    "************************* CREDIT CARD RECEIPT *************************" & vbNewLine & _
    "    " & CardType        & vbTab & CardNo & vbNewLine & _
    "    Charge Amount     " & vbTab & AuthAmt & vbNewLine & _
    "    Transaction ID #  " & vbTab & TransID & vbNewLine & _
    "    Authorization #   " & vbTab & AuthNo & vbNewLine & _
    "*********************************************************************************"

    'GD_LINES is the name of the main table
    oUIObj.InvokeChange "GD_Lines", "..." ?????
    
End If

Private Sub Msg(t)
    oSession.AsObject(oSession.UI).MessageBox "", CStr(t)
End Sub

Private Function GetProp(ctlName, pName)

    Dim retVal
    oUIObj.GetControlProperty ctlName, pName, retVal
    GetProp = retVal
    
End Function

Parents
  • +1
    verified answer

    Why are you using UI methods instead of business objects? 

    Example of how to get to the payment record here:

    https://www.sagecity.com/us/sage100_erp/f/sage-100-business-object-interface/159799/osession-asobject-obusobj-paymentobj-inconsistent-results-in-so-scripts

    Then

    Set oLines = oSession.AsObject(oBusObj.Lines)

    AddLine()

    SetValue() x at least 2

    Write()

    Check your retVal on the Write and when non-zero do this (to refresh the grid).

    oScript.LinesAdded = 1

  • 0 in reply to Kevin M

    Appreciate the response. However, I am receiving a syntax error:

    I hard-coded the payment variables for testing so it wouldn't be an issue with that, here's the code:

    And I placed those 'Msg' for step-by-step validation, which shows:

            

    Since the "Done" MsgBox appeared, this happened after the script was finished (and I still didn't see the comment line in Invoice Data Entry prior to clicking "End" in the error, so oScript.LinesAdded = 1 didn't appear to refresh the grid or it wasn't ever added).

  • 0 in reply to SoonerFan21

    Where are you running the button from?  It needs to be from somewhere that oBusObj is the header record (not the payment object), so try your button on the Header or Totals tab.

    The AddLine return = 2 is odd... I'm not sure what that means.

    And your syntax for Msg is highly unusual.  Typically you'd do a retVal = for each command, then handle whatever messaging you want after that.  You might find DebugPrint to be more convenient for testing...

  • 0 in reply to Kevin M

    I moved the button from the Lines tab to the Header tab. I still got the "done" message box and no error code.. at least until I switched to the lines tab. But at least this time I did see the line added - it's just that I still get this error.

    The error went away when I completely removed the "LinesAdded = 1` and the line is still showing when I switch back to that tab, so I am not sure how important this is but it appears to be working without it - but I will wait to save the changes here so I don't start breaking data tables or something in case it's necessary.

    Edit: Tried it on the test server and everything looks okay. Also moved the button to the Payment tab and it worked fine as well, which is where it would have likely been used after verifying the authorization from the sales order hadn't expired.

    Here's the final script:

    Dim CardNo:		CardNo = Right(GetProp("LAST4UNENCRYPTEDCREDITCARDNOS", "Value$"), 4)
    Dim CardType:	CardType = GetProp("ML_CARDTYPE", "Value$")
    Dim PmtAmt: 	PmtAmt = CCur(GetProp("ML_CC_BALANCE", "Value"))
    Dim AuthNo:		AuthNo = GetProp("CREDITCARDAUTHORIZATIONNO", "Value$")
    Dim AuthAmt: 	AuthAmt = GetProp("TRANSACTIONAMT", "Value")
    Dim TransID: 	TransID = GetProp("CREDITCARDTRANSACTIONID", "Value$")
        
    Select Case UCase(CardType)
    Case "VISA"
        CardType = "Visa Card #           "
        CardNo = "XXXX-XXXX-XXXX-" & CardNo
    Case "MASTERCARD"
        CardType = "MasterCard #          "
        CardNo = "XXXX-XXXX-XXXX-" & CardNo
    Case "AMEX"
        CardType = "American Express # "
        CardNo = "XXXX-XXXXXX-X" & CardNo
    Case Else
        Msg "Card Type (" & CardType & ") is not set up!"
        CardType = "CREDIT CARD #         "
    End Select
        
    Dim CommentText
    CommentText = _
    "************************* CREDIT CARD RECEIPT *************************" & vbNewLine & _
    "    " & CardType 			& vbTab & CardNo & vbNewLine & _
    "    Charge Amount     " 	& vbTab & FormatCurrency(AuthAmt, 2) & vbNewLine & _
    "    Transaction ID #  " 	& vbTab & TransID & vbNewLine & _
    "    Authorization #      " & vbTab & AuthNo & vbNewLine & _
    "*********************************************************************************"
    
    Dim oLines: Set oLines = oSession.AsObject(oBusObj.Lines)
    oLines.AddLine
    oLines.SetValue "ItemCode$", "/C"
    oLines.SetValue "CommentText$", CommentText
    oLines.Write
    
    Private Sub Msg(t)
    	oSession.AsObject(oSession.UI).MessageBox "", CStr(t)
    End Sub
    
    Private Function GetProp(ctlName, pName)
    
    	Dim retVal
    	oUIObj.GetControlProperty ctlName, pName, retVal
    	GetProp = retVal
    	
    End Function

    Thanks for the help!

    Edit #2: Changed client-side 'MsgBox' to 'Msg'

  • 0 in reply to SoonerFan21

    If you run the script from somewhere other than the Lines tab, the LinesAdded function is not needed.  It's for refreshing the grid display when you add lines when already on the Lines tab (usually from a detail event trigger).

Reply Children
No Data