Lookup tables
Available from version 8.3 and later.
A lookup table holds reference data in Historian: rows of values in named, typed columns. Calculations and calculated event properties read a lookup table and filter its rows, for example to find the temperature limit of the product code a line is running.
Use a lookup table for data that changes rarely and that you would otherwise hard-code in scripts: product specifications, thresholds per recipe, mappings from codes to names.
Where to find lookup tables
Lookup tables have their own page: click Lookup tables in the main menu. The menu item shows when you have the READ_LOOKUP_TABLES privilege and your license includes lookup tables. The page lists every lookup table of the organization with its name, description and columns.
Columns and rows
Every column has a name and a type: String, Number or Boolean. On a manual table, the type of a saved column cannot be changed. The names row and params are reserved and cannot be used as column names.
An import, a sync or an API call that replaces all rows of a table can write at most 500,000 rows. The name of a lookup table must be unique within the organization.
A lookup table only holds its current rows. It keeps no history, so a calculation always reads the rows as they are when it runs.
Privileges and license
- READ_LOOKUP_TABLES lets a user see lookup tables and their rows, and export them.
- MANAGE_LOOKUP_TABLES lets a user create, change, import and delete lookup tables.
The Administrators and Managers groups get both privileges. The Reader group gets READ_LOOKUP_TABLES.
Saving or running a calculation script requires READ_LOOKUP_TABLES, because a script can read any lookup table it takes as an input. When you upgrade to version 8.3, user groups that can manage measurements, event configurations or prototypes get READ_LOOKUP_TABLES. Add it by hand to user groups you create later.
Creating and changing lookup tables needs the lookup tables feature in your license. Deleting a lookup table does not.
Memory use
Historian keeps a lookup table in memory once something has read it. Two settings under Configuration > General settings > Lookup tables limit that memory:
- Lookup table memory warning: Historian logs a warning when the lookup tables in memory pass this many bytes. The default is 402653184 (384 MiB).
- Lookup table memory limit: Historian refuses to read or write a lookup table when the lookup tables in memory would pass this many bytes. The default is 536870912 (512 MiB).
Set a value to 0 to turn the warning or the limit off.