Mapping Exercise: EDI to Flat file.
Introduction: This is a quick exercise to familiarize you with mapping from an EDI file to a Flat File. If you are new to mapping, or want an idea of what mapping EDI will be like, this exercise should be a good place to start. If you are familiar with mapping this should be a quick review with a few tips. I use Target Based mapping. Check out my post on Target Mapping here. If EDI is unfamiliar and you need some basic information, my EDI primer is here. If you are ready to map, and understand EDI basics, then lets get started.
Mapping:
I practice and advocate the target oriented mapping approach. So we will start with defining the target. We will do this in our 6 column spreadsheet mapping template. This is also referred to as a Paper Map. To download a blank template, click here. Once we have the target defined, we will add the source to the spreadsheet. After both the source and the target have their fields on the Paper Map, we add the rules. We will also need to insert some control structures to make everything flow correctly. Once this is done we will have addressed the data format issues and constructing the map for our translator becomes just a technical exercise.
The Flat File Target:
What are we mapping? This is the first question that the target defines. In this example it is an invoice. So we will want the following information in the following format.
RecordID|VendorID|DateOfInvoice|InvoiceNumber|DateOfPO|PONumber|POLineNumber|QTY|
~UnitCost|LineTotal|InvoiceTotal
Here are the parameters for these values:
RecordID: A unique alpha-numeric. It is derived by contacting the ISA_Sender, ISA_Control_Number, GS_Sender, GS_Control_Number, ST_Control_Number, and Line_Number where the line number exists. Max size of 74 characters.
VendorID: The ISA_Sender ID. Alphanumeric with a max size of 15 characters.
DateOfInvoice: A long date, max 10 character formatted (mmddCCyy)
InvoiceNumber: Invoice number from the Vendor, Alpha numeric, max 22 characters.
DateOfPO: A long date, max 10 character formatted (mmddCCyy)
PONumber: PO number sent on the PO. Alphanumeric, max 22 characters.
POLineNumber: PO line number must be the same as the invoice line number. AlphaNumeric, max 20 characters.
QTY: Quantity of items invoiced. Numeric, max 10 characters.
UnitCost: Cost per unit. Currency, max 17 characters.
LineTotal: Total of line cost. Currency, max 17 characters. sum(QTY*UnitCost)
InvoiceTotal: Total of all line totals. Currency, max 17 characters. sum(all LineTotals for Invoice)
The EDI Source:
We are going to get our data directly from the EDI. We could be doing API calls to resolve VendorID values or getting an incrementing RecordID, but we can get what we need from the EDI under the right circumstances and so that is what we will do here in this example.
For the record ID we are going to do a simple operation and concatenate several values to create a unique value. This will almost always be unique. And it will refer back to the original document as a bonus. If we wanted to ensure that it was always unique we could include data and timestamps in the concatenation.
For the dates, we are converting the date from the EDI format into our preferred format. Many systems understand date format conversions, so it might be as simple as a cast or convert command on the existing string of numbers. If not, we will have to parse out the separate values and then string them back together concatenating in with dashes.
And there are two values that we will need to calculate. We will calculate the value for the invoice total before we create any lines. And we will need to create the line totals. Since these require processing the same data, we might create all of the totals and put them in an array to easy access. Anyway, this is the time to be creative.
Constructing Rules:
Now that we have the source and the target we will makes the rules, and we already have some in mind. We know that we are concatenating the record ID, reformatting the dates, and calculating the totals. We also add in some other rules like the trimming of the space on the ISA_06 to Vendor ID operation. We will also need to do that in the record ID or it will look odd.
Controls:
Controls are like rules, and they may be written in the same place if we need to. But where rules describe how we completed our mapping, controls tell us when to and not to map something. Sometimes mapping is conditional. Controls tell the map what conditions allow for valid mapping when conditions exist.
There may be times that we don’t want the map to go on and complete the process. Obviously if we have missing data. If there is no invoice or PO number, you may want to stop with an exception. Generally these types of controls are easy to build as you make the target field required. To indicate this you may want to place an additional column to indicate what data is required. On simple projects you can just put this in the notes.
There will also be other times. For instance, it might be that some invoices have their own total. If they do not match our calculated total, we want them to fail mapping and go to manual resolution. A use case would be to have additional charges attached to the invoice. This extra line charges would need to be handled outside of our map.
Controls can be written to make the mapping very powerful and provide a gateway to your data that ensures it stays valid and accurate. Some controls go beyond stopping the map. Instead we use them to put conditional logic into our data mapping project.
Summary:
This example project provided a short exercise to familiarize you with using a paper map and the fundamentals of target mapping. In this simple example we had a target specification, and a source specification. We created rules to move and change data from source to target, and we discussed controls. These are the fundamental tasks of mapping.
But this is not the end. I know that as I worked on this project, I saw things that might improve the map. I didn’t include them because I wanted to keep this mapping project simple. But you don’t have to. You can add your own improvements to this project and make it do what you want. I would be happy to respond to these if you post them in the comments.
You can find the resoucres for this project in the tool box.
Subscribe to "The Integration Engineer" by Email
Find out about the tools and services available at The Integration Engineer's Consulting site.
