You’re offline. This is a read only version of the page.
85
I'm trying to import with Integration Manager and my file doesn't have a Vendor ID number. We want to search for the last Temporary VendorID and increment by 1. If there isnt' a Temporary VendorID, create one as CR00001 and then start incrementing.
Two things to note. I haven't added the temporary status command so that my search only looks for Temporary Vendors (dont' know how), and there is one CRxxxxxxxxxxxx Vendor ID (not temporary) already in Fabrikam.
Thanks - Bob
I'm running this script and getting an error on Line 22.
ERROR:
Opening source query... Establishing source record count... Beginning integration... DOC 1 ERROR: Error Executing Script 'Vendor ID' Line 22: - [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'CR'.
DOC 2 ERROR: .....DOC 51 ERROR: repeat the same error as DOC 1
SCRIPT: (the call pConnection to the database works. I changed it for sharing purposes)
' Added by the IM Script Library ' Category: Microsoft Dynamics GP Scripts ' Script Type: Script ' Create a new connection Set pConnection = CreateObject("ADODB.Connection") 'Open the connection to the database Call pConnection.Open("db", "login", "password") 'Create a new recordset -- that will hold returned data Set pRecordset = CreateObject("ADODB.Recordset") 'Build a string containing your SQL Select Statement 'In this example, the SQL "Max" function is used, and the 'resulting column is given an alias of 'VendorID'. sSQL = "SELECT Max(VENDORID) VendorID FROM Two.dbo.PM00200 WHERE Left(VENDORID, 2) = CR" 'Open the recordset with the SQL SELECT statement. 'The '3' and '1' arguments in the Open method indicate a static, 'read-only cursor Call pRecordset.Open (sSQL, pConnection, 3, 1) 'If there is no existing Vendor ID using this 2-character CR prefix, 'then set the Vendor ID to "CR" and numeric suffix to "00001". 'Otherwise, take the rightmost 5 characters of the highest existing 'CR Vendor ID, and add 1 to it. If IsNull (pRecordset ("VendorID")) then Left(VendorID, 2) = "CR" Right(VendorID, 5) = "00001" Else sSuffix = Right(Trim(pRecordset ("VendorID")), 5) sSuffix = sSuffix + 1 End If 'Close recordset when finished Call pRecordset.Close 'Close connection when finished Call pConnection.Close
*This post is locked for comments
I have the same question (0)
Share
Report
All responses (
Answers (
