Troubleshooting > Handling 'Special' HL7 Fields

Certain areas of HL7 can require special consideration when you are trying to extract information from HL7 messages you have imported (see Importing HL7) or when you are creating HL7 messages to be exported. You should be able to determine which (if any) of the fields might require special handling by referring to your trading partner's HL7 document These are:

•HL7 Repeating Fields - An HL7 data field which contains more than 1 set of data.

Many HL7 fields CAN be 'repeating' fields but in actual practice few are actually implemented this way. For instance, HL7 field #9 of the PID (Patient Information) segment which is traditionally defined as the "Patient Alias" field. The HL7 data type for this field is usually a tradition XPN (Persons Name) data type BUT if a person has more than 1 alias then the field could contain more than 1 set of data. Example: Consider the famous American 'wild west' outlaw Billy the Kid, he went by many aliases like William H Bonney, William Henry McCarty and Henry Antrim to name a few. If you received an HL7 message for him then the 'Raw Value' of field 9 of the PID segment might look like this (note the repeat delimiter in RED):

Raw Value: Bonney^William^H~McCarty^William^Henry~Antrim^Henry

This field contains 3 separate names while in the <prefix>_Segment_PID_A table field 9 only contains columns for 1 name (PID_F9_C1, ..C2, ..C3 etc.). Our quandary here at HermeTech has always been how do we resolve this issue without either a) Creating even more schema tables OR b) Creating even more data columns in the existing schema tables. Both of those options would exponentially increase the amount of difficulty in navigating your HL7 data (whether for extraction or creation) as well as place a much greater strain on the SQL Server in the form of additional indexing, table space etc. All for the sake of handling only a very few (or none) of the HL7 message segment fields.

The solution is the C0 column. In every segment table in your database schema there is a C0 column for every HL7 data field. in the <prefix>_Segment_PID_A table field 9 in addition to the regular component data columns (PID_F9_C1, ..C2, ..C3 etc.) there is also a column named PID_F9_C0. It is defined as a 'Text' (blob) column which allows NULL values. WHENEVER an inbound processor detects that any HL7 data field contains repeating data it will place the ENTIRE RAW VALUE (see above) into the appropriate C0 column and then puts the FIRST data set into the appropriate regular data columns. So for the raw value shown above in the <prefix>_Segment_PID_A table the data in the columns would look like this:

•PID_F9_C0 = Bonney^William^H~McCarty^William^Henry~Antrim^Henry

•PID_F9_C1 = Bonney

•PID_F9_C2 = William

•PID_F9_C3 = H

If the raw HL7 data DID NOT contain repeating data (ie like this: Bonney^William^H) then the data in the columns would look like this:

•PID_F9_C0 = NULL

•PID_F9_C1 = Bonney

•PID_F9_C2 = William

•PID_F9_C3 = H

For processing inbound HL7 messages you will need to check if the C0 column contains data. If it does then your process should process the RAW value extracting the data elements manually. If the C0 column is empty then you can simply extract your data from the appropriate data columns (..C1, ..C2 etc).

For creating OUTBOUND HL7 messages in the schema tables. You must follow the same rules for creating repeating HL7 field data. Using the same raw HL7 value as an example of what you are trying to create. Your process for creating the HL7 message would place the raw value in the PID_F9_C0 column and then the first data set into the appropriate C1, C2, C3 columns etc. If your data does NOT contain repeating fields then do NOTHING to the C0 column (ie leave it as NULL or empty).

•HL7 Sub-Component data - A single component of HL7 data field which contains more than 1 set of data typically delimited with a & character.

HL7 Sub-Component data is very similar to HL7 Repeating fields in that it involves a situation where the schema table only contains 1 column for data which might contain multiple values. In today's cellular, social media world one way that your trading partner might use sub-component data is in HL7 fields designated for telephone numbers or contact information perhaps defining a component to contain multiple contact preferences.

Example contact field raw HL7 data: |(254) 549-0825&(254) 555-1212&(254) 999-8888&@MyTwitter^Work&Fax&Cell&Twitter|

The solution is the C0 column. Follow the same rules for processing HL7 Sub-Component Data as you do for HL7 repeating fields. WHENEVER an inbound processor detects that any HL7 data field contains sub-component data it will place the ENTIRE RAW VALUE (see above) into the appropriate C0 column and then puts the FIRST data set into the appropriate regular data columns. So for the raw value shown above in the <prefix>_Segment... table the data in the columns would look like this:

•Column .._<fld#>..C0 = (254) 549-0825&(254) 555-1212&(254) 999-8888&@MyTwitter^Work&Fax&Cell&Twitter

•Column .._<fld#>..C1 = (254) 549-0825

•Column .._<fld#>..C2 = Work

•Data Truncation - Occurs when the component value received in an HL7 message segment field is too small to fit into the appropriate database column. When the Schema Engine imports HL7 messages it dynamically checks the database column size at runtime to determine if the component value will fit into the field. If the component data is too large it will automatically be truncated to the maximum size the field will allow. What happens next is determined by your schema settings (Error Handling and Logging).

Programming Tip: At any time you can manually alter the defined size of the data columns in the segment data tables. So if you think that the Schema Engine did not make a field large enough then feel free to use SQL Server to change the field size yourself. If you are unsure how large to make a column then one idea is to change the SQL data type to 'Text' which will handle anything. Before doing this you should remember that you will be henceforth unable to use that field to 'Query' the database unless you are using the SQL feature 'Full Text Indexing' (refer to your SQL Server manuals for more information).