Automatically generate and create batch IDs in Dynamics GP using VBA

By Steve Endow

This has probably been done a few thousand times by a few hundred people over the years, but I don't recall ever doing it before.

A customer wants to automatically generate batch IDs in Dynamics GP.  They don't want to have to type the batch ID or create batches when entering transactions.  They want to be able to open the Payables Transaction Entry window, have the Batch ID automatically generated and just starting entering a transaction.

Here is a link to a Word document with the instructions and the script.

           https://1drv.ms/w/s!Au567Fd0af9Tmh3tbpX2qrAXYfuY


Once the code is installed, if you open the Payables Transaction Entry window, a new batch is automatically created and the batch ID value is populated in the Batch ID field.


The customer didn't want to use the date, but wanted a sequential numeric batch ID that would be unique.  I used the username, plus the two digit year, plus the day of the year.  This ensures a unique batch for each day, and it's a way of using the date in the batch ID without using the full date.


Make sure to following the instructions in the Word doc and add a VBA Reference to Microsoft ActiveX Data Objects 2.8 Library in the VBA Editor.


Option Explicit

Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
  Set conn = UserInfoGet.CreateADOConnection
  conn.DefaultDatabase = UserInfoGet.IntercompanyID
  cmd.ActiveConnection = conn
  cmd.CommandType = 1  'adCmdText
End Sub

Private Sub Window_AfterOpen()

    Dim interID As String
    Dim userID As String
    Dim shortYear As String
    Dim dayNumber As String
    
    Dim currentDate As Date
    Dim sqlDate As String
    Dim batch As String
    
    Dim sql As String
    
    interID = UserInfoGet.IntercompanyID  'Get the database name (INTERID)
    userID = UCase(UserInfoGet.userID)  'Get the GP user ID
    
    currentDate = DateTime.Now
    sqlDate = Format(currentDate, "yyyy-MM-dd")

    shortYear = Right(DateTime.year(currentDate), 2) '2 digit year
    dayNumber = DatePart("y", currentDate) 'Get day number (out of 365 / 366)
    batch = userID & "-" & shortYear & dayNumber  'Ex:  jdoe-17134
    
    'Create the batch if it does not already exist
    sql = "EXEC " & interID & "..taCreateUpdateBatchHeaderRcd '" & batch & "', '', 4, '" & sqlDate & "', 'PM_Trxent', 0, 1, 0, '', 0, 0, 1, 0, 0, 0, 0, '" & userID & "', 1, 0, 0, 0, '1900-01-01', '', '', '', '', '', '', '', 0, ''"
    cmd.CommandText = sql
    cmd.Execute
    
    'MsgBox (sql)
    
    'Assign the new batch ID to the batch field on the window
    batchID.Value = batch
    
End Sub