The purpose for this page is to describe how one can easily create an EDI translator. Although the
Framework EDI component, which will be used in this example is supported by many programming
languages, the source code examples will be in Visual Basic Script in MS Access. For the sample EDI file, we will use the EDI X12 850 Purchase
Order.
Requirements
The following are required to create an EDI translator:
1. An
Implementation guideline of your trading partner. An implementation
guideline is a document with a set of rules defining how EDI files should be
processed. For more details, please see http://www.edidev.com/articles/createIG/HowToCreateIG.html
2. A programming
language that supports COM or .NET components such as Visual Basic 6, VB.NET,
C++, C#, Delphi, FoxPro, VB Scripts (Access and Excel) etc.
The following points are
important to know about an EDI file when developing an EDI translator:
1. An EDI file is
made up of data segments, which in turn are made up of data elements.
2. An EDI file is
sectioned into tables (or areas): the header (or Area 1); the detail (or Area
2); the summary (or Area 3)
3. A loop is a
group of segments, and a loop can have other sub loops under it.
4. A data segment
can be uniquely located by its Area, Loop Section and ID
5. Instances of
segments or loops can be identified by their qualifier or entity identifier
respectively.
Setting up the
Framework EDI component
To install Framework EDI, simply run the installer and then follow the
instructions on each screen. For more details, please read Installing
Framework EDI.
Once Framework EDI is
installed, the COM or .NET component has to be referenced by the programming
language, which is done a number of ways. Please click on the programming
language listed for their instructions: Visual
Basic, Delphi,
C++,
.NET
Programming Overview Below are the steps we will take in creating an EDI translator.
Design the
destination
database.
Modify SEF file to
include trading partner's conventions.
Write mapping program
to get data from EDI file, and save them into a database.
Designing a Database
EDI is a medium for data exchange, while a database is a medium for data
storage. So it only makes sense that a database would be the destination
for data after the EDI translation (even if it's an intermediate database).
A database design can be
the difference of either having a simple mapping algorithm or a complex,
spaghetti type mapping algorithm. A good database design would have a
simple, one-on-one, parallel relationship
with the EDI file schema. For more about the similarities between a
database and EDI file structure, please see http://www.edidev.com/edi2database.htm.
Below is an example of a
database design for an EDI X12 850 4010 schema.
The Standard Exchange
Format (SEF) file
A SEF file is used to convey an implementation guideline in a standard format so
as to make it possible for computers to read and obtain the schema of a
corresponding EDI
file from it.
A SEF file is required by
the Framework EDI component so as to enable it to process a corresponding EDI file
correctly. So before we can translate an EDI X12 850 4010 file, we would
need the corresponding X12 850 4010 SEF file for the Framework EDI component.
Load the SEF file and
EDI file into the EDI document object.
Traverse through each
segment of the EDI document object.
Identify and interrogate
each segment to get the values from their data elements.
Map data elements to
database fields.
Instantiating an EDI
Document Object
To instantiate an EDI document object of the Framework EDI component that will
represent the EDI file in object-oriented programming, do the following:
Dim oEdiDoc As Fredi.ediDocument
Set oEdiDoc = New Fredi.ediDocument
Loading the SEF file
and EDI file
When the SEF and EDI files are loaded into the EDI document object, the EDI file
will get parsed automatically into identifiable units such as data segments and
data elements making it possible to reference them during mapping. To load the SEF and EDI
files, do the following:
Traversing through the
EDI file
To traverse through the entire EDI file, we have to start from the top, and then
iterate through each segment until there are no longer any segments. This
can be done programmatically by doing the following:
Set oSegment = oEdiDoc.FirstDataSegment
Do While Not oSegment Is Nothing
Set oSegment = oSegment.Next
Loop
Identifying Data
Segments
Data segments can be uniquely identified by checking its Area, LoopSection and
ID. Therefore conditions can be included into the traversing loop to locate the
data segments we wish to interrogate. For example, to find the BEG
segment, (which is located in Area1, and is not under any loop) we would add the following
lines and conditions into the traversing loop.
Set oSegment = oEdiDoc.FirstDataSegment
Do While Not oSegment Is Nothing nArea = oSegment.Area
sLoopSection = oSegment.LoopSection
sSegmentID = oSegment.ID
If nArea =
1 Then
If sLoopSection = "" Then
If sSegmentID = "BEG" Then
'get data elements
End If
End If
End If Set oSegment = oSegment.Next
Loop
Mapping Data Elements
to Database fields
Once the data segment of interest is located, we interrogate it to obtain the
values from its data elements by simply calling the DataElementValue
method. For example, to map the data elements of the BEG segment to their
corresponding fields we do the following:
Set oSegment = oEdiDoc.FirstDataSegment
Do While Not oSegment Is Nothing
nArea = oSegment.Area
sLoopSection = oSegment.LoopSection
sSegmentID = oSegment.ID
If nArea = 1
Then
If sLoopSection = "" Then
If sSegmentID = "BEG" Then
oRsPOMaster("PONumber").Value = oSegment.DataElementValue(3)
oRsPOMaster("PODate").Value = oSegment.DataElementValue(5)
End If
End If
End If
Set oSegment = oSegment.Next
Loop
Identifying a Loop
Instance
A loop is a group of segments with inter-dependant data to convey an
information. Loops can be repeated to convey another set of information
that has a similar structure. Each repetition of a loop is called an
instance. An example of loop instances are the the "Bill-To" and "Ship-To"
address loop information of the N1 Loop. To
identify the loop instances, we check the code value of their loop entity
identifier data element. To iterate through loop
segments, we do the following:
If nArea = 1 Then
....
If sLoopSection = "" Then
...
ElseIf sLoopSection = "N1" Then
'Obtain the
entity identifier of the loop to determine the kind of information the
segments in the loop holds
If sSegmentID = "N1" Then
sEntity = oSegment.DataElementValue(1)
End If
If sEntity = "BT" Then 'Bill To
Information loop
If sSegmentID = "N1" Then
oRsPOMaster("BillToName").Value = oSegment.DataElementValue(2)
oRsPOMaster("BillToID").Value = oSegment.DataElementValue(4)
ElseIf sSegmentID = "N3" Then
oRsPOMaster("BillToAddress").Value = oSegment.DataElementValue(1)
ElseIf sSegmentID = "N4" Then
oRsPOMaster("BillToCity").Value = oSegment.DataElementValue(1)
oRsPOMaster("BillToState").Value = oSegment.DataElementValue(2)
oRsPOMaster("BillToZip").Value = oSegment.DataElementValue(3)
End If
ElseIf sEntity = "ST" Then
'Ship To Information loop
If sSegmentID = "N1" Then
oRsPOMaster("ShipToName").Value = oSegment.DataElementValue(2)
oRsPOMaster("ShipToID").Value = oSegment.DataElementValue(4)
ElseIf sSegmentID = "N3" Then
oRsPOMaster("ShipToAddress").Value = oSegment.DataElementValue(1)
ElseIf sSegmentID = "N4" Then
oRsPOMaster("ShipToCity").Value = oSegment.DataElementValue(1)
oRsPOMaster("ShipToState").Value = oSegment.DataElementValue(2)
oRsPOMaster("ShipToZip").Value = oSegment.DataElementValue(3)
End If
End If
End If
End If
