Create EDI 210 Invoice from code Access VBA

I am looking to create an EDI 210 document from my Invoice database. I am looking for help on where to start. Maybe someone already has an example I can follow. Code can be VBA or VB.

I need to create the EDI doc then send it off to company using ftp. Code examples would be great
LVL 1
IdaracAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
not familiar with the "EDI 210" format.

Generally, when I'm writing anything to an EDI format, I create a new Type and each element of the type has the appropriate number of characters.  Then, depending on whether certain fields are left or right justified (defined in the EDI format), I either left or right pad the values as I fill the strings.  When converting numbers, I use the format() function to ensure I have the right number of decimal place.
0
IdaracAuthor Commented:
Thanx fyed

So does EDI have a certain file structure or does it change depending on the company.
I keep hearing about mapping the data.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
So does EDI have a certain file structure

Yes. The format is governed by ASC X12 (  http://www.x12.org/ ). That way all software systems can transfer data using a standard format.

EDI 210 Motor Carrier Freight Details and Invoice:

The EDI 210 transaction set is called the Motor Carrier Freight Details and Invoice. It replaces a paper invoice, used by commercial truckers and other freight carriers.

The 210 transaction is typically sent from the carrier to a shipper, consignee or third-part payment center for payment of freight charges. One 210 transaction is usually sent for each shipment. It provides an itemized detail of the charges, along with other information you would typically find on a paper invoice:

    Invoice date and number
    Bill-to name and address information
    Order information detail, including items, quantities and weights
    Shipping methods and descriptions
    Shipper name and address information
    Consignee name and address information
    Terms of payment
    Delivery information, including proof of delivery, tracking numbers, etc.

After a 210 Motor Carrier Freight Details and Invoice is received, a 997 Functional Acknowledgment is sent back from the transportation provider to indicate that the 210 transaction was successfully received.

EDI 210 Format:
ISA*00*          *00*          *01*ABCCOM         *01*999999999      *110813*1410*U*00300*000000001*0*P*>
GS*IM*006998397*123456789*20110813*1410*000000001*X*004010
ST*210*000000001
B3**2509121213*8000281336*PP**20110813*18304**20110801*017*XXXX
N9*PO*SM12003301
G62*86*20110801
N1*CN*AAA HARDWARE
N3*9805 POPLAR ST
N4*LEADVILLE*CO*80461
N1*SH*BBB OIL COMPANY
N3*2361 S DIXIE HWY
N4*LIMA*OH*45802
N1*BT*ANY PAY AGENT
N3*ATTN: DONNA SMITH*PO BOX 16789
N4*ANYTOWN*MO*12345-6789
LX*1
L5*1*PETROLEUM OILS,*15525002*N
L0*1***138*N***1*PLT**L
L1*1*120*PH*18304
L7*1******0E60
LX*2
L3*138*G***18304******1
SE*21*000000001
GE*000001*000000001
IEA*00001*000000001

Open in new window


EDI 210 Specification:
This X12 Transaction Set contains the format and establishes the data contents of the Motor Carrier Freight Details and Invoice Transaction Set (210) for use within the context of an Electronic Data Interchange (EDI) environment. The transaction set can be used to provide detail information for charges for services rendered by a motor carrier. It is used both as a motor carrier invoice to request payment or as details pertaining to motor freight shipment(s) charges.

Sources
Accredited Standards Committee X12. ASC X12 Standard [Table Data].
Data Interchange Standards Association, Inc., Falls Church, VA.
http://www.x12.org
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

IdaracAuthor Commented:
K so if I got this right.  My job is to create the EDI file for upload by.

1. Mapping my data to the EDI standard format (ASC X12).
2. Code my routine to extract the invoice data and create the EDI file.
3. I believe we will be using FTP to upload file.

I will have to put out a question on how to upload a file using VBA.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Sounds like you got it.

TIP - check out the example I have on my site:

FTP Client functions
This database is in 2000 format. It contains an example of how to perform FTP tasks from within Access like, upload, download, and browse the directory on an FTP site.
0
IdaracAuthor Commented:
In my cruising the net I see some examples without ISA and GS. Are these 2 always required?
Some just start off with the Transaction Set Header

I think I have the ST B3 stuff sorted but do you have any info on the ISA and GS lines. Which pieces are specific to the client sending?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
The required records/data are based on your data being submitted and the type of transaction.  

I recommend fully complying with tte standard.  I have found that to be the best solution with health care claims.
0
IdaracAuthor Commented:
I have one last request. I wonder if you could attach an actual EDI file just change anything private in it.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I deal with EDI a lot, just not these transaction sets.

The example file I posted previously is the closest I can give you to the actual format. Unfortunately without the specification documentation you will not have any way of knowing what the data means or which records are mandatory and which are optional based on need.

I did some searching and found a site that has a PDF of the specifications. Scroll down to the section labeled EDI Implementation Guides at this site: http://www.yrc.com/shippers/edi.html
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I work with EDI a lot, and the doc your referring to is an 810, not a 210.

I would strongly suggest that you do not generate this directly from Access, but instead use proper translation software.

You can still use Access to generate the application file that you feed to a translator, but you should not be using it to generate the EDI document itself.

 You've only started to touch on some of the things you will need to handle.  For example, not everyone uses the X12 std.  Some use UCS.    Even within X12, there are different versions.  3010, 4010, 5010 are popular right now as are the VICS versions.  

 Last, the translator will take care of meeting all the specs, including conditional elements, code list lookup, etc. and will also take care of the enveloping (the ST, GS, and ISA Segements).

  I'm not saying you can't do it, but if you use Access to write the EDI docs directly, your in for a lot of work and personally, I think you'd be heading down the wrong path.  THe other issue is that depending on who you deal with (ie. Walmart), you may only be allowed to use approved and certified communications software and translators.

 There are a multitude of services online that will handle the EDI side if you don't want to bear the expense of buying your own translator.

 Just stay away from SPS Commerce.

Jim.
0
IdaracAuthor Commented:
The thing to consider iswe currently have this app in a number of locations with more coming online. I will have to figure out which way to go either bite the bullet and create something , the translation software or using a service.

What is the ballpark for either the software or using a service. I would somehow have to pass these costs over to the client locations.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If your doing this as an application, I would write in the application file (an export/import  of the data) and stop there.

It would then be up to the client to either purchase a EDI translator or use a service.


You could go as far as providing a list of services /software they might use and approx pricing.

This is the way most applications approach EDI.   You provide the interfaces, which are typically:

850 - PO
855 - PO Acknowledgement
860 - PO Change notice
865 - PO Change notice acknowledgement
856 - Advanced Ship notice
810 - Invoice
812 - Debit / Credit memo
820 - Remitance Advice

and leave the rest up to the translators to convert from your interface file to an EDI document.

Jim.
0
IdaracAuthor Commented:
Can you confirm for me but it looks like the cost of translation software Low end 1,500 -3,500
mid range 4,500-15,000. Add to that training and yearly maintenance fees. Multiple that by the number of locations we have (and growing).
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Those are indeed the usual numbers. I have one client right now that pays $12k per year just in maintenance fees alone on their translator software.

That is why most SMB's use a service rather then doing it in-house.   Sterling Commerce, Liaison  (nuBridges), Hi jump software all offer EDI translation service.    

Apps such as Quickbooks,  Peachtree, JD Edwards,  etc. all approach EDI in this way

Jim
0
IdaracAuthor Commented:
Thanx Jim thank you being aware of the alternatives (ie software, service etc.)

My boss is of the mind that if we can make it work ourselves then that's more value added revenue for us.
So my focus is on trying to make it work as a one click operation sending out the invoice as an EDI doc. We have done some pretty involved export files in past financial, Quickbooks, XML. I have 15 years experience as a programmer.

If it doesn't work we can look at the translation software or talking to a service.
0
IdaracAuthor Commented:
Do the interfaces 810, 812, 850 share any thing in common in the structure?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
@Jim, Thanks for jumping in.

I totally agree with Jim that you are better off using a EDI translation service.   Especially if you want it fully implemented in the next 12 months.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Envelopes are all the same (st, gs, and Isa). But beyond that, yes and no.  Some segments are quite common, such as DTM (date time), but some docs have segments unique to them.   And all the segments and what they contain vary by standard and version.

Just to be clear on the translator software,  for a company, typically you only have one set of software,  not one for each location.   You'd move the application files around instead internally.

Jim
0
IdaracAuthor Commented:
You both have been most helpful. I wished I had more points to distribute or maybe buy you a beer.

We sell our software to different companies but I also noticed there are EDI APIs out there we may be able to use.

To the coach: Have you been able to develop a sort of one click operation that creates the EDI and sends it off to the destination?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
To the coach: Have you been able to develop a sort of one click operation that creates the EDI and sends it off to the destination?

Yes, but probably not the way you are thinking. Everything now goes through a clearinghouse (third-party).
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
JimD:

Just stay away from SPS Commerce
LOL ... comments on that? I'm up to my ears with these guys in another project ...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
They drag everything out, takes *forever* to get anything done, and they are over priced.

I've had more then a few projects go south with them over the years.  I don't know how they even manage to stay in business.

Jim.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
They drag everything out, takes *forever* to get anything done,
Exactly the fix I'm in right now. I'm just the data consultant on the project, so no real interaction with the EDI side of things from a programming standpoint, but it definitely takes them a looooooong time to get things done. We've already had to ask for extensions with a big military customer twice. The last (and final) extension is up on Monday, and there is no way they'll be ready, and the customer is boiling mad.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The last (and final) extension is up on Monday, and there is no way they'll be ready, and the customer is boiling mad. >>

 My last project was exactly like that.   Nine months to do one simple EDI document and it still wasn't finished.   Customer cancelled the project.  All of a sudden it was "done".  Customer refused to make any payments and went to another EDI provider.

  This was after SPS had given them three months for an estimate (which I thought was a long time already), and then they missed two delivery dates after that.

  Office Max uses them for EDI and everytime I go through a standards change with them, it's a nightmare.

  I cringe whenever I hear SPS Commerce.

Jim.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One of the companies I'm under contract with has partnered with SPS and another to provide EDI services for their software (ERP stuff). The other provider is pricey, but they're on target for rollout and have had very few glitches. SPS is somewhat less expensive, but you get what ya pay for in this game it seems.

I'm fighting with SCAC mappings right now. I've been on them for weeks now to finalize the SCAC mappings (the trading partner gave them a list of approved shippers, and we've mapped them on our side, but they haven't yet completed the link to that mapping).

<Sigh> ... oh well, just another day at the pump I suppose
0
IdaracAuthor Commented:
Just be a bit nosy but also in case I have to go that route. What services is it that SPS provides?

What is the process they go through to provide EDI?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
SPS and many others will offer everything from the "app" out.

So in your application, say for the 810, you export a flat file.

They will take that flat file from there and get it to your trading partner.

That means:

1. Doing the initial mapping of your file to the Trading Partner's (TP) 810 specification.
2. Initially setting up a connection to that TP either by VAN (Value Added Network) or AS2 (peer to peer secure communications via HTTP protocol).

3. As each file comes through, translating that into an EDI document and sending it.
4. Ensuring that a 997 Functional Acknowledgement is returned for each document. group, or interchange sent.

 If you had your own translator, then might provide just the communications part.   If you additionally had your own AS2 software, they might just handle the VAN portion.

Jim.
0
IdaracAuthor Commented:
Thank you both for your help I could not be as far as I am without it.
0
IdaracAuthor Commented:
I don't know if everyone still receives update once it has been closed. But I just want to thank you again for your help. Because I could not have done it without you. I just received word that my EDI doc passed error free and we are ready to do testing then on to production. It was lot of hard work and took me almost 3 weeks to get it going. I did end up coding it using VBA code in Access.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Thanks for the update.

Great job getting it working with VBA code in Access!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.