Lookup Tables
Available from version 8.3 and later.
A lookup table holds reference data in rows and typed columns, for example product codes with their target weights. Calculations and calculated event properties read lookup tables through an input. Lookup tables belong to one organization.
Lookup tables page
Lookup tables are managed on the Lookup tables page in the main menu. The page needs the READ_LOOKUP_TABLES privilege and the lookup tables license feature.
The list shows the Name, Description, number of Columns and UUID of every table. With lookup table sync turned on, it also shows the Source and the Sync status.
- Create lookup table opens an empty table in the detail panel.
- The dropdown next to it holds Import, which creates a new table from a file. See Import.
Selecting a table opens its detail panel with two tabs:
- Table: the rows of the table.
- Settings: the name, description and columns of the table, and the resources that reference it.
The header of the detail panel holds Export, plus Import for a manual table or Sync now for a synced table.
Settings
The Settings tab holds the following fields.
Name
Description: The name of the table. Required, and unique within the organization.
Description
Description: A free text description (optional).
Source
Description: Where the rows come from: Manual (users write them) or External database (a sync writes them). Only shown while lookup table sync is turned on. The source is set when the table is created and cannot change.
Referenced by
Lists the calculations, measurement prototypes, event property configurations and event property configuration prototypes that have the table as an input. Only resources you may read are named.
Deleting a table that is referenced also deletes every resource that depends on it, directly or through another resource, after a confirmation that lists them all. This includes calculations and event property configurations that read the table, their prototypes, and calculations that use a deleted calculation as input. To confirm, type the name of the table. The delete is only offered when you may manage every resource in the list. Through the API, deleting a referenced table needs the force parameter, and is refused without it.
Columns
Each column has a Column name and a Column type. Add a column with Add column, change the order with the arrow buttons and remove a column with the trash button.
- A column name is required and unique within the table.
- row and params are reserved and cannot be used as column names, because a lookup table filter uses them.
- For a manual table, the type of a column cannot change once the column is saved. To change it, export the table and import the file again with the new column type. Removing the column and adding it again clears its values. On a synced table, the type of a saved column can be changed, see Lookup table sync.
- Renaming or reordering a column keeps its values.
- Removing a column clears its values from every row.
A table has no key column. Rows can hold the same values more than once.
Column types
Type | Values |
|---|---|
String | Text, stored exactly as entered. |
Number | A 64-bit floating point number. |
Boolean | true or false. |
A cell of any type can be empty. A script reads an empty cell as undefined.
Rows
The Table tab shows the rows in pages of 100 by default. To change rows, click Edit, then:
- Click a cell to change its value. A boolean cell is a checkbox.
- Clear a cell with its clear button to leave it empty.
- Add a row with Add row, and remove one with its trash button.
- Click Save to write the changes, or Cancel to discard them.
The rows keep their order. A script query returns matching rows in that order.
The rows of a synced table are written by its sync only, and cannot be edited.
Import
Import reads the columns and rows of a table from a file.
- From the list, it creates a new table. Give the table a name in the import dialog.
- From the detail panel of a table, it replaces every row and every column of that table. This cannot be undone, so export the table first if you need a copy.
The import runs in steps:
- Select an .xlsx or .csv file. Leave The first row holds the column names checked if the file has a header row.
- Check the columns. Each column can be renamed, given another type, or left out with Include. The dialog shows the first 20 rows.
- Confirm. Historian writes the table in one step.
File format
- An .xlsx file is read from its first sheet. Other sheets are ignored.
- A .csv file uses a comma or a semicolon as separator, whichever the first line holds most. A UTF-8 byte order mark is removed.
- The file can be at most 16 MiB and hold at most 500,000 rows.
- Empty rows at the end of the file are ignored.
- A column without a header name is named Column 1, Column 2 and so on, by position. A repeated or reserved name gets a suffix, for example Weight (2).
Column types on import
The type of each column is detected from its first 1,000 values:
- Boolean when every value is true, false, yes or no (in any case), or every cell is a boolean cell in the .xlsx file.
- Number when every value is a number.
- String otherwise, and for a column without values.
A column of only 1 and 0 is detected as a number. Change its type to boolean in the dialog if it holds flags.
When importing into a boolean column, true, yes and 1 read as true, and false, no and 0 read as false. An empty cell stays empty in every column type.
Import errors
When a cell does not fit the type of its column, the dialog lists the problem cells with their row, column and value, and the import cannot continue. The list shows the first 50 problems and the total count. Change the column type or correct the file, then try again.
A file that is not .xlsx or .csv, that is larger than 16 MiB, or that holds more than 500,000 rows is refused before the dialog shows its columns.
Export
Export downloads the saved rows of the table as an .xlsx file named after the table. Unsaved changes are not included. The workbook holds one sheet with the column names in the first row and one row per table row. Numbers and booleans are written as numbers and booleans, so importing the file again detects the same column types.
Export needs the READ_LOOKUP_TABLES privilege.
Reading a lookup table in a script
A calculation or a calculated event property reads a lookup table through an input of type LookupTable. The alias of that input holds a query function that returns the matching rows:
rows := _Thresholds.query(["MaxTemperature"], "ProductCode == params.code", {
code: _A.Value
})
if len(rows) == 0 {
return undefined
}
return rows[0].MaxTemperatureA query reads the table as it is when the script runs. Changing a table does not recalculate values that were already written.
Lookup table inputs can be used in raw, sampled and simulated calculations, in calculation prototypes, and in calculated event properties. See Scripting for the arguments, the filter syntax and the errors of query.
Privileges
- READ_LOOKUP_TABLES shows the Lookup tables page, the rows of every table and the Export button.
- MANAGE_LOOKUP_TABLES is needed to create, change, import into and delete a table.
- Working with calculation scripts and calculated event properties also needs READ_LOOKUP_TABLES, because a script can read any table of the organization. Privileges lists the actions.
- Configuring, previewing and running a sync also needs READ_EXTERNAL_DATABASES.
The predefined Administrator and Manager groups hold both lookup table privileges. The Reader group holds READ_LOOKUP_TABLES. See Privileges.
Creating or changing a table needs the lookup tables license feature. Deleting a table does not.
Memory limits
Historian holds every lookup table that is read in memory, so calculations do not query the database for each row. A table is loaded on its first read and loaded again after a change. All tables of all organizations on the server share one memory budget, which two settings in the general settings control:
- Lookup table memory warning (default 384 MiB) logs a warning when the tables in memory pass it. Nothing is refused.
- Lookup table memory limit (default 512 MiB) refuses to load a table, or to write rows or columns, when the tables in memory would pass it. The request fails with status 507, and a calculation that queries such a table fails its evaluation. The error says to raise the limit or to ask an administrator. The memory sizes only appear in the Historian log.
The sizes are estimates of the memory the tables take, not a measurement of the process.
More information
- Scripting
- Privileges