I have just published my third and final Tech Article. This article describes how you can develop a single Interface Code Base and then using Package and Global Mappings generate a multitude of Interfaces that either have date PUSHED into them or PULL data from a Source Application or Database and in my example, transform that data into either HL7 or FHIR messages. I then use HTTP to send the message to a Target Server.

I have attached the article as a PDF which might make it a bit easier to read and I will be releasing the actual software on OEX in the near future.


I have had a quick look at the SS_User Table in L2016. The problem with the SS_Useer table is that it tends to be customised to the needs of the customer and so in my version I have no Column "OtherUserLoc". I checked the class to see if it exists in the class definition and is not projected to SQL but it is not in the class either.

The SS_User table is not a child table. It does have some child Relationships. The Episode and Test Set Tables will reference the SS_User table for the user who registers the Episode and the Laboratory Technician who performs the Tests. They will point to the SS_User Table like this:

Property EPVISUserIDDR As SSUser [ SqlColumnNumber = 31, SqlFieldName = EPVIS_UserID_DR ];

Let me investigate and I will come back to you. As a matter of Interest what exactly do you mean by TrakCare - Components - Items?

Do you mean Episode -> TestSet -> TestItem?


That is the most Likely cause, check the Message Type Field in the MSH. It Should show you the Message Code, the Trigger Event and the Message Structure. The Message Code would be ADT, the Trigger Event A31 and the Base Message Structure which in this example is ADT_A05

Another approach is to capture the contents of the message and write it into Notepad and save it as a .txt file and then from the Ensemble ->Interoperate -> HL7 V2.x ->HL7 Message Viewer, Select File from the Document Source Drop Down, locate your file, ignore the Document Number in File field, Then select the "Use ontent Declared Version Name". Your H7 message should be displayed and provided it is structurally sound the fields will all be in blue and hyperlinked and if you hover over any field you will get the Name displayed


I started writing a response to your questions yesterday afternoon and I wrote a lengthy explanation using LabTrak as the basis for my response. I wrote about 400 lines of explanation and then my laptop rebooted to install some updates and I lost everything I wrote. So I am going to answer but I am going to abbreviate the explanation as I don't have enough time to rewrite the whole document I wrote yesterday.

1) Different database Storage Models

  • DefaultStrorage
    • Is the Storage model used when you create a new Class/Table in Ensemble/IRIS.
    • Stores data in a Global whose name ends with a "D". It stores Indices for the Class/Table in a Global ending with an "I" and puts Stream data into a Global where the Name ends with an "S"
    • Data is stored in a $list structure. $list creates a couple of bytes at the beginning of each field that informs the Compiler how long the field is and the base datatype of the field: String, Number, Boolean and Bit.
    • By default, the RowId is a Sequential Contiguous Integer starting from 1. The RowId is referenced by the column named "ID"
    • The RowId can be overridden by creating an Index on a property of the class and assigning it the attributes [Unique, PrimaryKey, IDKey]. PrimaryKey says that the property value is the RowId for each row in the Table. IDKey says that the property Value is the Object.%Id() and is the Object Identifier. Unique is implicit in that you cannot have two different records with the same RowId value.
    • The Global reference for the Data Global looks like this:
    • ^GlobalD(RowId)=$lb(Property1,Property2,...,PropertyN)
    • An index on Property X will generate a Global Reference: ^GlobalI("{IndexName}",{Property_Value},{RowId_Value"})=""
    • The RowId, irrespective of whether it is system generated or based on the value of a specific property or properties is the "ID" in an SQL statement
  • SQLStorage
    • This model was implemented to allow InterSystems customers who had developed applications that used direct Global Access to create a Mapping of the Global Structures in such a way that the data in the global can be exposed as Class Objects or Table Rows.
    • The RowId can be referenced as "ID" in an SQL Statement. The RowId has to be generated by the application even if it is a sequential Integer, though in many applications including LabTrak typically subscripted their Application Globals with a data value generated by the application. For example, the Episode Number in LabTrak is based on the 2 character Site Code followed by a string of Numerics. e.g. AA1345363

2) Links between Tables

  • A Designative Reference is a term we use for a Property/Column whose value is the RowId of a row in another Class/Table. The most common examples are code tables. If you have a code table CT_Country where the Country_Code is the RowId of the table. A column on the table EP_VisitNumber (A Trak Episode)  called CountryDR is linked to a row in CT_Country. 
  • The CT_Country Global will look like this: ^User.CTCountryD({Country_Code})="{Country_Name}\{Country_Currency}\{Country_Telephone_Code}" and an example would be: ^User.CTCountryD("ZA")="South Agrica\ZAR\+27"
  • The Episode Record will look like this: ^TEPI({Episode_Number})="field1\Field2\...\ZA\...\FieldN".  The SQL query "Select Country_Code from SQLUser.EP_VisitNumber where EP_VisitNumber='AA12347690'" will display ZA from the Country_Code column. You can use a "->" (Points Into) construct supported in Ensemble/IRIS SQL to access the Country_Name without having to specify the CT_Country TableName in the FROM clause or the WHERE clause "WHERE EP_VisitNumber.Country_Code=CT_Country.ID"
  • The Select statement looks like this "SELECT EP_VisitNumber,EP_VisitSurname,Country_Code->CountryName from SQLUser.EP_VisitNumber
  • Designative References do not support Referential Integrity. That means that you could delete the "ZA" row in CT_Country even though there may be thousands of Episodes that point to that row. In order to enforce Referential Integrity, you declare a ForeignKey definition on CountryCode in the Class Definition. Ensemble will generate an Index of Country Codes and Episode Numbers and if there are any entries in this Index for the Country Code "ZA", i.e. Episodes linked to the Code "ZA" then an Error Status is generated that indicates that the ForeignKey Constraint prevents the delete of the the "ZA" row in CT_Country as there are rows in other Tables that reference that Row.
  • LabTrak doesn't use ForeignKeys (generally speaking), the application does, however, prevent you from Deleting rows in Code Tables if those Codes are referenced by other LabTrak Tables.

3) Relationships

There are two types of relationships most commonly used in Ensemble/IRIS Classes/Tables: Parent-Child and One-Many. Many-Many relationships can be defined. Refer to the documentation on Relationships to see how Many-Many relationships are defined. I can't think of any examples of Many-Many relationships in LabTrak so instead, I will just focus on the two main relationship Types.

Parent-Child Relationships

If you look at the TEPI global in LabTrak which is the global which stores Episodes. You will see something like this:

^TEPI({Episode_Number})="{Episode Fields}"



for example:

^TEPI("AA00000401")=""SALM\NIGEL\M\44453\\\S2\61839\646\61838\480\61839\0DR\\R\\\1039444\\41C028W000\\\\\40\\\F\\fred.smith\\61839\646\N\\AA\,690425\\\\\\N\0\\\\N\H\38768\\\\41C028W000\\\\\\\\\\\\\\\Z03.9\52ZETH000003" - This is the Episode Record

^TEPI("AA00000401",1,"H065",1)="61842\594\jane.doe\61842\597\jane.doe\\N\\\\\\\\\\\\\61839\646\\\N\\\Y\\\A\~~~\ \N\\fred.smith\\\\\\N\\61838\28800\\\1\\\\\Y\\\\61839\38760\\\N\\\\\\\N\61843\8122\\\P\Z03.9\" - This is a Test Set Record within the Episode

^TEPI("AA00000401",1,"H065",1,"DATA","H0440")="4.14\\\\\AAH02\\\\\\\\\" - This is a Test Item Record within the Test Set.

As you can see this global is a Multidimensional Array. It has many subscript levels. The ability to create Arrays like this is one of the features that differentiates Cache/Ensemble/IRIS from any other Database Technology. It is one of the reasons why Ensemble/IRIS creates databases that are, on average, half the size of other database technologies on a Like for Like basis. Partly because the data records do not use fixed-length fields. By using Delimiters or $List we do not need to pad out field values with leading '0s or trailing spaces or NULL characters. Secondly, by storing the Test Sets within the Episode men's that when we access the Episod/IRIS loads the data page that contains that episode into memory it brings with it the Test Sets and Test Items. Where you walk through the Test Sets the Test Set Records are already in memory. We haven't had to go to another data structure in the Database to fetch the Test Set Records from a Test Set Table as you would need to do in other Relational Database Technologies.

If you delete an Episode the Test Sets and Test Items are deleted as well. You do not need to delete the Test Items first, then delete the Test Sets and then, finally, delete the Episode.

Nor do you need to maintain Indices to link Test Items to Test Sets or Test Sets to Episode.

In LabTrak the property in the Parent Table that references the Child rows in the Chile Table is prefixed with the word "Child" and the corresponding property in the Child Table that points to the row in the Parent Table is suffixed with the word "ParRef"

One-Many Relationships

In the One to Many relationship, there will be two tables, a Row in the "One" table is linked to Many rows in the "Many" table. From an Objects point of view, there is a Property in the "One" class that points to the related rows in the "Many" table. In LabTrak the property name is prefixed with the word "child". There is a corresponding property in the "Many" Table and that property name is suffixed with the word "ParRef".

Your Question

When I read your question and I looked at the Schema Diagram I was a bit confused as there is no SQLUser.CT_Loc table in LabTrak. There is however a table called SQLUser.CT_UserLocation.

If you use the Management Portal -> System Explorer -> SQL in the LabTrak namespace and you select the SQLUser schema in the Schema drop-down list on the left of the SQL form. You will be presented with a list of all Tables in the SQLUser schema. If you scroll down and find the table "CT_UserLocation" and you drag it into the "Execute SQL" text area on the form it will display the following SQL Statement:

CTUSL_RowId, CTUSL_Code, CTUSL_Desc, CTUSL_Destination_DR, CTUSL_UniqueSite, CTUSL_MoveDirectory, CTUSL_AccreditationNumber, CTUSL_WebReportFooter, CTUSL_DisplaySequence, CTUSL_RegionCode, CTUSL_Address1, CTUSL_Address2, CTUSL_Address3_Suburb, CTUSL_Address4_State_DR, CTUSL_Address5_PostCode, CTUSL_Phone, CTUSL_Fax, CTUSL_email, CTUSL_StructuredCode, CTUSL_StructuredCodeLength, CTUSL_StructuredCodeLevel, CTUSL_Address, CTUSL_DocCourierRun_DR, CTUSL_LocCourierRun_DR, CTUSL_ActiveFlag, CTUSL_DefaultNID, CTUSL_UnixMoveDirectory, CTUSL_PFforBatchEntry
FROM SQLUser.CT_UserLocation

Note the Columns  CTUSL_Address4_State_DR, CTUSL_DocCourierRun_DR, CTUSL_LocCourierRun_DR. These are all Designative References to rows in other Tables.

The EP_VisitTestSet table contains the following Columns:

VISTS_ParRef, VISTS_RowId, VISTS_TestSetCounter, VISTS_TestSet_DR, VISTS_DateOfEntry, VISTS_TimeOfEntry, VISTS_UserEntered_DR, VISTS_DateOfAuthorisation, VISTS_TimeOfAuthorisation, VISTS_UserAuthorised_DR, VISTS_PathologistID_DR, VISTS_ExcludeFromCCR, VISTS_Rule3Exempt_Sequence, VISTS_Priority_DR, VISTS_Rule3Exempt_Max, VISTS_TherapeutDosage, VISTS_TimeOfDosage, VISTS_24HUVolume, VISTS_24HUTimePeriod, VISTS_BB_TransfEvents_DR, VISTS_Rule3Exempt_Date, VISTS_DateOfPathologistAtt, VISTS_TimeOfPathologistAtt, VISTS_StaffNotes, VISTS_DateOfCreation, VISTS_TimeOfCreation, VISTS_StandardLettersChecked, VISTS_BB_DateRequired, VISTS_ExcludeFromPatientMean, VISTS_UserSite_DR, VISTS_Machine, VISTS_Printed, VISTS_SuperSet_DR, VISTS_StatusResult, VISTS_DFT_TimeOfFirstCollection, VISTS_HISTO_Extra, VISTS_HISTO_BillingItem, VISTS_SupressBilling, VISTS_HospitalRefNumber, VISTS_UserCreated_DR, VISTS_MoveToReferralLab_DR, VISTS_MoveToUserSite_DR, VISTS_DFT_DR, VISTS_DFT_Position, VISTS_DFT_DateOfFirstCollection, VISTS_StatusEntry, VISTS_Confidential, VISTS_SpecimenType_DR, VISTS_SpecimenNo, VISTS_DateOfCollection, VISTS_TimeOfCollection, VISTS_CollectedBy_DR, VISTS_DisplaySequence, VISTS_SupressReason, VISTS_UserSupress_DR, VISTS_DateOfSupressBilling, VISTS_Rule3Exempt_Comments, VISTS_AnatomicalSite_DR, VISTS_Reason_DR, VISTS_DateOfReason, VISTS_TimeOfReason, VISTS_UserReason_DR, VISTS_DateOfReceive, VISTS_TimeOfReceive, VISTS_PaymentCode_DR, VISTS_SpecimenGroup_DR, VISTS_Document_DR, VISTS_BB_Neonatal, VISTS_RR_Date, VISTS_RR_Time, VISTS_RR_User_DR, VISTS_RV_Date, VISTS_RV_Time, VISTS_RV_User_DR, VISTS_BB_DoNotFile, VISTS_DateOfLastChange, VISTS_TimeOfLastChange, VISTS_UserOfLastChange_DR, VISTS_DateOfSentSTM, VISTS_TimeOfSentSTM, VISTS_DateOfReceivedSTM, VISTS_TimeOfReceivedSTM, VISTS_MovementStatus, VISTS_AddedByAction, VISTS_PricingStatus, VISTS_ICD10List, VISTS_LongTerm, VISTS_RequestBy_DR, VISTS_LongTermReason, VISTS_PairedSeraQueue, VISTS_DoctorDR, VISTS_BB_TimeRequired, VISTS_BB_Tags, VISTS_DTOfResultChange, VISTS_DateOfFirstAuthorisation, VISTS_TimeOfFirstAuthorisation, VISTS_AnatomicalSiteFT, VISTS_ReasonReportedTo, VISTS_ReasonTelephone, VISTS_ReasonObservations, VISTS_ManualPCEntry, VISTS_ReasonClearResults, VISTS_ReasonClearSN, VISTS_ReasonResultsNR
FROM SQLUser.EP_VisitTestSet

If I add a WHERE Clause: WHERE VISTS_ParRef %STARTSWITH 'AA' it will return records from the site that "AA" 

If you run this query you will see rows of data that look like this

AA00000005 AA00000005||H065||1 1 H065 04/19/2010 1209

The ParRef is the Episode Number. The RowId for the Test Set is AA00000005||H065||1, {Episode}||{TestSetCode}||{TestSetCounter}.

I created an ODBC connection to a LabTrak Namespace on our UAT server. I unspecified the Server IP address, the Super Server Port Number, The Namespace and my Credentials. I tested the connection which was successful. Te ODBC connection can be used by Excel and other reporting tools to create Reports and Charts.

I then use an application called DBBeaver (available on the Open Exchange site) to create ERD diagrams of the LabTrak classes. DBeaver uses the InterSystems JDBC driver and you set up the connection in DBeaver itself.



If you are using the HS FHIR Resource classes in IRIS for Health then you will see that attributes such as Patient.Picture is a BinaryStream. If you are using a BinaryStream you need to convert it into Base64 encoding which generates a stream of characters that do not include any control characters that may cause a JSON or XML parser to think that it has encountered a line terminator. If you look at the FHIR to SDA nd SDA to FHIR data transformations you will see how they Transform the Inbound Stream from Base64 to Binary and from Binary to Base 64 encoding for the outbound SDA to FHIR JSON or XML.

Hi Yuri

I forgot to mention that I agree with Eduard that your article is excellent.


Hi Yuri

You didn't use GoJS by any chance for the UML etc... If you look in the General Section of Discord I have written a chunk of stuff about GoJS and how a developer could build the necessary data collectors into their classes and then having but the data sets render the diagrams through GoJS

I felt the need to remind us that we were very lucky that InterSystems came along and did away with all those peculiarities. I should add it to the thread on the future of ObjectScript. Imagine if we had to sell that to a customer. Maybe it's because I first learned to write code like that and it is no wonder why I love ObjectScript so much.

I agree with Steve

You can use ^%GOF, GBLOCKCOPY,  or even Merge ^Global=^[{Old_Namespace_Name}]Global as long as you know which globals you need, that the globals are not already mapped from some other namespace.

When it comes to your code you have to take into account your Classes (.cls), include files (.inc), routines (.int) (rarely used in new Ensemble/IRIS applications but there are applications that are written entirely in routines and use old style "nested dot" syntax in the form:

TestList xx (xx,TestList)=""
sort="" f  sort=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort),-1) q:sort=""  d
. epis="" f  epis=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort,id)) q:epis=""  d
. . depseq="" f  depseq=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort,id,depseq)) q:depseq=""  d
. . . dep="" f  dep=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort,id,depseq,dep)) q:dep=""  d
. . . . sectseq="" f  sectseq=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort,id,depseq,dep,sectseq)) q:sectseq=""  d
. . . . . sect="" f  sect=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort,id,depseq,dep,sectseq,sect)) q:sect=""  d
. . . . . . tsseq="" f  tsseq=$o(^Global($zn,$j,"Literal",dh1,SORT,dh2,Master,report,sort,id,depseq,dep,sectseq,sect,xyzseq)) q:tsseq=""  d

I kid you not... 

There are .mac (same as .int but with embedded SQL, which compile down to .int), BPL's, DTL's, Business Rules,  modified HL7 schema's, CSP pages in /csp/{namesapce}, corresponding .js and .css in the /csp/broker/ directory but not necessarily, it depends on how they are referenced in the HTML Header.

So, in my opinion, Steve is correct. Either dismount the current database (you can tell that it is not mounted if there is no .lck file in the database directory. If there is a .lck file and you proceed with copying the .dat database file, there is a good chance that when you try and assign the new .dat to a namespace, it may fail to mount. Better to shut down the instance.

It is probably easier to delete the globals from the original database than to delete the logic as there are so many extensions that you need to check for (as listed above, and if you didn't write the original application, it is easy to forget about the .inc, .dtl,  .bpl, .hl7, .mac, .int entities (if there are any of course).

Export the globals you plan to delete from the original database, and I would use the Management Portal frankly as it is easier to check the checkboxes next to the global names you want to delete (and export first before you delete) than type in the global names in any of the terminal utility such as ^%GOF. One typo, and you think you exported ^Customer but actually exported ^Kustomer, but you killed ^Customer, and then you realise that ^Customer was actually mapped into the namespace. Now you've deleted it in the source database, and you need to restore it, but you don't have an export because of that typo. The Management Portal allows you to exclude System Globals and Mapped Globals and avoid an accidental kill.

It is also a good idea to adopt a meaningful naming convention to indicate the difference between your DEV, QC, UAT and PRD databases and whether the database is for data (DT) or code (RT or RTN). It is also worth considering separating your stream data into a third database using global mapping. The reason for this is that if you have, for example, a Patient Class and one of the properties is Picture as %BinaryStream, the Binary Stream content will be stored in the 'S' global in the Storage Definition. Pictures of your Patient may be useful to display in your Patient Registration UI. Still, unless you are exporting that Patient as a FHIR Patient Resource, it is unlikely that you will reference the picture very often. Even with Objects, IRIS won't load the Picture into memory (Global Buffer Pool or your User Process) unless specifically referenced, i.e. "Patient.Picture". IRIS creates a "stream" directory though I am not sure what its purpose is. 

MyDatabase-DT-PRD (Data, Production)

MyDatabase-RT-PRD (Code, Production)

form Namespace



The equivalent for Dat/Times is $zdt($h,3) which will give you YYYY-MM-DD HH:MM:SS

If you want greater precision you can use


If $now() is 65954,68889.600788 then

$zdt($now(),3,,6) returns 2021-07-29 19:08:09.600788

This can be useful where you have data flowing in where there are many records per second and you want to be more accurate when assigning a Date/Time to the data.

The 6 in $zdt($now(),3,,6) indicates 6 digits in the Milliseconds