Rejecting duplicate checks during Bank Transactions import with Integration Manager

One of the interesting things about checkbooks setup in Microsoft Dynamics GP is that you have the ability to prevent duplicate checks from being issued in the Payables Management module, yet those same controls do not apply if you are entering checks in the Bank Transaction Entry window.

Checkbook Maintenance window - Payables Options

This can certainly be an issue if those check numbers happen to be integrated from a line of business application as a bank transaction in the Financial module.

Bank Transaction Entry window

This was certainly the case for a partner who was integrating a number of transactions from a line of business application into Microsoft Dynamics GP and required to implement a control to prevent duplicate check numbers from being integrated.

Integration Manager scripting capabilities proved to be very helpful here. By adding some VBScript to the Before Document event script, we can check to see if the check number being integrated exists in the CM Transactions table (dbo.CM20100) prior to allow the integration to commit the record in Microsoft Dynamics GP.

Before Document script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

Const adUseClient     = 3
Const adCmdStoredProc = 4
Const adCmdText       = 1

Dim oCn, oCmd, oRs

Set oCn = CreateObject("ADODB.Connection")
With oCn
  .ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
  .CursorLocation = adUseClient
End With

GPConnection.Open(oCn)

' Prepare the SQL statement and retrieve the next voucher number

Set oCmd = CreateObject("ADODB.Command")
With oCmd 
 .ActiveConnection = oCn
 .CommandType = adCmdText
 .CommandText = "SELECT * FROM CM20100 WHERE CMTRXNUM = '" & _ 
    CStr(SourceFields("sourceQry.CheckNumber")) & _ 
    "' AND CHEKBKID = 'FIRST BANK';"
 Set oRs = .Execute
End With

If Not oRs.EOF Then
' This is a duplicate check
   CancelDocument "Duplicate Check Number for checkbook FIRST BANK: " & _ 
     CStr(SourceFields("sourceQry.CheckNumber"))
End If
oRs.Close
oCn.Close

Set oCmd = Nothing
Set oCn = Nothing

A few things to note:

The CancelDocument function is used to reject the record if it's found in the database. We can also add a simple message to be written to the integration log file describing the check number found to be a duplicate.

You can optimize this code by opening a connection to the database and storing the connection in a global variable in the Before Integration event script, rather than having to open and close the connection several times as I do here. Likewise, you can close the connection in the After Integration script.

The bottom line, nonetheless, is to show a simple technique for record control and rules implementation that help the partner and the customer overcome the issue they were having.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/