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.
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.
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
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/
 |
| 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/
