Name

tablelist — Extract a list of values from a lookup table

Syntax

tablelist(table)
tablelist(table, expr)
tablelist(table, expr, selection)

The TABLELIST function has the following arguments:

table

A literal string that names the lookup table to be used

expr

(Optional) An expression that will be evaluated to identify the name of the column within the lookup table to extract values from.

selection

(Optional) A string containing an expression that will be evaluated to select the rows that should be used to form list values. If not specified than all rows will be used.

Description

The tablelist function returns lists of values that have been extracted from a lookup table.

The first parameter is a literal string containing the name of the lookup table. If the lookup table name has not been defined then an error will occur. If no other parameters are specified then the function will return a list of the column names in this table.

The optional second parameter is a string containing a Patchworks Query Language expression that will be evaluated on each selected row of the lookup table to extract a value for the output list. The expression will be compiled in the context of the lookup table and can use any of the columns in that table.

The optional third parameter is a string containing a Patchworks Query Language expression that will be evaluated on each row of the lookup table to determined if the row is selected or not. The expression will be compiled in the context of the lookup table and can use any of the columns in the table. If the third parameter is not provided then all rows will be selected.

If the expressions in the second and third parameters contain quoted strings then the quote characters will need to be marked with a '\' escape character to indicate that they are to be embedded and do no demarcate the end of the current string, for example the expression

OLDMIN ne ''

would be contained in a string as

'OLDMIN ne \'\'' 

or using the q function

q(^OLDMIN=''^)

Examples

Example 1. Retrieve the column headings from a table

Formula

Description

Result

tablelist('t1')Extract a list of the column headings in the table named 't1'. 
tablelist('t1','theme2')Extract the values of the theme2 column from the table name 't1'.  

tablelist('t1','theme2',
             'OLDMIN ne \'\'')

Extract the values of the theme2 column from the table name 't1', but only for rows for the expression for OLDMIN ne ''.