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 CustomerDim cn Dim recsetdim 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 fieldend 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 cndim recsetDim CustomerDim EmailStmts
set cn = GetVariable("Connection")set recset = CreateObject("ADODB.Recordset")Customer = SourceFields("YOURSOURCE.YOURCUSTOMER")
'Get email stmts variableEmailStmts = 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, http://opensource.org/licenses/ms-pl.html.)
Posting from Mark Polino at DynamicAccounting.net
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
CurrentField= sSegment1 & "-" & sSegment2 & "-" & sSegment3 & "-" & sSegment4
'Close connection when finished
'Release the object
Set MyCon = Nothing
Never mind, discovered the Options for distributions was not on Default.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.