This week will look at one of the ways SmartConnect can improve on eConnect capabilities. According to the Microsoft Dynamics GP eConnect documentation, when updating an existing SOP document and/or SOP line items, we must include any existing values, except for a few fields listed in the documentation, or they will be recalculated. The difficulty with this is those values may not be a part of the source date. For example, we may be pulling the data from a CRM system that doesn’t store everything from a Dynamics GP SOP document. If we are using a bulk data source, we could use the Multi-Data Source option from SmartConnect and combine the CRM and GP data into a source. For this article we will discuss the scenario where we are using a Change or Real-Time data source where multi-source is no longer an option.
Initial Setup
For this article, we will assume an integration from CRM to SOP is already set up to work for creating new documents in Dynamics GP. We want to add the functionality of updating the SOP document based on updates that happen in CRM.
With a Change or Real-Time data source we can’t include destination data at the source level, so we need to do the lookups for the existing SOP data in our destination. We will use some .NET Scripting to pull the existing SOP data which we can get from this article. We need to have SmartConnect pull data for each line and header, so we will need to add scripting in both nodes. We will need to create a Global Variable for every field we will be retrieving from SQL.
Pull Existing Data from SQL
We need the script to run on every record, so I am placing it in the Restriction section of the mapping window. The script will pull each field for that record in SQL and assign it to a Global Variable. Here is a simplified example of the script with only two fields. In practice there will likely be many more fields.
'Create the SQL connection and open it
Dim conString As New String(_SQLDEF_TWO)
Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
myConn.Open()
'to ensure it’s using the runtime company or use a specific database name if needed
myConn.ChangeDatabase(GlobalCompanyID)
'Declare the SQL command using the procedure name in the first parameter
Dim SQLCode As String = "SELECT a.INTEGRATIONID, a.INTEGRATIONSOURCE FROM SOP10200 As a Where a.SOPNUMBE = '" + _SALESORDER_NAME + "' and a.ITEMNMBR = '" + _PRODUCT_PRODUCTNUMBER + "' "
'Declare the SQL command as the query string
Dim myCmd As New System.Data.SqlClient.SqlCommand(SQLCode, myConn)
myCmd.CommandType = System.Data.CommandType.Text
Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
'Check if record exists and set update existing flag
If sqlReader.HasRows then
GBL_LINE_EXISTS = 1
While sqlReader.Read()
GBL_INTEGRATIONID = sqlReader.GetValue(0)
GBL_INTEGRATIONSOURCE = sqlReader.GetValue(1)
End While
else
GBL_LINE_EXISTS = 0
end if
sqlReader.Close()
myConn.close()
return true
Mapping the Values
The next step is to create a Calculated Column for each field that we pulled from SQL. The scripts on these columns are very simple.
return GBL_INTEGRATIONID
I can then map the Calculated Column in the mapping window and it will populate that field with the existing value in the GP database.
Conclusion
Even though we have shown how to handle the scenario of updating SOP documents from CRM, the same steps can be completed for any data source such as a file or Salesforce.
Below is a link to download this example which includes a calculated column for every field you may need in the future. You don’t need to use all the calculated columns but having them available will save a lot of time when building the integration. The file includes a CRM change data source and a TEXT file data source in case you do not have the CRM connector configured. You will need SmartConnect version 20.18.1.14 or greater to import these maps.