How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.



Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box


-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULL
DROP PROCEDURE uspUpdateVendor1099Box;
GO

CREATE PROCEDURE dbo.uspUpdateVendor1099Box
@VENDORID CHAR(21) = NULL
,@TEN99BOXNUMBER SMALLINT = NULL
,@RET_VAL INT OUTPUT
AS

SET @RET_VAL = 0

IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)
BEGIN
SELECT @RET_VAL = 0;
RETURN
END

BEGIN TRY
UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =
@VENDORID
END TRY
BEGIN CATCH
SELECT @RET_VAL = ERROR_NUMBER()
END CATCH;
GO

GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRP
GO


Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document


' Created by Mariano Gomez, MVP
Const adUseClient = 3
Const adCmdStoredProc = 4
Const adSmallInt = 2
Const adInteger = 3
Const adChar = 129
Const adParamInput = 1
Const adExecuteNoRecords = 128

Dim oCn
Dim oCmd
Dim pVendorID, pTen99BoxNumber, pRetVal

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

With oCmd
.ActiveConnection = oCn
.CommandType = adCmdStoredProc or adExecuteNoRecords
.CommandText = "dbo.uspUpdateVendor1099Box"


'Set parameters
set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _
GetVariable("gVendorID"))
.Parameter.Append pVendorID

set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _
adParamInput, 8, GetVariable("gBoxNumber"))
.Parameter.Append pTen99BoxNumber

set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)
.Parameter.Append pRetVal

.Execute
End With

If pRetVal.Value <> 0 Then
' Error handling here, you can choose to write to IM's log
End If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script -- we will no longer map the vendor ID since it's necessary to use a field script.

Vendor ID field script


' Created by Mariano Gomez, MVP
SetVariable "gVendorID", SourceFields("someSource.Vendor")
SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")
CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don't get discourage if you don't see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Comments

Unknown said…
Mariano,

Why not use VBScript during the integration to populate the field as the records go in? You still get control of what get's populated and you don't need a proc.

Mark
Mariano Gomez said…
Mark,

Thanks for the inquiry!

You can certainly use VBScript all the way through without ever needing a procedure, by executing the same UPDATE statement via a command execute. However, I always try to separate front-end logic from backend logic, just as a matter of best practice. A stored procedure allows you build transactions or try..catch operations around an UPDATE that can evaluate and return the results of the attempted operation.

As you can see from the stored proc, it returns a status which can then be written to the IM error log if something ever happens during the UPDATE operation. This provides greater control and does not assume the update will always be successful.

Best regards,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
Unknown said…
Different approaches. One of my preferences is for the integration package to be as complete as possible. That way I'm not worrying about stored procs or views getting wiped out in an upgrade. It also makes it easy to tweak inside the integration if I've copied it to point to a different company. I don't have to ensure that a proc exists.

Mark
Devo said…
This comment has been removed by the author.
Devo said…
I'm experiencing the following error in IM:

ERROR: Error Executing Script 'After Document' Line 25:
Object required: 'oCmd'

My field level script is as follows.

SetVariable "gVendorID", SourceFields("VENDOR_ID")
SetVariable "gBoxNumber", SourceFields("M1099_CD")
CurrentField.Value = SourceFields("VENDOR_ID")

The integration will still run (giving the error above for each document) and I noted that the appropriate 1099Box cells are populated in the database table, but that they all have the value 7 instead of the values from the source text file. Also, putting a MsgBox at the end of the After Document script revealed that the script does not complete, as the MsgBox never appeared.
Mariano Gomez said…
Devo,

Your error is not referring to your field script, rather your After Document script.

To correct the problem, edit your After Document script as follows:

.
.
GPConnection.Open(oCn)
set oCmd.ActiveConnection = oCn
With oCmd
.CommandType = adCmdStoredProc or adExecuteNoRecords
.
.

Note I removed the ActiveConnection property from within the With..End With block.

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
Devo said…
I was able to successfully integrate the Vendor 1099 Box using this method. Thanks Mariano for another tool in the IT belt!

Having now completed the process and looking back though, I realize that there is an easier and more eloquent method of integrating the Vendor 1099 Box. That process is through properly defining and creating Vendor Classes. As vendors are integrated through classes, they inherit the properties of the class to which they are assigned, one property of which is the Vendor 1099 Box. Additionally, the proper use of classes simplifies vendor maintenance in the long run.
Mariano Gomez said…
Devo,

Glad to know you were able to apply the method discussed here. I completely agree on proper use of classes, but that's not always the case at some customers. For some customers, classes represent geographical locations, and not whether a vendor is trade or contractor, hence the post.

Best regards,

MG.-
Mariano Gomez, MVP
atlbabs said…
Hi - GP Dynamics Super User/NON SQL coder here...is the process described the only way to add destination fields in to an IM adapter? I'm trying to import vendor EFT info via IM but that button under cards/purchasing/vendor/address is not an accessible field nor is the following EFT input fields.

thanks!

Barbara
Mariano Gomez said…
Barbara,

Unfortunately, this is the only way. EFT is a good example too because EFT functionality is delivered via a separate add-in dictionary. So, the only way to access those tables is via SQL Scripting or VBScript.

If you feel I can help you building this integration, please do not hesitate to contact me.

Best regards,

MG.-
Mariano Gomez said…
Barbara,

Unfortunately, this is the only way. EFT is a good example too because EFT functionality is delivered via a separate add-in dictionary. So, the only way to access those tables is via SQL Scripting or VBScript.

If you feel I can help you building this integration, please do not hesitate to contact me.

Best regards,

MG.-
Devo said…
I could not find that field in IM either so I used a macro as described in the following post:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/30/how-to-use-word-mail-merge-and-macros-to-import-data.aspx
Mariano Gomez said…
Devo,

Unfortunately, not all fields are exposed to IM, hence the reason why I created this post. The technique outlined at Developing for Dynamics GP is just another method of importing data.

MG.-

Popular posts from this blog

Power Apps - Application Monitoring with Azure Application Insights

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

eConnect Integration Service for Microsoft Dynamics GP 2010