Sunday, February 25, 2018

How to improve Dynamics GP with a little bit of VBA

By Steve Endow

I've had a few Dynamics GP customers that purchase software from me every few years, and a few of them have mailed checks to my old mailing address from 4 years ago. How can this happen?

Well, the Dynamics GP Payables Transaction Entry window does not display the vendor Remit To address, so verifying the vendor address is not an obvious natural step in the invoice entry process.  Yes, there is a link to open the Vendor Address Maintenance window, but what if internal controls prevents the user who enters vendor invoices from editing vendor addresses?  The user would need to go through a separate process to verify the vendor address...for every invoice.  Not ideal.

How can VBA help?

In just a few minutes, VBA can be added to the Payables Transaction Entry window to check if the vendor has not had a transaction in over 60 days, prompt the user to verify the vendor address, and even open the Vendor Inquiry window to review the current Remit To address in Dynamics GP.

It's really easy!

Here's a video discussing the background and walking through the entire process of adding the VBA to Dynamics GP.

First, within Dynamics GP, you add the desired windows to Visual Basic by clicking on Tools -> Customize -> Add Current Window to Visual Basic.

After the window is added, I click on Add Fields to Visual Basic and then click on the Vendor ID field.

Since I am assuming that the user entering invoices will not have access to edit vendors or vendor addresses, I'm going to add the Vendor Inquiry window to Visual Basic and add 3 fields on the Inquiry window to VB:  Vendor ID, Address ID, and the Address ID Next button.

Once I have those windows and fields added to Visual Basic, I press CTRL+F11 to open the Dynamics GP Visual Basic Editor.  If you don't have access to the VB Editor, you may not be licensed to use it, or you may not have permissions--in which case, talk with your GP administrator or GP partner.

In the VB Editor window, I'll select the PayablesTransactionEntry window on the left, then select the VendorID field and the AfterUserChanged event.

I wrote the simple VBA code below to demonstrate how you can quickly and easily add some VBA to add some valuable functionality to Dynamics GP to save users time and improve data entry.

The code finds the most document date for any vendor transaction in Dynamics GP, and if that date is over 60 days ago, it opens the Vendor Inquiry window and displays the vendor Remit To address for the user to review and verify.

Private Sub VendorID_AfterUserChanged()
    Dim strVendorID As String
    Dim strSQL As String
    Dim dtLastDocDate As Date
    strVendorID = VendorID.Value
    'Find the most recent document date for the vendor
    strSQL = "SELECT COALESCE(MAX(DOCDATE), '1900-01-01') AS DOCDATE FROM PM00400 WHERE VENDORID = '" & strVendorID & "'"
    Set oConn = UserInfoGet.CreateADOConnection
    oConn.DefaultDatabase = UserInfoGet.IntercompanyID
    Set rsResult = oConn.Execute(strSQL)
    strLastDocDate = Trim(rsResult.Fields("DOCDATE").Value)
    'Get the Remit To Address ID for the vendor
    strSQL = "SELECT VADCDTRO FROM PM00200 WHERE VENDORID = '" & strVendorID & "'"
    Set rsResult = oConn.Execute(strSQL)
    strRemitID = Trim(rsResult.Fields("VADCDTRO").Value)
    dtLastDocDate = CDate(strLastDocDate)
    'If Doc Date is 1/1/1900, vendor has no transactions
    If dtLastDocDate = CDate("1900-01-01") Then
        Exit Sub
        intDays = DateDiff("d", dtLastDocDate, DateTime.Date)
        'If the last doc date is > X days ago, display a dialog
        If intDays > 60 Then
            msgResult = MsgBox("This vendor has not had a transaction since " & strLastDocDate & " (" & intDays & " days ago)." & vbNewLine & vbNewLine & "Please review the current vendor Remit To address and compare to the invoice address", vbOKOnly, "Verify Vendor Address")
            VendorInquiry.VendorID.Value = strVendorID
            While VendorInquiry.AddressID.Value <> strRemitID
                VendorInquiry.NextButtonWindowArea.Value = 1
        End If
    End If

End Sub

In just a few minutes, you can have this customization running in Dynamics GP without any additional development tools.

If you have more complex requirements, you can easily add more advanced functionality using VBA.  If you prefer using a separate development tool, you could also develop this customization using .NET or Dexterity, but the appeal of VBA is its simplicity and ease of use.

So if you have some small problem or additional business requirement that you'd like to handle in Dynamics GP, VBA might come in handy.

You can also find him on Twitter, YouTube, and Google+

No comments: