|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
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!
Mariano Gomez, MVP