You’re offline. This is a read only version of the page.
81
I am trying to create an integration that will load expense transactions into GP as a j/e. I have the basic integration working, but now need to add a before script to validate that the account in the source file exists in GP, if not, throw an error. I was given a very vague sample of code, but am having a hard time deciphering it. I don't know VBS. Can someone please share what they can to get me going in the right direction? We have a SQL db. Any samples and information would be appreciated. Can you break down the pieces I need, such as, create new connection, open db, etc.? Thank you in advance.
*This post is locked for comments
I have the same question (0)This might assist as a starting point, you can add additional code.
Set MyCon = CreateObject("ADODB.Connection")
'Open the connection to the database - TWO is my database
MyCon.ConnectionString = "TWO=" & GPConnection.GPConnInterCompanyID
GPConnection.Open(MyCon)
'Build a string containing your SQL Select Statement
sSQL = "SELECT ACTNUMST from GL00105 where ACTNUMST = '"
sSQL = sSQL & SourceFields("Accounts.Account Number") & "'"
'If you're having trouble getting your SQL statement correct,
'then un-comment the following "MsgBox" line by removing the
'single-quote character. This will generate a message box
'containing your SQL statement every time the script is run.
'After you've corrected any errors, add the single-quote back
'to the beginning of the line.
'MsgBox ("The SQL statement is: " & sSQL)
'Open the recordset
Set recset = MyCon.Execute(sSQL)
'Test to see if any records were matched to an exisitng account
If Not recset.EOF Then
MsgBox "Account Number " & sAccount & " Already Exists"
CancelDocument sErrMsg
Else
'do nothing
End If
'Close the connection
Call MyCon.Close
'Release the object
Set MyCon = nothing
-----------------------------------------------------'
Mick
Share
Report
All responses (
Answers (
