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

24 thoughts on “Integrating Vendor EFT Information Into Dynamics GP

  1. You can try several things to help speed up the process
    and maybe even lighten the tan up a little bit.
    Sun Labs also provides a lot of individual self tanning and fake tan products that
    you can sell to your clients as they walk out the door. That way,
    you will never be out of the color you like if they happen to run out of it at the local drugstore
    or beauty center.

  2. Even frequent blow-drying at extreme heat or brushing obsessively can cause your hair to
    become extremely fragile causing it to break and fall out.
    Purchase shampoo or other hair care products that are meant specifically
    for people with thinning hair. We live in a climate that gets hot and humid and there are many activities to choose from.

  3. And you know what they say: “once on the internet, stays on the internet”.

    Does your recruiter make his or her money from your sign-up fees.
    But other than those who just want to communicate with friends, old and
    new, social media is also currently being taken advantage by business oriented people as a way to
    reach their target clients and improve their businesses.

  4. 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.
    Connect “A’s” positive terminal to “B’s” negative terminal.

    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.

  5. Pls avoid face all show, bang do one row, try to make both sides hair have fleeciness feeling, unfavorable to keep long straight hair, such as: mushrooms hairstyle, short Xiu – Zhi hair,students’hairstyle;.
    For the young professional families that can no longer afford King William have chosen Mahncke Park and have started
    refurbishing the run down homes with wraparound porches.

    The employer may want to explain how to do something differently than how you have been doing it.
    New York City, major center of finance, insurance, media,
    and real estate, sis one of the world’s financial capitals.
    According to Schwarzenegger, Russia is considered to be a gold mind to foreign investors.

    As soon as you’ve looked at all the principals, it’s time to have a trip to the self-storage facility itself
    to see what exactly they are offering as a portion of the package.
    Leading organizations specializing in datacenter solutions offer their clients and customers with
    authentic warranties. They will used simply by several sportsmen, celebrities,
    artists and also superstars for instance – Supra Skytopwill
    be duplicated, of his concerts and also roller skates celebrations.

    3rd party logistics providers don’t just stock and handle merchandise, they provide crucial support for shippers and retailers who are looking into overseas markets.
    Unanticipated incidents or acts of God such as fire or floods can be dangerous for every single business who relies
    on their stored paperwork.

  6. Furthermore, feeding live food is difficult as all live meals have
    a much higher risk in bringing bad bacteria or microorganisms that maybe harmful to
    your fish. Nowadays, these super coolers are used for both commercial and domestic purpose.
    Be sure to vacuum floors, rugs and upholstery your pet comes in contact with regularly.

  7. These forms require you to have an employer identification number.
    Most of all, these business owners ensure
    that aside from core functions in their company, that the people and other downstream resources are given the right treatment all the time.
    So as you can see, the earning potential with
    CPA offers is virtually unlimited.

  8. Take for instance a company that for the current period
    has more outstanding payables than they do receivables. But there is an exception if the
    caregiver is your parent. Itemized deductions
    and personal exemptions do not affect self-employment taxes.

  9. She has a charm that is slightly reminiscent of the childhood favourite Rosie and Jim’s Ragdoll, with a
    modernized interior to suit the 21st century traveller. Be sure to go with fresh bait, and stay away from the baits that are based on chicken or
    cat food. Freshwater fish can be placed in the live well
    on the boat, or on a stringer to keep it alive until you are able to clean it.

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

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

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

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

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

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

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

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

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

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

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