Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: GP SOP Update Integrations


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.

 

SOP Update Maps

 

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.