Name

tablemap — Extract a value from a list.

Syntax

tablemap(tablename, keycolumn, key)
tablemap(tablename, keycolumn, key, resultcolumn)
tablemap(tablename, keycolumn, key, resultcolumn, default)
              

The TABLEMAP function has the following argument:

tablename

The name of the table that will be used for this lookup operation.

keycolumn

The name of the column containing the primary keys.

key

The key to use to lookup the value to be returned.

resultcolumn

The column from which the result value will be retrieved.

default

(Optional) A default value that will be provided if the key does not match an item in the primary key column, or if there is no value in the result column.

Description

This function extracts a value from a lookup table by searching a primary key column to find a matching entry, and then returning a value from the result column in the same row.

The keycolumn and resultcolumn arguments specify the names of columns in the lookup table. The key argument specifies a value that should be found in the keycolumn. If the value is found then return the value that is in the same row from the result column.

There are three forms of this function. The first form only specifies the tablename, the keycolumn and the key. In this for the function returns a boolean value (true or false) to indicate if the key is found in the table.

The second and third forms of this function use the resultcolumn argument to determine the column from which the result will be returned. If the key value is not found, or if the result value is an empty string then an error will occur. If a default value has been provided then the default value will be returned instead of an error condition.

All values in the lookup table have a string data type. If a different data type is required then a cast may be used on the result.

Examples

Formula

Description

Result

tablemap('Operability','Type','SWD')

Search the 'Type' column in the 'Operability' table for a key value of 'SWD'. Return true if the key is found.

true or false

tablemap('Operability','Type','SWD','Min')

Search the 'Type' column in the 'Operability' table for a key value of 'SWD', and return the value of the 'Min' column in the same row. If the key is not found or the 'Min' value is empty then an error occurs.

min value or error

tablemap('Operability','Type','SWD','Min','0')

Search the 'Type' column in the 'Operability' table for a key value of 'SWD', and return the value of the 'Min' column in the same row. If the key is not found then return '0'.

min value or '0'

int(tablemap('Operability','Type','SWD','Min','0'))

Search the 'Type' column in the 'Operability' table for a key value of 'SWD', and return the value of the 'Min' column in the same row. If the key is not found then return '0'. Convert the return value to an integer

min value or 0

tablemap('FUSuccessn','FU_SI',PLANFU+';'+Resp_BASC1,'BASC1','') ne ''Check the 'FUSuccessn' table for a matching value in the 'BASC1' column. If the key is not found or if there is no matching value then the function returns an empty string. The logical test will return true if a value is found, or false if no value is found.true or false if the key exists and a value is found in the column.