Lookup Tables (Reference)

Table of Contents

A lookup table is defined by an entry in the LookupTable table. Each row in this table defines a lookup table. The rows of each one of these lookup tables are stored in one of the value tables. The LookupTable has the following properties:

uniqueName

Primary key. A string that is used to refer to the table. Contrary to the property name, it does not have to be unique. It is the combination of uniqueName, validAfter, and simulationSet that uniquely identifies a lookup table.

validAfter

Primary key. Specifies a date after which this lookup table can be used. When multiple lookup tables share the same name, the lookup table with the most recent validAfter date will be used.

simulationSet

Primary key. A nullable string that forms

type & valueType

These properties do not have meaning on their own, but the combination determines which value table will be used. When the type is SIMPLE, the valueType determines the type of value. When the type is RANGE, the valueType determines the type of the key.

status

Value of the TimedObjectStatus enumeration. Determines whether — or in what context — the lookup table will be used.

label

The text that will be displayed to the end-user — to represent the lookup table.

formatType

Only relevant for simple lookup tables. Specifies how the value in the key-value-pair will be formatted in the frontend application.

Value Tables

The value tables store the actual rows of the lookup table. It is the combination of LookupTable.type and LookupTable.valueType that determines which value type table will be used. The table below contain these details.

type (string) valueType (string) Table (class)

MATRIX

[matrixlookuptablevalue]

MATRIX

MATRIX2

[matrixlookuptablevalue2key]

MATRIX

MATRIX3

[matrixlookuptablevalue3key]

MATRIX

MATRIX4

[matrixlookuptablevalue4key]

MATRIX

MATRIX5

[matrixlookuptablevalue5key]

MATRIX

MATRIX6

[matrixlookuptablevalue6key]

SIMPLE

REAL | INT | STRING | DATE

[lookuptablevalue]

SIMPLE_DATE_KEY

REAL | INT | STRING | DATE

[lookuptablevalue]

SIMPLE_INT_KEY

REAL | INT | STRING | DATE

[lookuptablevalue]

SIMPLE_REAL_KEY

REAL | INT | STRING | DATE

[lookuptablevalue]

RANGE

REAL | INT | STRING | DATE

[lookuptablevalue]

JSON

[jsonlookuptablevalue]

JSON

JSON2

[jsonlookuptablevalue2key]

MatrixLookupTableValue

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the MatrixLookupTableValue table.

LookupTable.type – MATRIX

LookupTable.valueType – MATRIX

Value Table – MatrixLookupTableValue

The MatrixLookupTableValue has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

name – Primary key(s), whose combination uniquely identifies a single lookup table row.

attribute1..30 – Fields that can be customized with attribute metadata.

When the LookupTable.valueType is MATRIX, the name of the key is name — rather than key1.

MatrixLookupTableValue2Key

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the MatrixLookupTableValue2Key table.

LookupTable.type – MATRIX

LookupTable.valueType – MATRIX2

Value Table – MatrixLookupTableValue2Key

The MatrixLookupTableValue2Key has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

key1..2 – Primary key(s), whose combination uniquely identifies a single lookup table row.

attribute1..30 – Fields that can be customized with attribute metadata.

MatrixLookupTableValue3Key

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the MatrixLookupTableValue3Key table.

LookupTable.type – MATRIX

LookupTable.valueType – MATRIX3

Value Table – MatrixLookupTableValue3Key

The MatrixLookupTableValue3Key has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

key1..3 – Primary key(s), whose combination uniquely identifies a single lookup table row.

attribute1..30 – Fields that can be customized with attribute metadata.

MatrixLookupTableValue4Key

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the MatrixLookupTableValue4Key table.

LookupTable.type – MATRIX

LookupTable.valueType – MATRIX4

Value Table – MatrixLookupTableValue4Key

The MatrixLookupTableValue4Key has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

key1..4 – Primary key(s), whose combination uniquely identifies a single lookup table row.

attribute1..30 – Fields that can be customized with attribute metadata.

MatrixLookupTableValue5Key

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the MatrixLookupTableValue5Key table.

LookupTable.type – MATRIX

LookupTable.valueType – MATRIX5

Value Table – MatrixLookupTableValue5Key

The MatrixLookupTableValue5Key has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

key1..5 – Primary key(s), whose combination uniquely identifies a single lookup table row.

attribute1..30 – Fields that can be customized with attribute metadata.

MatrixLookupTableValue6Key

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the MatrixLookupTableValue6Key table.

LookupTable.type – MATRIX

LookupTable.valueType – MATRIX6

Value Table – MatrixLookupTableValue6Key

The MatrixLookupTableValue6Key has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

key1..6 – Primary key(s), whose combination uniquely identifies a single lookup table row.

attribute1..30 – Fields that can be customized with attribute metadata.

LookupTableValue

The LookupTableValue table is used for two different types of lookup tables:

If the LookupTable.type is SIMPLE, SIMPLE_REAL_KEY, SIMPLE_INT_KEY, or SIMPLE_DATE_KEY, then the LookupTableValue table is used to store key-value pairs. In this case, the LookupTable.valueType defines the type of the value in the key-value pair. The key is determined by LookupTable.type, while the value is determined by the LookupTable.valueType.
If the LookupTable.type is RANGE, then the LookupTableValue table is used to store key-value pairs, where the value is a pair of integers. This pair denotes a range. In this case, the LookupTable.valueType defines the type of the key in the key-value pair.

The LookupTable.valueType is used for different purposes depending on the LookupTable.type.

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the LookupTableValue table.

LookupTable.type – SIMPLE | SIMPLE_REAL_KEY | SIMPLE_INT_KEY | SIMPLE_DATE_KEY | RANGE

LookupTable.valueType – REAL | INT | STRING | DATE

Value Table – LookupTableValue

The LookupTableValue has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

name – A primary key that can be used to uniquely identify a row. If LookupTable.type is RANGE, the LookupTable.valueType determines the type of name. Otherwise, the type of name is determined by LookupTable.type.

value – If and only if the LookupTable.type is not RANGE, this property is used to store a value. The type of the value is determined by LookupTable.valueType.

lowerBound – If and only if the type is RANGE, this property is used to store an integer that denotes the minimum value.

upperBound – If and only if the type is RANGE, this property is used to store an integer that denotes the maximum value.

JsonLookupTableValue

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the JsonLookupTableValue table.

LookupTable.type – JSON

LookupTable.valueType – JSON

Value Table – JsonLookupTableValue

The JsonLookupTableValue has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.

JsonLookupTableValue2Key

When an instance of LookupTable has the following combination of type and valueType, the lookup table’s rows will be stored in the JsonLookupTableValue2Key table.

LookupTable.type – JSON

LookupTable.valueType – JSON2

Value Table – JsonLookupTableValue2Key

The JsonLookupTableValue2Key has the following properties

lookupTable.id – A reference to the LookupTable row to which this row belongs. This property can be used in queries, but will not be part of the results.