Map Tables (v15)

Map tables have been significantly revamped in version 15 to allow for a dynamic number of columns as well as more powerful querying options (directly query the underlying DataTable object using SQL syntax or LINQ). Most devices which have map table functionality now use this new functionality.

Using an HL7 Transform (v2) device, open the map table dialog and create a new map table:

Typically, your table content will come from another source such as Excel. In this case, the easiest way to import the data is via the clipboard. Simply copy from the source, right-click the top-left empty cell, and choose the paste option. The correct number of columns will be automatically created during the paste operation.

Notice the first row of the table contains the column names. You can right click the first row and select the 'Use Current Row As Header'.

The next step is to define your indexes. In addition to improving performance, indexes ensure that value(s) you are searching for within the table are unique. If you wish to search on multiple different columns, you can define multiple indexes. Additionally, if you are searching on multiple columns, you can define 'compound' indexes. For example, in the table above, you may wish to search on 'Modality' and 'Body Part' together and return the corresponding LOINC code ("find the LOINC code where Modality = 'ct' and Body Part = 'brain'"). In this case we must define a compound index which includes both the Modality column and Body Part column. To do this we click on the key icon in the Body Part column and select the compound index option:

Compound indexes show a different key icon in the column header.

The inverse of looking up LOINC codes based on Modality and Body Part would be to look up either Modality or Body Part based on the LOINC Code. In this case we would want to add a single index the LOINC column. To do this, click the key icon in the LOINC Code column header, and select the first option.

Now the key icon in the LOINC Code column indicates a single index.

Using the gear icon displayed in the header of each column (or the corresponding column context menu), you can also modify the structure of the table:

• Remove an existing column
  
• Append a new column
  
• Rename an existing column
  

Once you have configured your map table, you can reference it within the HL7 transform device. Start by adding a new map table transform:

The map table transform UI lets you configure the index, source paths, and output column. You can select a different index by clicking on the red text:

In custom code, the underlying data table object is now exposed. This allows you to perform your own complex queries:

using System;
using System.Linq;
using System.Text;
using System.IO;
using System.Collections.Generic;
using Connexion.Core;
using Connexion.Core.HL7;
using Connexion.Share;

namespace Connexion.Device
{ 
  // ** This is pseudo-code **
  [ClientRunnable(true)]
  public partial class CustomFunctions : BaseHL7CustomFunctions
  {   
    public void TableLookup(IMessageContext context)
    {
      MapTable mt;
      if(!MapTablesByName.TryGetValue("New MapTable", out mt))
        throw new Exception("Maptable 'New MapTable' not found");
     
      var message = context.GetAsHL7Message();
      
      // put the values to search for in a dictionary where the key is
      // the column name and the value is the value to search for.
      // In production, cache the dictionary and just change the values.
      var lookup = new Dictionary<string, string>();
      lookup.Add("Modality", message["MSH-9"]);
      lookup.Add("Body Part", message["MSH-10"]);
      
      // set the value of MSH-11 to whatever the map table lookup returned
      message["MSH-11"] = mt.Lookup(lookup, "Loinc Part Code");
    }
    
    public void DatatableLookup(IMessageContext context)
    {
      MapTable mt;
      if(!MapTablesByName.TryGetValue("New MapTable", out mt))
        throw new Exception("Maptable 'New MapTable' not found");
      
      var message = context.GetAsHL7Message();
      
      // SQL syntax - careful this can be slow
      // https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
      var datatable = mt.DataTable;
      var result = datatable.Select($"Modality = '{message["MSH-9"]}' AND [Body Part] Like '{message["MSH-10"]}%'");
      var row = result.FirstOrDefault();
      if(row == null)
      {
        message["MSH-11"] = "Not Matched";
      }
      else
      {
        message["MSH-11"] = row["LOINC Part Code"].ToString();
      }
    }

  }
}