Repeating Field Alternatives

Repeating Fields: Continuing the discussion from the parent topic HL7 Repeating Fields. There is still another alternative which you may find more appealing than those already listed. We have listed it separately because it does involve manually altering your SQL Schema segment tables.

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.

IMPORTING DATA (See Inbound Processors)

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

A Simplified Alternative

You can in ANY segment table for ANY HL7 field create a C0 column. The Schema Engine will automatically detect that this column exists and when detected it will attempt to update the value of that column with ENTIRE HL7 FIELD DATA. So if I know that in my SKY segment that Field 5 can contain repeating data I can alter my SKY segment table and add a column called SKY_F5_C0 (must be Varchar OR Text data type). Make sure that the field ALLOWS nulls. Once I have done that my resulting data in the database would look like this.