Today we are going to talk about ways of generating Custom incrementing fields in integration using SmartConnect. The methods will work for Dynamics GP integration, Dynamics CRM integration and also many other areas.
The first method we will talk about is simply using a built in fuction called a Custom Rolling Column. This can be added from the main mapping screen by clicking on Additional Columns-Columns-Custom Rolling Column.
In the figure below we can see that it is very simple to give the Column a name and then the next value to increment.
It is not necessary to select the Global Variable checkbox but if you want to use this value in a script later on that option is available.
This option works great for Master Records (Customers, Vendors, Employees, etc.) that may not have a function to increment values. There are other times when you may want to call a stored procedure or lookup a value in a table directly to get the next value for one of your fields. If that is the case the next example may be a better method for you.
In this method it will involve creating a Calculated Field in SmartConnect to return the value. A Calculated Field can be added by clicking on Additional Columns-Columns-Calculated. This allows a user access to write a quick VB.NET or C# script. In the figure below, the example is calling a Stored Procedure:
If you want to copy and modify the script below for you own use here is the code:
‘—————————————————————————————
If String.IsNullOrEmpty(GBL_E1_EXCEL_SOP_DOCTYPE ) Then
‘Define the SQL connection and open it
Dim myConn As New System.Data.SqlClient.SqlConnection( _SQLDEF_CONNECTIONSTRING )
Try
‘Declare the eOne stored procedure that will be called
Dim storedProc As String = “exec ” & _COMPINTERID & “.dbo.E1_SC_GetNextNumber 3,” & _DOCTYPE & “,'” & _SALESTYPE & “‘”
myConn.Open()
‘Declare the SQL command as the stored procedure
Dim myCmd As New SqlClient.SqlCommand(storedProc, myConn)
‘Define the SQL Reader and execute it
Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
‘Read the line
sqlReader.Read()
GBL_E1_EXCEL_SOP_DOCTYPE = sqlReader(“Number”).ToString()
Catch ex As Exception
‘If there is nothing to read, make blank and cause it to error out
GBL_E1_EXCEL_SOP_DOCTYPE = “-1”
‘Close the connection
myConn.Close()
End Try
end if
‘At this point, the global constant should be set above if it was blank, else it remains the same throughout the map
‘ Messagebox.Show(GBL_E1_EXCEL_SOP_DOCTYPE)
return GBL_E1_EXCEL_SOP_DOCTYPE
‘—————————————————————————————
I hope you have been inspired to try a few new ways in how you configure maps.
