Programmatically Working with Lookup Tables

InterSystems IRIS provides the utility function called Lookup() so that you can easily perform a table lookup from a business rule or DTL data transformation. This function works only after you have created at least one lookup table and have populated it with appropriate data.

For information on defining lookup tables, see Defining Data Lookup Tables.

If you need more direct manipulation of lookup tables than the Management Portal provides, use the Ens.Util.LookupTableOpens in a new tab class. This class exposes lookup tables to access via objects or SQL. Additionally, it provides class methods to clear tables, export data as XML, and import data from XML.

Ens.Util.LookupTableOpens in a new tab provides the following string properties:

TableName

Name of the lookup table, up to 255 characters. You can view the lookup tables defined in a namespace by selecting Interoperability, Configure, and Data Lookup Tables in the InterSystems IRIS portal and then selecting Open.

KeyName

Key for the entry within the lookup table, up to 255 characters. This is the value from the Key field on the Interoperability > Configure > Data Lookup Tables page.

DataValue

Value associated with this key in the lookup table, up to 32000 characters. This is the value from the Value field on the Interoperability > Configure > Data Lookup Tables page.

A sample SQL query might be:

SELECT KeyName,DataValue FROM Ens_Util.LookupTable WHERE TableName = 'myTab'

Ens.Util.LookupTableOpens in a new tab also provides the following class methods:

%ClearTable()

Deletes the contents of the specified lookup table.

  do ##class(Ens.Util.LookupTable).%ClearTable("myTab")

%Import()

Imports lookup table data from the specified XML file. For the import to be successful, the file must use the same XML format as that provided by the %Export() method of this class.

  do ##class(Ens.Util.LookupTable).%Import("myFile.xml")

%Export()

Exports lookup table data to the specified XML file. If the file exists, InterSystems IRIS overwrites it with new data. If the file does not already exist, InterSystems IRIS creates it. The following example exports only the contents of the specified lookup table, myTab:

  do ##class(Ens.Util.LookupTable).%Export("myFile.xml","myTab")

The following example exports the contents of all lookup tables in the namespace:

  do ##class(Ens.Util.LookupTable).%Export("myFile.xml")

The resulting XML file looks like the following example. Note that all entries, in all tables, appear as sibling <entry> elements inside a single <lookupTable> element.

<?xml version="1.0"?>
<lookupTable>
  <entry table="myOtherTab" key="myKeyA">aaaaaa</entry>
  <entry table="myOtherTab" key="myKeyB">bbbbbbbbb</entry>
  <entry table="myTab" key="myKey1">1111</entry>
  <entry table="myTab" key="myKey2">22222</entry>
  <entry table="myTab" key="myKey3">333333</entry>
</lookupTable>

For each <entry>, the table attribute identifies the table that contains the entry. The key attribute gives the name of the key. The text contents of the <entry> element provide the entry’s value.

In addition to the XML format described above, you can use the SQL Import Wizard to import comma-separated value (CSV) files that list tables and keys.

Defining a Custom Archive Manager

Overriding Start and Stop Behavior