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

Dawn Langlie - Click for blog homepageI 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
SetVariable "Connection", MyCon


After Integration Script

'Close the Connection to the database:

set cn = GetVariable("Connection")


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 ''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,"'","''") & "')"
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,

Comments (3)
  1. David Musgrave says:

    Posting from Mark Polino at…/integration-manager-customer-address.html

  2. Anonymous says:

    Please Help!

    I'm trying to integrate, but keep receiving the following error: ERROR: Error Executing Script 'Distributions.Account Number' Line 12: – Permission denied

    This is my script I'm trying to use:


    sItemNumber = SourceFields("SOP Items.Location Code")

    'Open a connection to the SQL database

    Set MyCon = CreateObject("ADODB.Connection")

    MyCon.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID


    'Create a new recordset — that will hold returned data

    Set recset = CreateObject("ADODB.Recordset")

    'Create a SQL SELECT statement to retrieve the item's default

    'GL sales account number

    sSQL = "SELECT Rtrim(ACTNUMBR_1) 'Segment1', "

    sSQL = sSQL & "Rtrim(ACTNUMBR_2) 'Segment2', "

    sSQL = sSQL & "Rtrim(ACTNUMBR_3) 'Segment3', "

    sSQL = sSQL & "Rtrim(ACTNUMBR_4) 'Segment4' "

    sSQL = sSQL & "FROM IV00101, GL00100 WHERE "

    sSQL = sSQL & "IV00101.ITEMNMBR= '" & sItemNumber & "'"

    sSQL = sSQL & " AND IV00101." & sItemAccountFieldName

    sSQL = sSQL & " =GL00100.ACTINDX"

    'Open the recordset using the SQL statement

    Set recset = MyCon.Execute(sSQL)

    'Set the value of Segments 1, 2, 3 and 4





    'Concatenate the four segments and set the current field to the

    'resulting value

    CurrentField= sSegment1 & "-" & sSegment2 & "-" & sSegment3 & "-" & sSegment4

    'Close connection when finished

    Call MyCon.Close

    'Release the object

    Set MyCon = Nothing

  3. Anonymous says:

    Never mind, discovered the Options for distributions was not on Default.

Comments are closed.

Skip to main content