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

15 thoughts on “Integrating Vendor EFT Information Into Dynamics GP

  1. It is suitable for use even on those tender parts of the body.

    Choosing a good conditioner that is right for you can keep hair healthy
    in the long run. There are a number of lasers used
    to remove locks like Ruby aesthetic laser, Alexandrite laser, Pulsed diode
    array, Aragon aesthetic laser as well as Nd:YAG.

  2. Many anglers avoid the use of whole eggs altogether because they
    although they contain all kinds of essential nutritional components fish are obviously going to recognise them from previous egg-containing baits plus eggs affect digestion in various ways.
    Be sure to go with fresh bait, and stay away
    from the baits that are based on chicken or cat food.
    Here are the guild quests along with a handy location and a listing of the reward for
    completion.

  3. Yet he chose to ask me so many questions about how and why I do things, because he could see the profound insight I had built up due to
    going my own way with my own research and testing over the past 7 years.
    The largest inland body of water found in Maryland is Deep Creek Lake.
    Revealed in my unique readymade bait and homemade bait carp and catfish bait secrets ebooks is
    far more powerful information look up my unique website (Baitbigfish) and see my biography
    below for details of my ebooks deals right now.

  4. This is required whether or not the both spouses
    are responsible for the IRS debt. The combination of income taxes and
    self-employment taxes can easily be the biggest single expense
    for the self-employed. Once you have found a school that can help you achieve your educational goals, enroll in their internet classes.

  5. Advanced Plastic Surgery is here to celebrate beauty
    with its state of the art facilities and innovative services
    such as dermabrasion, BOTOX Cosmetic, body surgeries, and the latest Vi – Peel.

    In 2009, men undergoing some sort of cosmetic surgery procedure rose eight
    percent. There is one error: the sentence “The heir who wins the windfall will be the one who finds the.

  6. 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.

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

  8. 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.

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

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

  11. 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