Exporting Dynamics GP Purchase Orders: It's Much Trickier Than You Might Think

Two and a half years ago, a client asked me to help them export purchase orders out of Dynamics GP.  They work with third-party warehouses and logistics services that receive their inventory and also fulfill customer orders.  Okay, no problem, a PO export.  Famous last words, right?

Well, to this day, I am still updating and refining the PO export to properly send data to the third party warehouses and trading partners.  I've learned that exporting purchase orders from Dynamics GP can be a very tricky proposition.

There are three primary reasons why exporting a PO is difficult.

First, Purchase Orders in Dynamics GP are dynamic documents.  They are not posted transactions like a journal entry or payables invoice.  They can be created, edited, have lines added, quantities changed, quantities cancelled, lines received, lines closed, and lines cancelled.  And they can have different statuses, such as new, approved, released, and change order.  There are a lot of ways that a PO can be changed.  And since they can change constantly, they are a moving target.  You can't just export a PO right after it is entered and consider your work done.  Sure, some companies may have very simple PO procedures, but my client is not one of those companies.  They have hundreds of lines on their POs, and those POs constantly change in nearly every way imaginable.

Second, Dynamics GP doesn't have a means to tell you exactly what was changed on a purchase order--or any transaction or record, for that matter.  The eConnect Requester can detect that a PO was inserted, edited, or deleted, but that's about it.  It doesn't tell you if a comment was changed, a line was added, or a line had a partial quantity cancelled.  It doesn't tell you if the PO went from new to released.  It's up to you to figure that out.  Similarly, it's up to you to figure out which POs you have already exported.  And which lines.  If one line on a 300 line purchase order is updated, you probably only want to send the single updated line, not all 300 lines.

Third, different systems have different ways of handling purchase orders.  If you are exporting POs from Dynamics GP and sending them to another company, their system will very likely have different rules and requirements than Dynamics GP.  You will likely need to do some things with your PO data to accommodate the external system.  If the quantity ordered on a line is changed from 15 to 20, do you export a "change" with a quantity of 20?  Or do you send just the difference--a change with a quantity of 5?  If you have a PO line with quantity 20, and cancel a quantity of 5, do you send a cancellation?  Or a change?  And what quantity do you send?  5?  -5?  The difference of 20 - 5 = 15?  And if you have already received 10, do you net out those receipts?  So 20 - 10 - 5 = 5?  It all depends on the receiving system.  And if you are sending PO data to multiple external entities, you can bet that they will each have slightly different requirements.  (Trust me!)

And don't forget that there's more to it than just exporting the data.  You need to pick a file format.  TXT?  CSV?  XML?  XLS?  Windows or Unix line terminators?  Yes to all of them!  What file naming conventions do you use for each trading partner?  And what about actually sending the file automatically?  Drop it on your FTP site?  Upload it to their FTP site?  How about their Secure FTP site?  And even better, a secure FTP site that uses public keys for a login instead of a password?  And then there is good old e-mail for those trading partners that have manual processes.  Naturally, some will want the PO data sent to them hourly.  While others will only want it once per day.  And what if a company says they didn't get the data from last Thursday?  Yup, you will need to be able to re-export or re-send the POs that were added or modified last Thursday.

Get the drift?

It's much trickier than you might think...


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com