Retail EDI

_____________________________________________________________________________

(from the front page)

This topic was added to our calendar when a reader sent us an e-mail which reads: “Hi, we have a homemade application and our invoice info is generated thru a sql query and saved as a CSV file.  How can I import the CSV file in Peachtree…?”  I don’t have any techies to research this or figure out ‘how to’ anymore so I went into my rolodex and started calling and e-mailing people questions about Peachtree and how to get data into or out of it.

______________________________________________________________________________ 

For those not familiar with it, Peachtree by Sage is one of the most popular, least expensive accounting applications on the market today.  From what I can tell the original application came out around 30 years ago – it’s got some longevity!  I guess its closest competitor is QuickBooks (Not positive about that).  The application starts at around $300.00 and has a broad range of versions as well as a couple dozen third party applications to assist with processes such as point of sale scanning, shipping modules for FedEx, and UPS and a range of other solutions.

What about EDI?  Is it suitable for Retail Suppliers? 

If you are a manufacturer or supplier selling to a retailer – how can you get all the store level orders (And invoices) in an out of the program?  Not to mention manage documents like the ASN (856 Advanced Ship Notice) or others.  These are critical.  Even if they are blanket PO’s, if you have one retail client ordering 8 SKUs from you for several stores, weekly, the data entry can become a burden to say the least. 

One of the first places I went to look for answers was here (http://www.sagepss.com).  This is the Peachtree by Sage Partner Site – you can find all sorts of ‘bolt-on’ tools here including four EDI providers.  Only one of them has any reviews (B2B Gateway), but you will find True Commerce is the only Sage endorsed EDI solution on the site.  It’s really just an oversight that more of each of these companies doesn’t have more reviews.  The fact is True Commerce and the other EDI provides have been in partnership with Sage as developers for some time and all *should* have numerous clients running their EDI solutions on conjunction with Peachtree by Sage. 

What if you want to roll your own and integrate some sort of EDI solution with your Peachtree application?  If you do there are some details to consider.  When thinking about application integration typically you need to look at common file formats each of the two programs will accept.  A bridge or: common ground.  Some of the more regular formats include: .CSV, .TXT, XML and so on.  Often you can also write to and from a database (Not in this case, see OLEDB below).   Really, to keep it simple we just want to look at the most common: .CSV, .TXT, and XML.  I discovered the following in my research:

• XML - The XML import/export was never fully developed, and has a lot of bugs in it.  The Peachtree API, described by the SDK documentation allows you to import XML files.  XML is inherently more robust than CSV, so is a preferred way to go. The XML data needs to be in the right structure for Peachtree to accept it, however again, it’s buggy and there is little documentation to help.

• CSV/TXT - Import via .csv or .txt file is how most EDI integration with Peachtree works.  After talking to several integration consultants and certified Peachtree consultants – this is the only scenario I found to have any successful resonance.

A quick comment about ODBC and Peachtree:  ODBC (Open Database Connectivity) and OLEDB (Object Linking and Embedding, Database) offer you a way to connect to your Peachtree data without using Peachtree directly. You should be able to create an ODBC or OLEDB connection directly between another application, such as Microsoft Excel or Access and your Peachtree instance.  I got the following statement from the Peachtree by Sage Forum (Written by a Sage employee): “Configuring ODBC has proven to be less than straight forward - primarily due to the number of unique operating system, server configurations, Excel & Crystal Reports connections, etc. that exist. In all honesty, we didn't anticipate all of the intricacies of setting up ODBC in the myriad of environments our customers work within and our documentation lacked that detail as a result.”  Nuff said.  Be careful.  One consultant I spoke to was hopeful that this would be resolved in the 2010.

There are developers who can write a custom application which will eat up the output from your EDI solution and insert the data into your Peachtree database and vice versa (Peachtree uses Pervasive).  The scenario could look like this:

Using Gentran, BizTalk (A translator) or even taking a flat file from your favorite VAN: 

1. To receive PO’s you would take your EDI map from Gentran, convert to CSV and deposited into a specific directory.  From there you may be able to use a script on a schedule to look and see if there is a file and import into Peachtree.  
2. To send Invoices you use a script to export invoices in CSV format to another directory.  Gentran can be set to ‘look’ for files on a schedule and convert the CSV to EDI – and route as necessary.

What about the ASN and other documents (Inbound or outbound)?  If you need to do these for your clients, it’s not happening in Peachtree.  And look really hard before you listen to someone tell you that you MUST buy MAS 90, 200 or 500.

You could go and look for add-on applications, but in my experience the scenario above will require enough tweaking and fine-tuning to cure anyone of this.  The cost of the programming, time lost in fine-tuning, etc. makes it almost an immediate deal breaker, except in rare circumstances.  The best, most cost effective solution in this scenario is to go with a solution like True Commerce or one of the others on the Peachtree Partner Site.  A very small lightweght client-side application, connecting to a SaaS system.  Take a look at this case study on the Peachtree by Sage website (Click here).  Faster, better support and long term cost effective.  In these integration scenarios, SaaS really pays dividends in contrast to a roll your own integration project. 

Special kudos to the following for helping me dig on this topic. 

Tom Sechler - Sage Certified Consultant