Create EDI 210 Invoice from code Access VBA

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.

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.

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.

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.

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?

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.

I have one last request. I wonder if you could attach an actual EDI file just change anything private in it.

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

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.

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.

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

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

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.

Do the interfaces 810, 812, 850 share any thing in common in the structure?

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

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

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?

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

JimD:

Just stay away from SPS Commerce

LOL ... comments on that? I'm up to my ears with these guys in another project ...

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.

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.

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

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

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?

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.

Thank you both for your help I could not be as far as I am without it.

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.

Thanks for the update.

Great job getting it working with VBA code in Access!