Integrating Vendor EFT Information Into Dynamics GP

Recently, I was asked to integrate vendor information into Dynamics GP. At first glance, this is a straight forward integration which is done all the time. As I went over the file, I found one of the requirements was to include EFT information along with the address. These fields are not available using either the Dynamics GP or eConnect destination adapters.

It is possible, of course, to develop an application that calls web services or writes directly to the tables, and while that is a better solution, it would take significantly longer than writing something in Integration Manager.

The first step is to map the vendor information using standard Integration Manager mapping. Following that, you want to put a script in the “Before Document Commit” area of the integration object. This script will run after Integration Manager validates all other data in the record, but before the record is posted to the database.

The first step is to declare the variables you will use to enter data in each field. That is done using the dim command. Included in the variables should be connections for both the database and a recordset so that records can be added to the address master table (sy00600)

Dim objRec
Dim objConn

Set objRec = CreateObject(“ADODB.Recordset”)
Set objConn = CreateObject(“ADODB.Connection”)

The following is a sample of adding variables for the data fields.

dim ADRSCODE
dim EFTBankCode
dim EFTTransitRoutingNumber

You will then assign values to each variable by using the field name followed by an equals (=) sign and the value. In the case of Integration Manager, we will want to read those values from the source file provided. There is a command called sourcefields available for that. For example, assuming we setup a source object called vendors and a field called Address ID, we could set the value of the ADRSCODE by using:

ADRSCODE = sourcefields(“vendors.Address ID”)

I also recommend using the trim command to remove any leading or trailing spaces.

ADRSCODE = trim(sourcefields(“vendors.Address ID”) )

The cmdstring command is used to create a SQL statement that can be executed at the end of the script.

sSQL = “insert into [dbo].[SY06000] … “

Also, very important is to close your connection at the end of the script. If you don’t, each record imported will open a new connection. At some point, you will run out of system resources if there are too many connections open.

The following is an example of a script after all these components are put together. It is only a sample and is provided as is. You will need to modify based on your specific field names and fields you want to update. Be sure to change the database name and password on the connection string as well.

Dim objRec
Dim objConn
Dim cmdString
Dim VendorID
Dim ADRSCODE
Dim EFTBankType
Dim BankName
Dim EFTBankBranch
Dim IntlBankAcctNum
Dim Bnkctrcd
Dim CBankcd
Dim RegCode1
Dim EFTTransitRoutingNo
Dim Curncyid
Dim BankCode
Dim RegCode2
VendorID = trim(SourceFields(“VendorsEFT.Vendor ID”))
ADRSCODE = trim(SourceFields(“VendorsEFT.Address ID”))
EFTBankType = trim(SourceFields(“VendorsEFT.EFTBankType”))
BankName = trim(SourceFields(“VendorsEFT.BankName”))
EFTBankBranch = trim(SourceFields(“VendorsEFT.EFTBankBranch”))
IntlBankAcctNum = trim(SourceFields(“VendorsEFT.IntlBankAcctNum”))
Bnkctrcd = trim(SourceFields(“VendorsEFT.BNKCTRCD”))
CBankcd = trim(SourceFields(“VendorsEFT.CBANKCD”))
EFTBankCheckDigit = trim(SourceFields(“VendorsEFT.EFTBankCheckDigit”))
RegCode1 = trim(SourceFields(“VendorsEFT.RegCode1″))
EFTTransitRoutingNo = trim(SourceFields(“VendorsEFT.EFTTransitRoutingNo”))
Curncyid = trim(SourceFields(“VendorsEFT.Currency ID”))
EFTBankCode = trim(SourceFields(“VendorsEFT.BANK CODE”))
RegCode2 = trim(SourceFields(“VendorsEFT.RegCode2″))
Set objRec = CreateObject(“ADODB.Recordset”)
Set objConn = CreateObject(“ADODB.Connection”)
objConn.ConnectionString = “Provider=MSDASQL;DSN=Dynamics GP;Initial Catalog=TWO;User Id=sa;Password=PASSWORD”
objConn.Open
cmdString = “INSERT INTO [TWO].[dbo].[SY06000]” &_
“([SERIES]” &_
“,[CustomerVendor_ID] “&_
“,[ADRSCODE] “&_
“,[VENDORID] “&_
“,[CUSTNMBR] “&_
“,[EFTUseMasterID] “&_
“,[EFTBankType] “&_
“,[FRGNBANK] “&_
“,[INACTIVE] “&_
“,[BANKNAME]“&_
“,[EFTBankAcct] “&_
“,[EFTBankBranch] “&_
“,[GIROPostType] “&_
“,[EFTBankCode] “&_
“,[EFTBankBranchCode] “&_
“,[EFTBankCheckDigit] “&_
“,[BSROLLNO] “&_
“,[IntlBankAcctNum] “&_
“,[SWIFTADDR] “&_
“,[CustVendCountryCode] “&_
“,[DeliveryCountryCode] “&_
“,[BNKCTRCD] “&_
“,[CBANKCD] “&_
“,[ADDRESS1] “&_
“,[ADDRESS2] “&_
“,[ADDRESS3] “&_
“,[ADDRESS4] “&_
“,[RegCode1] “&_
“,[RegCode2] “&_
“,[BankInfo7] “&_
“,[EFTTransitRoutingNo] “&_
“,[CURNCYID] “&_
“,[EFTTransferMethod] “&_
“,[EFTAccountType] “&_
“,[EFTPrenoteDate] “&_
“,[EFTTerminationDate]) “&_
“VALUES “&_
“(4 “&_
“,rtrim(‘”& VendorID &”‘)”&_
“,rtrim(‘”& ADRSCODE &”‘)”&_
“,rtrim(‘”& VendorID &”‘)”&_
“,” “&_
“,rtrim(’1′) “&_
“,rtrim(’26′) “&_
“,rtrim(’1′) “&_
“,rtrim(’0′) “&_
“,rtrim(‘”&BankName & “‘) “&_
“,” “&_
“,rtrim(‘”&EFTBankBranch &”‘) “&_
“,1″ &_
“,rtrim(‘”&EFTBankCode &”‘) “&_
“,” “&_
“,rtrim(‘”& EFTBankCheckDigit &”‘) “&_
“,” “&_
“,rtrim(‘”& IntlBankAcctNum &”‘) “&_
“,” “&_
“,” “&_
“,” “&_
“,rtrim(‘”& Bnkctrcd &”‘) “&_
“,rtrim(‘”& Cbankcd &”‘) “&_
“,” “&_
“,” “&_
“,” “&_
“,” “&_
“,rtrim(‘”& RegCode1 &”‘) “&_
“,rtrim(‘”& RegCode2 &”‘) “&_
“,0″&_
“,rtrim(‘”& EFTTransitRoutingNo &”‘) “&_
“,rtrim(‘”& CURNCYID &”‘) “&_
“,2 “&_
“,1 “&_
“,rtrim(’1900-01-01 00:00:00.000′) “&_
“,rtrim(’1900-01-01 00:00:00.000′)) “
Set objRec = objConn.Execute(cmdString)
ObjConn.Close

8 thoughts on “Integrating Vendor EFT Information Into Dynamics GP

  1. I’m not finding a table, under the Table import option that has the EFT fields. Do you know the name of the table? I’ve looked under all the options, not just purchasing. Thank you!

    • The sy06000 table has the addresses for customers and vendors. The series will be 4 for purchasing. The vendor id and address id are also in that table along with all the EFT information.

  2. Pingback: Integrating Vendor EFT Information Into Dynamics GP - All Dynamics GP - Microsoft Dynamics GP - Microsoft Dynamics Community

  3. Very true. While table import is also a good option, but requires an extra step as the table import would need to be run following the integration. It is definately easier to write than the SQL script though.

  4. Pingback: Integrating Vendor EFT Information Into Dynamics GP | alldynamicsgp – 2/5, DynamicAccounting.net | Partner Compete

  5. Pingback: Integrating Vendor EFT Information Into Dynamics GP | alldynamicsgp - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community

  6. Pingback: Integrating Vendor EFT Information Into Dynamics GP | alldynamicsgp - DynamicAccounting.net - Microsoft Dynamics GP - Microsoft Dynamics Community

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s