Integration Manager Customer Address Update does not generate an error and does not update

This is a reposting of an article Dawn Langlie originally wrote on my Developing for Dynamics GP blog.

I worked on a Integration Manager 2010 case recently for a Customer update integration.  We ran across an issue when the customer you are updating has the Send Email Statement box marked in the Customer Maintenance Option window.  I also had the previous Customer’s address getting updated with the information from the Customer that had the Send Email Statements marked.

What I had to do was add the following script to unmark the Send Email Statement box before the document and after the document mark the Send Email Statements only for those customers that the box was previously marked.

In the code sections below, find the line of:

Customer = SourceFields(“YOURSOURCE.YOURCUSTOMER”)

Replace “YOURSOURCE.YOURCUSTOMER” with the appropriate data source and column name from your integration.

Before Integration Script

'Open the Connection to the database and store the connection in variable:

set recset = CreateObject("ADODB.Recordset")
set MyCon = CreateObject("ADODB.Connection")
MyCon.Connectionstring = "database=" & GPConnection.GPConnInterCompanyID
GPConnection.Open(MyCon)
SetVariable "Connection", MyCon

After Integration Script

'Close the Connection to the database:

set cn = GetVariable("Connection")
cn.Close

Before Document Script

'Update the RM00101 table to unmark the Send Email Statement box:

Dim Customer
Dim cn
Dim recset
dim EmailStmt

set cn = GetVariable("Connection")
Set recset = CreateObject("ADODB.Recordset")
Customer = SourceFields("YOURSOURCE.YOURCUSTOMER")

'Query RM00101 to check Send_Email_statements
'Use Replace() function to fix single quote characters in customer field

sqlcommand = "select * from RM00101 where (CUSTNMBR = '" & Replace(Customer,"'","''") & "')"

'executes the SQL statement
set recset = cn.Execute(sqlcommand)

'check if recordset is empty

If recset.EOF = False Then '...it's not empty
  EmailStmt = recset(88)  '88 is the column for this field
end if

'if not empty, then store current Send_Email_Statements value
SetVariable "EmailStatements", EmailStmt

'if checked then run update SQL to uncheck
if EmailStmt = 1 then
  'run your update statement to uncheck it
  updatecommand = "update RM00101 set Send_Email_Statements = 0 where (CUSTNMBR = '" & Replace(Customer,"'","''") & "')"
  recset = cn.Execute(updatecommand)
end if

After Document Script

'Update the RM00101 table to mark the Send Email Statement box for customers that had the box marked originally:

dim cn
dim recset
Dim Customer
Dim EmailStmts

set cn = GetVariable("Connection")
set recset = CreateObject("ADODB.Recordset")

Customer = SourceFields("YOURSOURCE.YOURCUSTOMER")

'Get email stmts variable
EmailStmts = GetVariable("EmailStatements")

if EmailStmts = 1 then
  'the checkbox was previously checked, and you unchecked it in the Before Doc script...
  'so now we need to reset it

  'run the update statment to recheck the box
  updatecommand = "update RM00101 set Send_Email_Statements = 1 where (CUSTNMBR = '" & Replace(Customer,"'","''") & "')"
  cn.Execute(updatecommand)
end if

If you encounter this issue and this script resolves it and you would like to be added to the bug, please log a support case which won’t be charged since you are reporting a bug.

Best Regards,
Dawn Langlie

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on https://www.winthropdc.com/blog.