web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Integration manager Pu...
Microsoft Dynamics GP (Archived)

Integration manager Purchasing Vendor ID

(0) ShareShare
ReportReport
Posted on by Community Member

In IM - when importing an item with an incorrect vendor ID, the line will fail.  How can I do a field script which checks if the vendor ID is valid, and if not, can prompt the user for a valid vendor?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    This one can be done in one of two ways (very similar to your previous request):

    1. You can either do the validation in the Before Document script, or

    2. Do the validation in the Vendor ID field script.

    For option 1, you can do something like this:

    ' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdText= 1
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim oCn, oCmd

Set oCn = CreateObject("ADODB.Connection")
Set oCmd= CreateObject("ADODB.Command")

' this connection uses your Windows account to connect to the database server
oCn.Open "Provider=SQLNCLI10;Server=YOURSQLSERVER;Database=YOURCODB; Trusted_Connection=yes;" 

With oCmd
 .ActiveConnection = oCn
 .CommandType = adCmdText
 .CommandText = "SELECT @Result = VENDORID FROM PM00200 WHERE VENDORID = @VendorId" 'vendor parameter
 
 .Parameters.Append .CreateParameter ("@VendorId", adVarchar, adParamInput,,SourceFields("SomeSourceQuery.VendorID"))
 .Parameters.Append .CreateParameter ("@Result", adVarchar, adParamOutput)
 
 .Execute 
 VendorExists = oCmd.Parameters("@Result").Value

End With
oCn.Close

Do While VendorExists = "" 
   VendorId = InputBox("Please enter a valid Vendor ID")
Loop

SetVariable "gblVendorId", VendorId

Set oCmd = Nothing
Set oCn = Nothing


    You will then need to transfer the value to the vendor field, using a field script as follows:

    CurrentField.Value = GetVariable("gblVendorId")



  • Community Member Profile Picture
    Community Member on at

    I used this script, the first issue I came to was the first line which integration manager VB doesn't seem to like "Option Excplicit"  For fun I thought I'd remove that line - I then get the problem that says "Parameter Object is improperly defined.  Inconsistent or incomplete information was provided.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Yeah, it seems vbScript does not like a few things.

    1. You did well commenting out the Option Explicit. That only works with Windows Script Host (WSH).

    2. The first CreateParameter would seem to need the Size property. Try this:

    .Parameters.Append .CreateParameter ("@VendorId", adVarchar, adParamInput, 4, SourceFields("SomeSourceQuery.VendorID"))

  • Community Member Profile Picture
    Community Member on at

    I did as much research in VB to solve this before coming back to you - the new error is:

    Application uses a value of the wrong type for the current operation.  

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Does it specify or stop at a specific line that is failing? By the way, this is VBScript, which is very similar to VB, but certain things tend to behave, well how shall I say, different :-)

  • Community Member Profile Picture
    Community Member on at

    LOL - noted.  It says line 20 - which is this line after I've removed comments and blank lines:

    .Parameters.Append .CreateParameter ("@VendorId", 200, 1, 4, SourceFields("Vendor"))

    Just for testing I used 200, 1 which are the substitute values for adVarcharm, etc.  Using any number between 1 and 11 for value generates the same error.  12 and greater gives the original error of the parameter being improperly defined.

  • Suggested answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    I realized couple things from your post.

    1) You are not providing a source query for the "Vendor" field. Take a look at your queries for the integration and specify the source query name containing the vendor field, i.e., PM_HDR.Vendor

    2) I believe we may need to do something slightly different. We probably need to create a calculation for the source query value and inject that calculation into the CreateParameter function method as follows:

    Const adCmdText= 1
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim oCn, oCmd

Set oCn = CreateObject("ADODB.Connection")
Set oCmd= CreateObject("ADODB.Command")

' this connection uses your Windows account to connect to the database server
oCn.Open "Provider=SQLNCLI10;Server=YOURSQLSERVER;Database=YOURCODB; Trusted_Connection=yes;" 

With oCmd
 .ActiveConnection = oCn
 .CommandType = adCmdText
 .CommandText = "SELECT @Result = VENDORID FROM PM00200 WHERE VENDORID = @VendorId" 'vendor parameter
 
  myVendor = SourceFields("SomeSourceQuery.Vendor")
 .Parameters.Append .CreateParameter ("@VendorId", adVarchar, adParamInput, 4, myVendor)
 .Parameters.Append .CreateParameter ("@Result", adVarchar, adParamOutput)
 
 .Execute 
 VendorId = oCmd.Parameters("@Result").Value

End With
oCn.Close

Do While VendorId = "" 
   VendorId = InputBox("Please enter a valid Vendor ID")
Loop

SetVariable "gblVendorId", VendorId

Set oCmd = Nothing
Set oCn = Nothing

    I have highlighted the changes in red for easy follow. Note that I also removed the Option Explicit line.

  • Community Member Profile Picture
    Community Member on at

    Sadly I get the same error.  BTW I found it not necessary to name the data source if you are only using 1 - so using SourceFields("vendor") works for me.  Injecting this into a variable doesn't work.  I added mgsbox (myVendor) just to see if it was passing the vendor name and it does properly.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Cool! Thanks for the update. I will check to see what's not working with this script - now I have to whip out IM :-)

  • Community Member Profile Picture
    Community Member on at

    Found an article, changed the script around to this:

    Const adCmdText= 1

    Const adParamInput = 1

    Const adParamOutput = 2

    Const adParamInputOutput = 3

    Const adInteger = 3

    Const adVarchar = 200

    Const adBoolean = 11

    Const adChar = 129

    Const adDate = 7

    Const adNumeric = 131

    Dim SqlStmt

    Dim oCn, oCmd

    Set oCn = CreateObject("ADODB.Connection")

    Set oCmd= CreateObject("ADODB.Command")

    Set par1= CreateObject("ADODB.Parameter")

    Set par2= CreateObject("ADODB.Parameter")

    oCn.Open "Provider=SQLNCLI10;Server=ACTIONSRV15-3;Database=SYS; Trusted_Connection=yes;"

    par1.Direction=adParamInput

    par1.name="@VendorID"

    par1.Size=200

    par1.Type=adVarChar

    par1.Value=myVendor

    par2.Direction=adParamOutput

    par2.name="@Result"

    par2.Size=200

    par2.Type=adVarChar

    With oCmd

    .NamedParameters = True

    .ActiveConnection = oCn

    .CommandType = adCmdText

    .CommandText = "SELECT @Result = VENDORID FROM PM00200 WHERE VENDORID = @VendorId"

     myVendor = SourceFields("vendor")

    .Parameters.Append (par1)

    .Parameters.Append (par2)

    .Execute

    VendorExists = oCmd.Parameters("@Result").Value

    End With

    oCn.Close

    Do While VendorExists = ""

      VendorId = InputBox("Please enter a valid Vendor ID")

    Loop

    SetVariable "gblVendorId", VendorId

    Set oCmd = Nothing

    Set oCn = Nothing

    Now I'm seeing that parameters don't work in ADO commands - so I'm getting the "Must declare scalar variable @Result.  

    Still working at it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans