IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 2

Part 2 of 2 - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.

---

In the previous installment, I explained the technique that I otherwise use to relate source systems records with imported records in Microsoft Dynamics GP for which a Use Default field rule has been set for the key field. This article demonstrates the key event scripts needed to obtain the record.

Before Integration event script

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

' Secure a connection against the company database we will be running
' the integration against.

Const adUseClient = 3

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

GPConnection.Open(oCn)

' Make sure the connection is valid
If (oCn.Status = 1) Then
   ' Setup global connection variables
   SetVariable "gblConn", oCn   
   SetVariable "gblInterID", GPConnection.GPConnIntercompanyID
Else
 CancelIntegration
End If

The Before Integration event script will allow us to secure a connection against the company database we will be running our integration against. By opening the connection in this event script, we will avoid having to open a connection for each transaction being integrated, further on, creating any loss of performance in the process. It is important to highlight that we need to save the successful connection to a global variable, to be able to use it in other event scripts. In this case, we will assign the oCn connection variable to a global variable, gblConn, using the SetVariable statement.

Once Integration Manager has integrated the document, we will use the After Document event script to retrieve the record integrated. At this stage, we are assuming that the mapping of source fields to destination fields provisions a user-defined field or description field for the source key field. In the past, I have also used note fields to store these key fields when it has not been feasible to use a standard Microsoft Dynamics GP field.

Note: the After Document event script will only execute upon success of the document being integrated. If Integration Manager is unsuccessful integrating the document, the Document Error script will execute instead. This event can be used to report failure to the source system, which may facilitate new attempts to integrate from the source system by reporting different event statuses.

After Document event script

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

' Prepare the SQL statement and retrieve the assigned Sales Transaction number
Set oCmd = CreateObject("ADODB.Command")
With oCmd
 .ActiveConnection = GetVariable("gblConn")
 .CommandType = adCmdText

 .CommandText = "SELECT SOPNUMBE FROM SOP10106 WHERE USERDEF05 = '" & SourceFields("mySourceQry.KeyField") & "'"
  Set oRst = .Execute

 If Not oRst.EOF Then
                SopNumber = oRst!SOPNUMBE
 End If
 oRst.Close
End With  

' From here on you can open a connection to your source system and update the 
' some status flag and the column provisioned to track the GP document number

I hope you find this technique useful. Of course, this is a technique I have been using over the years. I would like to find out from you what methods you have used to accomplish the same.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/