Edi System Translator for Health Care Provider

Hoa Diep

CS491B

EDI SYSTEM TRANSLATOR FOR HEALTH CARE PROVIDER

I. ABSTRACT:

The health care industry in the past and some even now used paper documents as their means of keeping information on their clients. Such methods are very inefficient. It causes the health care industry a lot of money to have their paper work maintained. And yet, problems still arises. Inaccuracy due to human error is one thing, and unavailable information between businesses is another. And so, the industry seeks to improve their accuracy, availability between partners, efficiency due to automation, and the HIPAA (Health Information Portability and Accountability Act) Standardization was required by the government. The government required the health care industry to use EDI for all their transactions. And so, my goal is to demo a simplified version of an EDI System in action.

The EDI System Translator is a system that allows the user, a health care personnel, to open an X12 file and extract the names and addresses of the clients onto a temporary table. The information will then be reviewed and once satisfied be upload into the database. The system itself is dynamic in a sense that it can be modified to extract different information base on its rules. So, basically the information extracted is based on what the rules defines.

II. Technical Requirements:

The technology I’ve used for this system is as follow:

 Visual Basic .NET

This is the language I used to develop this system.

ADO .NET

This technology is used to connect my system to the database.

Microsoft SQL Server 2000

This the server I use for my database.

III. Project Design:

I used Microsoft SQL Server 2000 to create m database. The name of my

database is AddressBook.

III.1 Creating Table:

I’ve created 2 tables for my database, AddressList and FieldRule. The two

tables is as follow:

1. Created table AddressList:

I made AddressID as the primary key.

Create table Address List (AddressID int primary key, LastName varchar(50), FirstName varchar(50), Street varchar(50), Street2 varchar(50), City varchar(50), State varchar(2), ZipCode varchar(10), TSID varchar(3));

Field / Type / Null / Primary key / Default / Extra
AddressID / Int / Yes / NULL / Auto increment
LastName / Varchar(50) / YES / NULL
FirstName / Varchar(50) / YES / NULL
Street / Varchar(50) / YES / NULL
Street2 / Varchar(50) / YES / NULL
City / Varchar(50) / YES / NULL
State / Varchar(2) / YES / NULL
ZipCode / Varchar(10) / YES / NULL
TSID / Varchar(3) / YES / NULL

2. Created table FieldRule:

The primary key for FieldRule is RuleID.

Field / Type / Null / Primary Key / Default / Extra
RuleID / Int / NO / YES / NULL / Auto increment
TSID / Varchar(3) / YES / NULL
SegmentID / Varchar(50) / YES / NULL
SegmentQualifier / Varchar(50) / YES / NULL
QualifierElemNum / Smallint / YES / NULL
ElementNumber / Smallint / YES / NULL
FieldName / Varchar(50) / YES / NULL
TableName / Varchar(50) / YES / NULL
SegmentOrder / Smallint / YES / NULL

III.2 How the System work:

The following will be source codes that is the heart of this system, the

codes will be accompanied by comments explaining what the codes do.

//start going through the file segment by segment and process them according to the following logics:

ForEach Segment In SegmentArray

ElemArray = Segment.Split(ElementTerminator)//Parse the segment into a list of elements or fields, with which the first elem is the segmentID - ElemArray(0)

If newST = TrueThen

DataSet1.Tables("FieldRule").Clear()//clear the fieldRule table in the dataset

DataSet1.Tables("SearchList").Clear()//'clear the SearchList table in the dataset

Me.SqldaFieldRule.SelectCommand.Parameters("@TSID").Value() = tsid.ToString//set the parameter to the select stmt to the new TSID obtained

Me.SqldaFieldRule.Fill(Me.DataSet1, "FieldRule")//execute the select stmt pre-defined in the data adapter to obtain the new set of rules from the fieldRule table in the database and store the returned rows in the FieldRule table in the dataset1

Me.SqldaSearchList.SelectCommand.Parameters("@TSID").Value() = tsid.ToString//set the parameter to the select stmt to the new TSID obtained

Me.SqldaSearchList.Fill(Me.DataSet1, "SearchList")//execute the select stmt pre-defined in the data adapter to obtain the new set of rules from the fieldRule table in the database and store the returned rows in the FieldRule table in the dataset1. distinct list of segments for this tsid

//constructing the searchlist…DataSet1.Tables("SearchList").Rows(0) returns the 1st record of the SearchList table defined in DataSet1.

These records were obtained earlier by the execution of stmt: Me.SqldaSearchList.Fill(Me.DataSet1, "SearchList")

startSegmentID = DataSet1.Tables("SearchList").Rows(0).Item("SegmentID")

//This obtain the segmentID of the first record, and this is the startSegmentID which starts the name and address data block

If DataSet1.Tables("SearchList").Rows(0).Item("SegmentQualifier") Is DBNull.Value Then // determines if the segmentQualifier field

startSegmentQual = "" // of the database record is null, and makes proper assignment.This is necessary because DBNull is considered different type than string, and comparision of DBNull with a string value is not allowed. Conversion of null to empty string ("") is recommended to resolve possible problem in this regard later on.

Else

startSegmentQual = DataSet1.Tables("SearchList").Rows(0).Item("SegmentQualifier")//"NM1".ToString

EndIf

startSegmentQNum = DataSet1.Tables("SearchList").Rows(0).Item("QualifierElemNum")//"NM1".ToString

//DataSet1.Tables("SearchList").Rows.Count returns the total number of records in the SearchList table, and dataSet1.Tables("SearchList").Rows.Count - 1 returns the index of the last record because 9 records would have indice of 0 to 8 (9-1).

endSegmentID = DataSet1.Tables("SearchList").Rows(DataSet1.Tables("SearchList").Rows.Count - 1).Item("SegmentID") //"N4".ToString

IfDataSet1.Tables("SearchList").Rows(DataSet1.Tables("SearchList").Rows.Count - 1).Item("SegmentQualifier") Is DBNull.Value Then

endSegmentQual = ""

Else

endSegmentQual = DataSet1.Tables("SearchList").Rows(DataSet1.Tables("SearchList").Rows.Count - 1).Item("SegmentQualifier") //"NM1".ToString

EndIf

endSegmentQNum = DataSet1.Tables("SearchList").Rows(DataSet1.Tables("SearchList").Rows.Count - 1).Item("QualifierElemNum") //"NM1".ToString

//in this program, the stop segment is the same as the start segment, so the first record in the searchList is used...

stopSegmentID = DataSet1.Tables("SearchList").Rows(0).Item("SegmentID") //"NM1".ToString

If DataSet1.Tables("SearchList").Rows(0).Item("SegmentQualifier") Is DBNull.Value ThenstopSegmentQual = ""

Else

stopSegmentQual = DataSet1.Tables("SearchList").Rows(0).Item("SegmentQualifier")//"NM1".ToString

EndIf

stopSegmentQNum = DataSet1.Tables("SearchList").Rows(0).Item("QualifierElemNum")//"NM1".ToString

//the below obtains the total number of records in the SearchList table and assign that to the searchLCount var.

searchLCount = DataSet1.Tables("SearchList").Rows.Count

If searchLCount > 2 ThensearchSegmentList = Array.CreateInstance(GetType(String), searchLCount - 2)

searchSegmentQualList =

Array.CreateInstance(GetType(String), searchLCount - 2)

searchSegmentQualNumList =

Array.CreateInstance(GetType(Int16), searchLCount - 2)

//Go through the array and assign the segmentid, and segment qual & num if any from data obtained from SearchList table (DataSet1.Tables("SearchList").Rows(<index>))

For i = 0 To searchSegmentList.GetLength(0) - 1

searchSegmentList(i) =

DataSet1.Tables("SearchList").Rows(i + 1).Item("SegmentID")

If DataSet1.Tables("SearchList").Rows(i + 1).Item("SegmentQualifier") Is DBNull.Value ThensearchSegmentQualList(i) = ""

ElsesearchSegmentQualList(i) =

DataSet1.Tables("SearchList").Rows(i + 1).Item("SegmentQualifier")

EndIf

searchSegmentQualNumList(i) =

DataSet1.Tables("SearchList").Rows(i + 1).Item("QualifierElemNum")

EndIf

newST = False

rw2 = DataTableTemp.NewRow()//This creates new TempAddrList record, ready for storing name and address data to be extracted

EndIf

SelectCase ElemArray(0)//The 1st item in the elem list is the segmentID: ElemArray(0)

Case "ST".ToString //start new ts

startFound = False

If endFound = FalseThen//indicates that start of block is encounterred, but end of block is not encounterred yet, need to write last extracted block of data before starting new one....

rw2.Item("TSID") = tsid //assigns current tsid value to record before inserting

DataTableTemp.Rows.Add(rw2)//insert new record into TempAddrList table in DataSet1. Same as stmt: DataSet1.Tables("TempAddrList").Rows.Add(rw2)

EndIf

endFound = False

If tsid > ElemArray(1) Then//1st elem after segment id of ST segment is tsid value, and this determines if current tsid is same as new tsid just encounterred. No need to obtain new rule set and searchList if tsid are the same

last ts is different than current ts, so need to load new rule set based on current tsid

tsid = ElemArray(1)

newST = True

EndIf

Case startSegmentID //If currently processed segment is startsegment

If startSegmentQual.ToString() = ElemArray(startSegmentQNum) Then

If startFound = TrueThen

rw2.Item("TSID") = tsid

DataTableTemp.Rows.Add(rw2)

rw2 = DataTableTemp.NewRow()//start a new data block record

EndIf

//parse and get new data from start segment

ForEach rw In DataSet1.Tables("FieldRule").Rows

IfNot (rw.Item("SegmentQualifier") Is DBNull.Value) And startSegmentQual > "" Then

If rw.Item("SegmentID") = ElemArray(0) And rw.Item("SegmentQualifier") = startSegmentQual Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then //matched!Asign values...

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Elserw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

//else extract data only if no qualifier is defined in rule record

ElseIf rw.Item("SegmentQualifier") Is DBNull.Value And startSegmentQual = "" Then

If rw.Item("SegmentID") = ElemArray(0) Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then//When no Qualifier is defined and SegmentID matched ! Assign values...

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then//will concatenate when has value...

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

endFound = False

startFound = True

Else//not end segment, so treat like others segments, but skip if ISA or GS or none segment

If ElemArray(0) > "" And ElemArray(0) > "ISA" And ElemArray(0) > "GS" Then

For i = 0 To searchLCount - 2 – 1

If searchSegmentList(i) = ElemArray(0) And searchSegmentQualList(i) > "" And searchSegmentQualList(i) = ElemArray(searchSegmentQualNumList(i)) Then

searchSegmentQual = searchSegmentQualList(i)

//segment matched - extract data by go through whole rule list and try to find the match

ForEach rw In DataSet1.Tables("FieldRule").Rows

IfNot (rw.Item("SegmentQualifier") Is DBNull.Value) And searchSegmentQual > "" Then

If rw.Item("SegmentID") = ElemArray(0) And rw.Item("SegmentQualifier") = searchSegmentQual Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then//matched!Asign values...

//will concatenates when has value...

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

ElseIf rw.Item("SegmentQualifier") Is DBNull.Value And searchSegmentQual = "" Then

If rw.Item("SegmentID") = ElemArray(0) Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then//matched!Assign values...

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) =String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

ExitFor

EndIf

Case endSegmentID//currently processed segment is end segment

If endSegmentQual = "" Or (endSegmentQual > "" And endSegmentQual.ToString() = ElemArray(endSegmentQNum)) Then

If startFound = TrueThen

//parse & get new data from end segment

ForEach rw In DataSet1.Tables("FieldRule").Rows

IfNot (rw.Item("SegmentQualifier") Is DBNull.Value) And endSegmentQual > "" Then

If rw.Item("SegmentID") = ElemArray(0) And rw.Item("SegmentQualifier") = endSegmentQual Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then//matched! Assign values...

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

ElseIf rw.Item("SegmentQualifier") Is DBNull.Value And endSegmentQual = "" Then

If rw.Item("SegmentID") = ElemArray(0) Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

//insert address rec because the end segment completes the data block, should continue by looking for start of block after insert into TempAddrList table

rw2.Item("TSID") = tsid

DataTableTemp.Rows.Add(rw2)

//create new data block var and ready for storage of new data block if found

rw2 = DataTableTemp.NewRow()

startFound = False

endFound = True

EndIf

Else//not end, so treat like others - just extract data if match is found in search List of rules

If ElemArray(0) > "" And ElemArray(0) > "ISA" And ElemArray(0) > "GS" And startFound = TrueThen

For i = 0 To searchLCount - 2 – 1

If searchSegmentList(i) = ElemArray(0) And (searchSegmentQualList(i) > "" And searchSegmentQualList(i) = ElemArray(searchSegmentQualNumList(i)) Or searchSegmentQualList(i) = "") Then

searchSegmentQual = searchSegmentQualList(i)

//segment matched - extract data

ForEach rw In DataSet1.Tables("FieldRule").Rows

IfNot (rw.Item("SegmentQualifier") Is DBNull.Value) And searchSegmentQual > "" Then

If rw.Item("SegmentID") = ElemArray(0) And rw.Item("SegmentQualifier") = searchSegmentQual Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then//matched! Assign values

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

ElseIf rw.Item("SegmentQualifier") Is DBNull.Value And searchSegmentQual = "" Then

If rw.Item("SegmentID") = ElemArray(0) Then

If rw.Item("ElementNumber") < ElemArray.GetLength(0) Then

If rw2.Item(rw.Item("FieldName")) Is DBNull.Value Then

rw2.Item(rw.Item("FieldName")) = ElemArray(rw.Item("ElementNumber"))

Else//concatenate

rw2.Item(rw.Item("FieldName")) = String.Concat(rw2.Item(rw.Item("FieldName")), " ", ElemArray(rw.Item("ElementNumber")))

EndIf

ExitFor

EndIf

EndSelect

III.3. Creating the Interface:

Using Visual Basic .NET, I created the form that will be used throughout

for my system.

I created 3 pull down menus, File, Edit, and Help

The File menu contains the Load, and Quit option. If you click on the Load option, it open up another window that lets you look through the harddrive to find the file you wish to put into the database. And the Quit option basically ends the application. I have also made some quick key buttons to activate the two options. If you press Crtl+L, it will active the Load option. And if you press Crtl + Shift + Q, it will active the Quit option.

The next picture shows the Edit pull down menu.

The Edit pull down menu have two options as well, Clear and Update Changes. The Clear option’s function is to erase the information stored in the Staging Area of the application. It will not do anything to the Database Records. The Update Changes option on the other hand, doesn’t do anything to the Staging Area, what it does is update whatever information was entered or modified on the Database Records. I made only one quick key button for this pull down menu. I did not make a quick key button for the Update Changes Option because I felt it is the most important part of the system, and so it should not be so easily activated. And so that leads to the other option, Clear, to activate the Clear option just press Crtl + C.

And then there’s the four button on my system, Load File, Clear, Insert into Database, Update Changes.

The following picture shows what happens when I click on the Load File button.

Once the Load File button is clicked, another window pops up just like what the picture illustrates. The Load File button does the same thing as the Load option in the File pull down menu. The reason I created this button was to make it easier to access.

The next picture is of the Clear button.

The Clear button does exactly the same thing the Clear option in the Edit pull down menu.

Next two pictures shows what the Insert Into Database does.

You notice that there is a record in the Staging Area section of this system. What the Insert Into Database button does once clicked is insert into the record it contains into the Database Records and then remove the record from the Staging Area section. The next picture will show this.

You notice how the Staging Area is once again emptied and the Database Records has another record. The next two pictures will show what happens when I use the Update Changes button.

You notice that one of the record is missing, its because I deleted it. But unless I press the Update Changes button the record will not be permanently be removed. The next picture will show what happens when I close the system without pressing the Update Changes button.

The deleted record has return, that’s the reason why we must remember to update our changes when we modified something in this system and wish to keep the modification.

IV. How to Compile the Source Code

Its rather straight forward for Visual Basic .NET, all I had to do is load the .sln file. In my case, it’s the test.sln file. Once it is loaded, I make sure that any other solution being used is released. Then I would go to the Build pull down menu, click it and then choose the rebuild Solution option. It then builds me a test.exe file in the release folder which is located in the project space folder that I have created for this project. The test.exe itself is fully functional.

V. Conclusion:

The EDI System Translator can be a very useful tool for the health care industry. By allowing businesses to obtain information from other businesses in matters of seconds saves them a lot of time and money. With this design, obtaining different information from a transaction set is made simple by the fact that all we need to do is alter the field rule a little and adjust the appropriate table. Without the field rule, trying to get different information from a transaction set might require a lot of hard coding. And so, this way also saves time, and effort.

VI. Sources: