Avatar of Idarac
Idarac asked on

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
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

8/22/2022 - Mon
Dale Fye

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

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.
ASKER CERTIFIED SOLUTION
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Idarac

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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

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?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Idarac

I have one last request. I wonder if you could attach an actual EDI file just change anything private in it.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Idarac

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Dettman (EE MVE)

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

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).
Jim Dettman (EE MVE)

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Idarac

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

Do the interfaces 810, 812, 850 share any thing in common in the structure?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

@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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim Dettman (EE MVE)

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
ASKER
Idarac

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?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

JimD:

Just stay away from SPS Commerce
LOL ... comments on that? I'm up to my ears with these guys in another project ...
Jim Dettman (EE MVE)

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.
Scott McDaniel (EE MVE )

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jim Dettman (EE MVE)

<<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.
Scott McDaniel (EE MVE )

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
ASKER
Idarac

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

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

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

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Thanks for the update.

Great job getting it working with VBA code in Access!