HL7 Repeating Fields

Important: This section deals with HL7 repeating fields (and also HL7 Field SubComponents which are handled in the same way), a working knowledge of what repeating fields are and how they work within HL7 is needed to understand some of the concepts in this section. You should also be aware of HOW the Schema Engine builds and populates the database tables and how those tables are laid out. So if you haven't done so already you should do the following before reading further:

Give the section on Navigating the Schema a thorough read-through. You need to understand how the Schema Engine creates the database tables and in particular the part that the EasyHL7 Vendor Definition plays (along with what HL7 Data Types are and the part that they play) in this process (you choose the EasyHL7 Vendor Definition when you create your Schema Profile).

Play with the Schema Engine a little by importing some HL7 messages (even if your main goal is to produce outgoing messages). There is a sample .hl7 file that installed with the SQL Schema Engine and will be in the same folder that the application installed into which will help you do this. Examine the data in the tables, compare it to the raw HL7 data that was imported and generally try and get a feel for how the SQL Schema Engine works.

3.	If you're stuck, contact our customer service department at info@hermetechnz.com and ask for someone to call you back and walk you through it. We're always happy to help even if you're just evaluating the product in DEMO mode.

A brief history: If you're reading this section, you are probably aware that the EasyHL7 MS SQL Schema Engine has never really handled HL7 repeating fields or subcomponent values very well. That's right, we said it right here in writing. We are fully aware of this shortcoming and the problem lies in the classic argument of "the needs of the many vs the needs of the few". The MS SQL Schema Engine is a VERY popular product, one of our biggest sellers in fact. In fact, despite HermeTech's low market profile it's probably one of the top-selling products of it's kind in the world. How do we know this? Well, most other products which import HL7 data into MS SQL Server sell for well over US$10,000.00 while ours sells for a full retail price of only US$500.00. We know how many we're selling and if most of these other companies were selling significantly greater quantities at those prices their companies annual income would be much higher than reported.

We attribute the popularity of this product to 2 things: Ease of Use and Price. Of the two, we firmly believe (and all of our customer feedback confirms this) that the absolute MOST IMPORTANT feature is the products ease of use. We could charge more for the product as it is and it would still be a top-seller, but if it were not so easy to install, configure and use then the product would not be nearly as popular no matter what the cost. The one thing that we absolutely know for sure is that no one wants to own any system that is too complex for them to understand.

Our Dilemma: How to create a relational database structure that does all of the following (in order of IMPORTANCE);

1.	Is EASY to understand and navigate (even for SQL and HL7 novices). Thus our rule #1 all relationships must be no deeper than Parent-Child. No Parent-Child-Grandchild relationships.

2.	All tables must be able to be dynamically created with little or no user involvement or need for "post-creation" manual table alterations.

Will work all over the world and accommodate all released versions of HL7 including specifically "custom" or "vendor specific" HL7 definitions which might contain unknown segments (any Z<xx> segments for instance) or even versions where the system has (or needs to) completely "retask" existing data fields/segments for other uses (according to the HL7 Police this is an incredible absolute NONO and should NEVER be done, so it only happens all the time).

4.	Fully accommodates and handles ALL HL7 structural 'Quirks'. Specifically HL7 Repeating Fields and SubComponent Values automatically without the user having to do extensive setup and configuration.

Well, 3 out of 4 isn't bad. To come up with programmatic business rules that would completely satisfy #4, invariably it would violate or negate something in 1-3.

In this example Component 1 of the field contains 2 subcomponents: C1Data&subC1Data&subC1Data2

When IMPORTING data (See Inbound Processors) the Schema Engine would place ALL of the data into the same field in the database (Ex DB Field: PID_F5_C1). It would be up to you to detect and process those sub-components manually yourself when you extract that data field for your own use.

When EXPORTING data (See Outbound Processors) you would actually insert all of the data (C1Data&subC1Data&subC1Data2) into the field yourself when building your outbound HL7 message. Special Note: Prior to version 3 of the Schema Engine this was not supported as the Schema Engine would automatically 'Escape' the HL7 data when exporting messages. From version 3 onwards the Schema Engine will export the field "as-is" unless the value also contains the Segment Delimiter (defined in the vendor definition) or the database column is a Text column, in which case all data is automatically 'Escaped' (see HL7 Escape Rules) to insure that it doesn't corrupt the message.

Programming Note: If you are receiving Subcomponents (or wish to create them for outbound messages) you may find it necessary to alter the particular database column in question and make it larger than whatever default size the Schema Engine used when creating the table (to avoid data truncation). VERY IMPORTANT: If you do alter the field properties DO NOT convert the field type to "Text". "Text" columns are automatically "Escaped" (see HL7 Escape Rules) when exporting data with the exception of 1 column which is the OBX_RESULTDATA column in the OBX Segment Table which is always exported "as-is".

Repeating Fields: Are much more complicated to handle than SubComponent Values, although we've tried to simplify the process greatly beginning in version 3.0.

Example Raw HL7 Field: |C1Data1^C2Data1^c3Data1^C4Data1~C1Data2^C2Data2^c3Data2^C4Data2|

We'll assume that the field above is HL7 Field #5 of an imaginary HL7 segment called the SKY segment. The imaginary HL7 Vendor definition defines field 5 of the SKY segment as a standard HL7 CM data type (4 components). However field 5 can also repeat (as shown by the ~ character). Your Schema Engine tables when creating the table for the SKY segment created 4 columns for field 5 called SKY_F5_C1, SKY_F5_C2, SKY_F5_C3 and SKY_F5_C4.

When an inbound processor attempts to import the field above the resulting values would look like this:

The inbound processor will try and stuff all of the field data from a repeating field into the column SKY_F5_C4 (the first value that actually contained the REPEAT DELIMITER (~)

Programming Note: As with Subcomponents if you receive REPEATING fields (or wish to create them for outbound messages) you may find it necessary to alter the particular database column in question and make it larger than whatever default size the Schema Engine used when creating the table (to avoid data truncation). VERY IMPORTANT: If you do alter the field properties DO NOT convert the field type to "Text". "Text" columns are automatically "Escaped" (see HL7 Escape Rules) when exporting data with the exception of 1 column which is the OBX_RESULTDATA column in the OBX Segment Table which is always exported "as-is".

You can make things a little easier by Editing the EasyHL7 Vendor Definition and create a NEW Data Type (See Editing Vendor Definitions) called CM2 (for example) which would contain 7 Components. Then edit the SKY segment definition (again see Editing Vendor Definitions) and change the HL7 data type of field 5 to the new CM2 data type. Then you could EITHER recreate your schema tables (see Creating Tables) OR (be very careful) alter the database table definition for the SKY segment table and add 3 new columns (of type VARCHAR AllowNULL=True) called SKY_F5_C5, SKY_F5_C6 and SKY_F5_C7. After doing this your imported data would look like this:

When you insert data into your SKY table to create a repeating field you must build the entire repeating structure yourself and insert that entire value into the LAST column defined (if you're using standard data types and haven't created your own custom data types as described in the section on Importing Data. Like so: