Tackling the tricky 5% of EDI projects

Introduction

Most new EDI projects are fairly straightforward to deliver providing the people involved are familiar with the technologies involved, and have a reasonable amount of experience.

95% of the time, the files are in EDI or XML format and are exchanged using well established methods like FTP, AS2 etc. This what most EDI/integration systems are designed to handle, so for these you can be pretty sure of a successful and cost-effective outcome using whatever EDI system your business has implemented.

The remaining 5% of potential projects tend to never be delivered. Perhaps they involve potentially troublesome formats like Excel spreadsheets or PDFs, or new software needs to be developed perform some task that’s beyond the scope of the EDI system.

They never come to fruition usually because the return on investment wouldn’t be good enough, but that needn’t be the case.

With the right tools working alongside your existing EDI system, a good part of that 5% can be delivered at a cost that finally makes them a practical proposition.

In this article, I’ll describe some real-world scenarios where this was the case.

Incoming PDF invoices

The starting point here was a trading partner (a supplier) who had no EDI system in place, so the usual EDIFACT, ANSI, XML approaches were simply not an option for them. Instead, this supplier’s systems generated significant numbers of PDF invoices which were then automatically sent to the customer.

Those PDFs where generated by software used by the supplier, rather than being scanned versions of paper invoices. This was good news as it made them potentially machine-readable.

If it could be done at a reasonable cost, it would save the customer many hours of manual effort each week and eliminate the problems caused by data entry errors.

The challenge was to do the following:

1. Accept the PDF invoices for processing
2. Extract the text they contain
3. Parse that text to produce usable data (XML files in this case)
4. Pass the resulting XML data to the customer’s existing EDI systems for processing.

The customer spoke to the company that supported their EDI systems, who were very professional and quickly understood what they needed. Unfortunately, the quote they came back with was for multiple days of custom development and an increase to the annual support costs. This meant that the return on investment would be pretty poor, so the customer was faced with handling the PDF invoices manually in the long term.

The customer eventually discovered a newly developed cloud-based EDI system and elected to use it alongside their existing EDI system for this one specific task, mainly because of its broad range of functions and its Azure style billing option. This involved a low monthly subscription plus a modest “pay only for what you use” approach.

It’s built in range of actions meant that it could do most of the job out of the box. The only development needed was a small script to parse the text extracted from the PDF files. That script was developed and delivered in less than two days, finally making the project a very cost-effective option for the customer.

Most importantly, there was no need to make expensive and potentially risky enhancements to their existing EDI system, which had been in place and working very reliably for many years. It was simply presented with data originating from the PDF files in a form that it could easily handle and integrate it into the business systems, much like all other EDI files.

On-line Business Customers

The company involved here already had an EDI system in place, which they used for their larger customers, as well as a web-based system that allowed them to accept orders directly from consumers. This meant that the majority of the business, at both ends of their market, was already very well served and working reliably.

What they were missing was something for a small but significant section in the middle. Business customers who couldn’t do traditional EDI but would not be well served by the web site designed for consumers.

What the business needed to complete the picture was:

1. A system that would allow their sales representatives to record orders they’d taken from small retailers as part of a sales visit.
2. A system that would allow smaller customers to enter orders directly.

This meant that there would often be a delay in starting the processing of a customer’s order following a sales visit. Very occasionally, data entry errors would occur and when they did, they had the potential to harm the relationship with that customer.

It also meant that the company had to maintain a significant number of people in their telesales department to accept and record customer orders by phone and email. Again, this wouldn’t always work flawlessly and errors or misunderstandings had the potential to impact the business relationship with the customer.

For the company to develop or source a completely new system for such a small section of their market would have been prohibitively expensive, so there was apparently no easy way of addressing the gap.

Fortunately, they discovered an on-line customer order system designed specifically for this purpose. It allows restricted logins to be created that would allow a sales representative to compose and submit a customer’s order while they were still with them at their premises. They could then check it through with the customer and ensure that it was absolutely correct before submitting the order.

This meant that the customer’s order would often be in picking before the sales rep had made it as far as their car.

It also allowed the company to create logins for a specific customers so that they could compose and submit their own orders at any time they needed, rather than e-mail their requirements or wait until the telesales team started work the next day.

The improvements in speed and accuracy completed the picture for the company and allowed it to realise cost savings that would otherwise have been out of reach.

The mapping “gotcha”

This project started off like most others. A new customer wanted to receive EDIFACT invoices from the supplier, and the supplier already had a robust system in place for generating EDI invoices for other customers.

The specification provided by the customer was standard enough, but as the saying goes, the devil’s in the detail.

In this case, the customer had several stores around the country but hiding in the specification was a stipulation that had not been immediately noticed. It said that one INVOIC EDI message could contain multiple invoices, but each of those invoices must be for the same store.

This presented a problem since the supplier’s business systems were setup to export invoice data at customer rather than store level, so one invoice file could contain invoices for multiple stores.

To modify the export process so that for this one customer, it would export each store as a separate file would have involved some rather expensive software development that had not been planned, and for which no budget existed.

The company spoke to their EDI software provider who told them that it had no standard facility to split the files by store, although this could be developed as an additional process at the company’s expense.

Fortunately, they discovered a system had a “Split the file” function that can do this quite easily. By setting this up, and identifying the location of the branch code, that system was able to quickly split a file of invoices into as many parts as there were different stores.

It was implemented by configuring the company’s EDI system so that the invoice files for that one customer would be routed to the other EDI system. That would then perform the splitting process and pass back the individual “store invoices” files for mapping.

The existing EDI system could then process these individual files as normal in the knowledge that none of the resulting EDIFACT messages would contain invoices for more than one store, and that those invoices were being sent in the most efficient manner.

Most importantly, there was no setup or development costs involved. The company simply paid a modest and fixed monthly subscription for the service which would allow them to process as many files as they needed.

The Spreadsheet Partner

This is another example involving a trading partner who does not have their own EDI system. Previously, they’d been receiving orders via e-mail and returning ASNs and invoices in the same way.

These took a significant amount of manual work to handle, and the situation became worse over time as the volumes of orders increased.

The company had a meeting with the supplier and explained the need for an automated approach to orders, ASNs and invoices. The supplier agreed but was concerned that getting their own EDI system up and running will not be a quick thing to do. It was also something that they’d not budgeted for that year.

They agreed that in the short term they’d do this:

1. Orders will be e-mailed to the supplier as Excel Spreadsheets.
2. Those spreadsheets would show the ordered quantities and have empty columns where the supplier can enter return information like delivery quantities, substitute product codes etc.
3. The supplier would complete the spreadsheets and sent them back as an ASN.
4. Once the delivery had been made, other empty columns in the order spreadsheet would be completed to show the invoiced quantities and actual unit prices.
5. Again, the spreadsheet would be returned to the company as an invoice.

This sort of process would present quite a challenge for most EDI systems, but the IT manager had heard about a new system that could do it quite easily.

The first step is to configure the existing EDI system so that the order data for this supplier gets routed to the other EDI system.

It will then perform an “Excel Export” to generate the order spreadsheet with the additional columns for the supplier to complete. That spreadsheet was then delivered to the supplier.

Once the ASN spreadsheet came back, an “Excel Import” function was used to convert it into XML format. That XML was then be passed to the company’s existing EDI system for mapping.

Similarly, when the invoice spreadsheet came in, another “Excel Import” converted it to XML which could then passed back to the existing EDI system for processing.

From the point of view of the existing EDI system, orders for this supplier were being passed to the new system and then later, XML files containing ASN and invoice information would come back. The existing EDI system didn’t need to be concerned with the spreadsheets themselves, this was something handled by the new system for a fixed monthly charge.

This gave the company what it urgently needed, a fully automated flow of orders, ASNs and invoices much sooner than if it had waited for the supplier to implement a traditional EDI system.