An Introduction to OIE Tables

The OIE Table entity provides a simple means to embed look-ups, filters, and translations into workflows. The principle of a Table is that it always receives a value and returns a value - a look-up.

Table definitions are presented via WebDAV in the /dav/Workflow/Tables folder as simple YAML files; they can be created and edited using your favorite text editor. If you are familiar with OIE Format definitions the Table definition should seem very familiar. Tables are identified by their unique name which is specified by the name attribute of their YAML description.

StaticLookupTable

The static look-up table provides a method to do simple recoding of data without relying on external data-sources such as an LDAP DSA or SQL RDBMS. The definition of a StaticLookupTable provides a values dictionary where input values are looked up and the corresponding value returned. The optional defaultValue directive may specify a value to be returned if the input value is not found in the values table; if no defaultValue is specified the table will return a None.

class: StaticLookupTable
defaultValue: 9
values: { 'ME1932': 4,
          'Kalamazoo': 'abc' }
name: TestStaticLookupTable

Text 1: A StaticLookupTable that returns 4 for the input value "ME1932", and "abc" for the input value "Kalamazoo". Any other input value results in the value 9.

PresenceLookupTable

A presence look-up table contains a list of static values. It returns a specified value if the input value matches one of the values stored in the table; otherwise it returns an alternative value. Presence look-up tables are most commonly used when a small and known set of values needs to used to filter a set of data.

class: PresenceLookupTable
name: BankeCodeExclusionTable
returnValueForFalse: true
returnValueForTrue: false
values: [ME1932, Kalamazoo, 123]

Text 2: A PresenceLookupTable that returns boolean false for the input values "ME1932", "Kalamazo", and 123; returns boolean true for all other input values.

SQLLookupTable

An SQLLookupTable permits the translation or look-up of values using an SQL data source defined in the OIESQLSources server default. The table definition must at the minimum define SQLQueryText and SQLDataSourceName directives. Within the SQLQueryText value the "?" is substituted for the input value; the first column of the query result is the return value of the table. If the query identifies no rows then a None value is returned from the table.

SQLDataSourceName: mydbconnection
SQLQueryText: 'SELECT CASE WHEN COUNT(*) = 0 THEN
    ''True'' ELSE ''False'' END  FROM bank_code_exclusion WHERE bank_code
    = ? AND ex_service_followup = ''Y'';'
class: SQLLookupTable
doInputStrip: true
doInputUpper: true
doOutputStrip: false
doOutputUpper: false
name: ServiceFollowUpExclusionTable,
useSessionCache: true

Text 3: An example SQLLookup table which uses the data-source "mydbconnection" as defined in the OIESQLDataSources server default.

The optional directives: doInputStrip, doInputUpper, doOutputStrip, and doOuputUpper, which all default to false, allow the input and the output values to be changed to upper case and stripped of white-space. Converting a value to upper case may be useful in the case where a database backend itself does not support case-insensitive compare. Trimming whitespace on input values can protect from attempting to look-up padded strings and output trimming is useful for database engines that always return strings values defined like CHAR(30) as padded values.

Using Tables

In Python code using a table is as simple as loading the class and calling the lookup_value method. However the Table performs the look-up is entirely encapsulated in appropriate Table class [SQLLookupTable, StaticLookupTable, ...]

table = Table.Load(name)
return table.lookup_value(value)

Text 4: How to use a table to look-up values in Python code.

More commonly Table lookups are going to be performed within workflow actions such as maps and transforms. When performing an XSLT transform using any table is available via the tablelookup OIE extension method; this allows values from the input stream to be easily used as lookup-values facilitating translation of ERP and other codes/abbreviations between disparate applications.

<xsl:template match="row">
</xsl:template>
<xsl:if test="total_charges>1000">
</xsl:if>
<xsl:variable name="include" select="oie:tablelookup('ServiceFollowUpExclusionTable',string(bank_code))"/>
<xsl:if test="$include='True'">
<row>

Text 5: This snippet of an XSLT transform demonstrates how to use a Table lookup from with an stylesheet.

Overall Tables provide a simple and elegant way to automate all the codes that need to be inserted and translated in the wide variety of documents processed by the workflow engine as well as providing a means to easily implement dynamic filtering.

Author: Adam Tauno Williams