Home‎ > ‎

An Open Letter to all those about to commit EDI

 Dear fellow IT Director,

 I work for a firm that dropships products to your customers reliably, cheaply, and confidentially. We reproduce your custom designed packing slip, perform QA on every outbound order, and never substitute name-brand goods for knock-offs. We can provide a turnaround of 12-hours or less on business days and inventory feeds updated every 15 minutes so you never oversell our stock (and we never go OOS on the must-haves). We can process your returns and can handle tiny orders, huge orders, bulk freight-shipped orders, partial-fills, custom inserts and international shipping.

 This, of course, means messaging. A purchase order has to be submitted to us somehow, so we handle text, XML or EDI, plaintext or PGP, sent over FTP, SFTP, FTPS, AS2 and VANs. We can do Functional Acknowledgements (997), Order Acknowledgements (855), PO Change Requests (860), Oder Cancel Notifications/Floor Denials (865), Ship Notifications (856), Invoices (810), Inventory Availability Advice (846) and Return Notifications (180). In fact, we cover just about everything that begins with an "8" on the EDI message-type chart.

 But it took a long time to get to the point where we could handle every cockamamie half-assed ass-backwards Rube Goldberg counterintuitive duck-tape contraption you bastards keep coming up with, because each and every one of you keep reinventing the wheel. Just when we've finished spending a good part of a year designing, building and testing a flexible, configurable messaging system, one of you slurps a sheet of blotter acid and designs a PO system that breaks every abstraction we had developed.

 In the course of trying to accommodate our partners' messaging systems, we've encountered:
  1. Excessively overengineered pipe-delimited message formats, with over 100 columns per line in normalized format, so we have to partially parse the line to discover a value in column #2 that tells us what the column mapping is going to be for the rest of the line. These formats are also headerless and come with documentation that doesn't number the column definitions, so we have to print it out and hand-write the column numbers in the margins in order to define and verify the mapping for them
  2. Custom FTP servers that auto-deleted or moved a file after an attempt to download it. This meant that every time a file transfer failed we'd have to phone a support desk and ask for them to restore the file for a second attempt
  3. XML that was clearly a serialization of an internal data structure, with multiple redundancies, counterintuitive structure, and the namespace re-declared in every element
  4. A vendor who wanted us to build a SOAP web service just for them, designed to their spec, and open up our firewall so they could query it at will
  5. Vendors that inform us they're going to go with VCommerce as their VAN and file format, give us enough time to tool-up for it, then take a look at VCommerce's price quote and chicken-out at the last minute to go with a quick-n-dirty messaging system whipped up by a lowball contractor
  6. Sleepy-eyed integration specialists at the VAN who provide a turnaround of about 2 weeks to confirm each messaging test
  7. Where the PO format is an XML schema borrowed from Yahoo! Stores, but the ship-notification, invoice and inventory formats are homebrew CSV
  8. Case-sensitive alphanumeric PO numbers, so DxQV56mn is not the same PO as DxQv56mn
  9. A system that communicates order status by moving files around subdirectories on an FTP site (so if an item is Out-Of-Stock, we have to move the PO file to their "OOS" directory)
  10. PO numbers that get recycled periodically, destroying our silly, naive idea of having a uniqueness constraint on that database column
  11. Fixed-width column formats that aren't wide enough for our order ID numbers, or other values
  12. Orders that want us to ship to a PO box address via UPS ("Brown" can't deliver to PO boxes). This wouldn't be so bad because we just change the ship method to USPS, but then you get messaging systems and accounts payable departments that throw a fit if the ship method changes or there are postage charges they weren't expecting
  13. "Value Added" Networks (VANs) who have more scheduled downtime than a polysomnography lab (Albany, I'm looking in your direction)
  14. Packing slips that can only handle one line-item, quantity of 1, for a web store that has a shopping cart. This means that when someone goes to their web site, puts two items in their cart and sets the quantity of each to 5, we're required to print out ten packing slips and ship each unit in ten separate boxes (their file format is also XML, but the SKU is part of the order header)
  15. Custom GUI applications for submitting message files. We know you were trying to make it easy for Ma n' Pa Kettle, but our volume is thousands of orders a day and we can't automate your .Net WinForms app
  16. Files with static names that get overwritten every hour with whatever orders haven't been confirmed yet, creating duplicates whenever messaging isn't in perfect sync
  17. Providing purchase orders via a POST over HTTPS where the parameters had to include start-date and end-date, but don't support start-dates more than 3 days in the past (which made it difficult to recover when something broke early in a 3-day weekend)
  18. Partners who cannot tell if an order is from a real customer or just one of their own tests
  19. Companies that put their PO files on an FTP server, but require an HTTP GET for each file retrieved in order to confirm them
 When you're a supplier you have to support dozens of multicolored file formats and transfer mechanisms, and each one will have a twist that'd make M. Night Shyamalan drop jaw and stare. 

 "Is the sales tax given as a dollar amount or a percentage, did you include it in the totals or put it in a separate field, and is it per line item or per order?"

 "Uhm... yes."

 Messaging shouldn't be hard. The hard part should be defining the product and how it gets sold, but the above gripe-list is all about stupid designs that did not improve messaging or enable anything new to the business. Here are some tips for making the messaging part easier for you and your partners.

1. You don't need a VAN anymore

 The "Value Added Network" was born in the 1960s when businesses first began to do EDI but the public phone network wasn't adequate, so they ran leased lines to each other through privately owned exchanges. "Hold on," said the Government, "that's a phone network and should be regulated!" But there was a loophole: if the network was more than just a common carrier--if it "added value"--then it didn't need to be regulated, and the main value they added was audit trails*. 

 Physical private networks are obviously no longer needed today, and third parties are no longer needed for audit-trails thanks to a good Public Key Infrastructure.

 A VAN is now an impediment to business:
  1. It adds an enormous cost to each message. Some real-world figures: $5 for an inventory inquiry, $3 for the PO, $6 for the PO acknowledgement, and $2.50 per carton for a ship notification. If you are buying skids-worth of goods at a time then this is tolerable, but it makes the consumer dropship business impossible. Other VANs charge by the KCh (1000 characters), so imagine surfing the web at $0.25 per kilobyte. There is no justification for these prices anymore.
  2. The VAN's interests are not aligned with yours. This is why the above gripefest included the "integration specialist" who took 2-weeks to verify a test, but it also included the wait to have a file restored whenever an FTP transfer failed. And when there's a problem with an order--like an invalid SKU--you have two parties who each think its the other guy's fault
  3. If the VAN defines the file format then they will cram it with every field needed by the partners you're not doing business with, too. This is where the 110-column formats came from. These formats are hard to change, so if you need to exchange other types of information then you must wait for the VAN to expand the spec (and push those useless columns onto everybody else in the network, too)
 What you should consider are third party FTP servers, because these will relieve your IT staff of the administrative and security burden of running one in-house. Look for one that supports FTPS and lets you create as many logins as you like with storage quotas. And never store anything there that isn't encrypted or desensitized.

* - VANs also added message routing, authentication and validation, but modern software again makes these less important to outsource.

2. Chose the simplest file format that works

 There are three basic file syntaxes that everyone uses: CSV (or delimited text), XML, and EDI. These are then used to define a format for encoding inventory advice, purchase orders, acknowledgements, ship notifications, and invoices. You should always chose the simplest format that works, and nine times out of ten that's probably going to be denormalized CSV. 

 "Denormalized" means that if you're giving us a PO with multiple line items then you need to repeat the order header for each line. The overhead this creates is not extreme (text compresses very well), and when we import it we'll group on your order number to reconstitute the PO correctly. Every shop out there has tools for mapping columns to columns and they all assume it'll work this way, so it'll take fifteen minutes for your partner to map three or four different message types. That means faster to test, faster to production, fewer mistakes, and faster to make changes in the future.

 Use XML if you need to pass hierarchical data. This is rare because POs are inherently relational in structure, but consider it if you have to describe a complex product or a complex fulfillment process. If you design an XML format then please do not just serialize your internal data structures; please put some real thought into it. My rule that "Nobody who uses XML knows what they're doing" comes from never seeing an XML messaging format that wasn't either grossly overengineered or bizarrely dysfunctional.

 As an example, I saw one ship-notification format that got the nesting of values back-to front: the line-items were the first level, the order header was at the second level, and the tracking number was at the the third, like this:

    <LineItem LineNumber="1" SKU="123456">
        <Order Number="555444">
            <Package ShipMethod="UPS" Tracking="1Z123467WW53631"/>
    <LineItem LineNumber="2" SKU="654321">
        <Order Number="555444">
            <Package ShipMethod="UPS" Tracking="1Z123467WW53631"/>

 The designer of this schema was on crack.

 EDI is also a hierarchical format, and most shops now translate it to XML as an intermediate step. Sometimes you'll hear people talk about "EDI" as the concept of sending electronic POs in general--regardless of the format (the title of this article, for example)--but most of the time they'll mean ANSI X12. EDI is three formats in one: the base syntax (X12, EDIFACT), the set of standard message types and their revisions, and then the vendor's own tweaks. Do not volunteer to use EDI, only use it if your partner gives you no other choice. EDI cannot do anything better than XML can*, but the tool-chain is expensive and "Enterprisey", putting it out-of-reach for small or under-budgeted shops.

* - With one notable exception: the set of standard EDI message types mean you don't see many acid-trip schemas. The greater plan for EDI was to establish standardized processes for fulfillment, and the file syntax was just one part of it.

3. Don't meddle with file transfer

 Use FTPS to send PGP encrypted copies of messages to-and-from a firewalled drop-box server. All done!

 But, do not:
  1. Implement a custom FTP server that behaves differently than a standard server
  2. Delete or move a file when the partner attempts to fetch it, successful or not
  3. Refuse to honor the DELE command
 One of the VANs we have to work with has a customized FTP server that moves a file to an unreachable location whenever a client attempts to GET it, even if the attempt fails. After a failed transfer resulted in a 48-hour delay fulfilling an order (by that I mean a 48-hour wait for one of their techs to figure out what was wrong and restore the file) I called them and asked them why they did this, and they said--no kidding--that they couldn't let us delete the files ourselves "in case they had to restore it again later". 

 This blew my mind. 

 This is a major VAN and they have the most incompetent implementation of file transfer that I have ever seen*.

 An FTP site, even if it's protected with a firewall and FTPS and all the messages are encrypted, is an inherently insecure site, so you must treat it as a drop-box that you place copies of messages on. It's also at the lower-end of the messaging protocol stack and needs to follow the conventions that fit such a level. If you are using FTP then DELEting a file after fetching it is how I can communicate that the transfer was successful. It's very easy for me to do this because my code is:

    Tranceiver.GetFile(remoteFile, localFile);
    if (File.Exists(localFile) && FileSize(localFile) > 0)
catch (Exception ex)
    logger.Error("Exception thrown when fetching {0}: {1}", remoteFile, ex.ToDump());

 If there was an exception thrown during the transfer then the remote file doesn't get deleted. If the file doesn't exist locally after the transfer then the remote file doesn't get deleted. If the local copy is zero bytes then the remote file doesn't get deleted. If a man in Brazil is coughing then the remote file doesn't get deleted. Furthermore, the file I'm fetching should be a copy and the VAN should be keeping an archive to restore from. So when I do actually issue a DELE command it's my way of saying--at the file transfer protocol level--that I've got the file and it's dandy. Further up the stack we have things like MDNs and Functional Acknowledgements and Purchase Order Acknowledgements and sometimes all three. There is no need to fuck around with file transfer.

* - A month after this article was written they added a tool to their web-portal that restores zealously deleted files, although I'm not sure if I should be happy; it still forces manual intervention when the alternative is a completely automatic recovery of the fault.

4. Use something compatible with OpenPGP

 Don't use a 10-year old copy of PGP. It has published vulnerabilities and has compatability issues with newer, open-source PGP implementations. We use Bouncy Castle, for example, which has an implementation of OpenPGP. Others may use GNU Privacy Guard. All of them have issues with PGP 7.2 and older.

 It's also common to confuse the transport-level security of FTPS or SFTP with the encryption of the message itself. You want to use FTPS to prevent the login password from being sniffed but that isn't the only way to compromise a host, so the messages need to be encrypted to the public key of your trading partner so that a compromise of the host doesn't mean a compromise of the message. It also means you can outsource your FTP server to a third party without worrying about security. In fact, outsourcing your FTP host will improve security by eliminating another IT headache and firewall exception.

5. Use unique, unrecycled case-insensitive PO numbers and IDs

 Fulfillment is messy. Sometimes we have to chase down stock that got misfiled in the wrong bin location, reprint a packing slip that got dropped and muddied, change a ship method to one that can actually deliver to the address specified, and so-on. These all require manual intervention to even the best automated process, and it frequently means somebody has to scan an ID number. If the barcode is unscannable or the workstation doesn't have a wand then it has to be typed by hand.

 Case-sensitive IDs are not going to survive this failure mode.

 They also wreak havoc on databases that were designed years ago on platforms like Microsoft SQL-Server, where varchar columns are case insensitive by default. That means a query on it will fail, joins will fail, and if there was a uniqueness constraint then inserts can fail, too. The IT department will grumble and bitch, put in a ticket for the DBA to change the column's collation to SQL_Latin1_General_CP1_CS_AS and schedule a couple of weeks for all the programmers to go through the code and conditionalize the use of ToUpper().
 And if any of the applications are using a .Net DataTable, forget it.

 We can handle 15 or 16-digit numbers and we don't care if they're alphanumeric. We're fine with hyphens that break-up long numbers. Checkdigits are awesome, too, but don't expect us to code the logic for them. 

6. IT departments aren't like Microsoft

 We don't have infinite resources and we can't dedicate a team to your issue on the same day you decide to change the spec. Furthermore, you're not the only guys who have a new packing slip style or message type to be supported by next Monday.
  • DO NOT bunch us up with waves of vendors who all have to be implemented as a group on the same day. Your staff will be overwhelmed when every single vendor wants to do their testing at the last minute and we won't be able to get the attention we need
  • DO NOT give us only a week's notice for a new style of packing slip you want us to support, and then forget to provide us with the new artwork until a day before the deadline
  • DO NOT schedule a conference call every time a trivial issue comes up. Use email
  • DO NOT plonk an account manager between your engineers and ours and force all communications to go through her. Give us the email address and phone number of your engineer. We can't do our jobs if we have to wait for her to forward email and then try to re-phrase your engineer's technical question in layman speak. She is clueless and will schedule a conference call with 7 people every time somebody wants an IP address
  • DO verify that we received your notifications. Sometimes you'll mail a technical spec to the lady who quit on maternity leave 18 months ago, or to the warehouse manager who hasn't got time to be a human email switchboard

7. Learn how to parse ISO 8601, and other sensible encodings

 ISO 8601 is a standard for expressing date-and-time strings and every modern platform used by IT today (.Net/Mono, Java, Ruby, Python, Cocoa, etc.) can parse it into their native DateTime types. They also spit it out with their respective ToString()s as the default encoding, complete with timezone if it was set. Here's a bit if ISO 8601:

2009-08-19 12:28Z

 The 'Z' at the end is for the Zulu timezone, the ethnically neutral successor to GMT. Now here's the date-time format of a major VAN:

20090819 12:28

 It seems that at first they were all over dates, and had defined an encoding that stripped all hyphens, slashes and other punctuation. But then... somebody needed to add the time! And the engineers said "Oh shit! Well I guess we can add colon-separated hours and minutes after the date, then". This format appears in both their CSV and XML messages.

 If you're lucky then your platform's DateTime.Parse() can figure out this crap and read it without getting the value wrong, otherwise you'll have to do string manipulation on the input. But you'll still have to face the fact that you will have to do formatting on the output, because this VAN's software cannot parse ISO 8601. You have to munge it into their format first.

 This also extends to other encodings for common values:
 For everything else, I beg you to do a Google search before you invent your own encoding. If we have to create a table to map your proprietary numeric country codes then we will make a mistake and ship your best customer's order to East Timor. 

8. Unique filenames, unique contents

 Message files aren't meant to behave like REST URIs. While there's nothing wrong with making REST over HTTP available for us to query, do not try to shove this concept into static files on an FTP site. A file is a message, so it needs a unique name (timestamps or serial numbers are fine) and unique contents. We have partners who thought it was a good idea if "market_orders.txt" was a magic file that always contained every unacknowledged order, refreshed every hour. But all this meant was a constant stream of "duplicate order" exceptions whenever messaging wasn't in perfect sync, or worse, contented for locked resources. 

 It also made it impossible to discuss a problem with a file because you couldn't tell them which file had the problem: it might have been overwritten with new contents by the time you called them up.

 Exposing a REST interface can be very useful to automate error recovery; we can build feedback-control loops to keep messaging in sync. But that doesn't eliminate the need to have every message in a single identifiable package.

9. Put an email address on your case tracking system

 Many partners have wisely implemented a case tracking system, which is fantastic. What they didn't do was give it an email address, so to open a case I have to:
  1. Open up KeePass
  2. Type in the passphrase
  3. Search for your company name
  4. Pick the entry for your web portal
  5. Click on the URL to load it in a browser
  6. Copy-over the username
  7. Copy-over the password
  8. Click on "Manage your case log"
  9. Click on "Create a new case"
  10. Cut-n-paste the email addresses of everyone who ought to be 'cc'ed from Outlook into your form
  11. Enter a description of the issue and click on 'Submit'
 Or I could:
  1. Send an email to edi-issues@yourcompany.com

10. Have a way to flag test orders

 One of the biggest messes I ever saw involved a company that managed eBay listings for you and relayed paid orders via their messaging format. The problem occurred when they sent us an order without a shipping address, "oopsed" over it and told us to disregard because it was a test order. A few weeks later the customer behind that order is asking what the hell happened and giving us negative feedback scores.

 It's normal to run a full-cycle test once you've linked two production systems: you send us a "real" order placed by one of your staff, pay for it, we pack and ship, then we wrap-up with a return-n-refund to proof the whole system. Because these "proofs" go through a production system they need to be flagged as such, so that if something goes wrong then we can relax and approach the problem from a different angle: something went wrong with messaging, not something went wrong with a real customer's order.

 Proof orders are also pathological and tend to hit every branch of the Ugly Tree on the way down: multiple ship-tos, deliberate short-stocks, sales tax, gift messages, returns and refunds, because you want to test all of those conditions. If one of those was to be mistaken for a real order then it might tie up three or four departments in a frenzy instead of just one.

 That's what happens when you mistake a test order for a real one. It's an even bigger mess when the opposite happens. Even if you're just a "Market intermediary" you still need the ability to attach meta-data as it passes through your own system.

The future of EDI

 The classic EDI standard isn't trendy among the kids nowadays; it's seen as a product of the mainframe era: pre-XML, pre-Internet, pre-PKI, pre-Everything. That much has become abundantly clear just from all the different dot-coms who come to us seeking everything except good 'ol ANSI X12. On the downside, no formidable standard has risen to replace it, only a vast wasteland of homemade crap. The tower of babel has fallen once again and as a result we feel like we're playing Twister with rubber-armed mutants and a play-mat with ten thousand colored dots on it, and every color on the spinner is spelled in a different language. And some of them are in EBCDIC. And big-endian.

 I am annoyed that everybody and his uncle feel it's necessary to make a boring and mundane part of business "special". It isn't special, it's plumbing. There is nothing to gain and everything to lose by inventing your own format. Forty years after the invention of EDI and you're still all doing it wrong.