Lookup tables are general-purpose dictionaries. A lookup table has one to six primary keys, and one to 30 custom attribute properties. In a lookup query, you specify values for the keys and retrieve one or zero results. For any row in a lookup table, the combination of keys has to be unique. Thus, any lookup can at most retrieve a single result.

Lookup tables can be created dynamically. When you do, you do not instantiate a new table in the relational database. Instead, a lookup table is defined by a single entry in the LookupTable table. The properties uniqueName and validAfter uniquely identify a single lookup table.

A lookup table’s rows are persisted in a separate table; called a value table. There are multiple value tables, so the properties type and valueType on the LookupTable object specify which value table is used.

LookupTable

Lookup tables are referred to by their uniqueName. When multiple lookup tables share the same uniqueName, they are considered different versions of the same table. In calculations, the lookup table with the most recent validAfter date is always used — older lookup tables are ignored.

Contrary to its name, the uniqueName does not have to be unique. It is the combination of uniqueName, validAfter, and simulationSet that uniquely identifies a lookup table. (For non-simulations, the simulationSet value will be null.)

Lookup Table Value Tables

The rows of any lookup table are persisted in one of the value tables. The combination of type and valueType on the LookupTable object determines which value table is used. This means that different lookup tables can share the same value table. To differentiate between the two row sets, each lookup table row contains a reference to the LookupTable entry that it belongs to.