Lookup Tables
What is a Lookup Table?
Available from version 8.3 and later.
A lookup table is reference data that you manage in Factry Historian and read from calculations. It holds rows and typed columns, like a small spreadsheet: product codes with their target weights, recipes with their temperature limits, or shift codes with their names.
Each column has a name and a type: String, Number or Boolean.
Why does it matter?
Calculations often need values that are not measured: a target, a limit or a conversion factor that depends on the product or the line. Without a lookup table, those values end up hard-coded in scripts or stored as metadata on many assets.
A lookup table keeps that reference data in one place:
- Change a target once, and every calculation that reads the table uses the new value from then on.
- Keep scripts generic. The script looks up the value instead of carrying it.
- Let process engineers maintain the data without editing scripts.
How does it fit in the system?
Lookup tables are managed on the Lookup tables page in the main menu. You can:
- Create a table and edit its columns and rows in the browser.
- Import rows and columns from an Excel or CSV file. An import replaces every row and column of the table.
- Export the saved rows to an Excel file.
A calculation or a calculated event property reads a table through an input of type LookupTable with an alias, such as _Thresholds. The script queries that alias with a list of columns to return and a filter on the rows. The result is a list of the matching rows.
The flow:
Some rules to keep in mind:
- Lookup tables belong to an organization, like other configuration.
- Reading a table needs the READ_LOOKUP_TABLES privilege. Creating and changing tables needs MANAGE_LOOKUP_TABLES, and the lookup tables feature in the license. See Users and Groups.
- Saving or running a calculation script needs READ_LOOKUP_TABLES, because a script can read any table its author picks as an input.
- Historian keeps lookup tables in memory, so scripts read them quickly. The general settings hold a memory warning level and a memory limit. Historian refuses to load or change a table when the tables in memory would cross that limit.
- The delete dialog lists every resource that depends on the table, directly or through another resource. Confirming deletes the table and all of them.
Syncing from an external database
A lookup table can also be filled from a query on an external database, instead of by hand. The sync runs on an interval, or on demand with Sync now. A failed sync keeps the existing rows and shows on the homepage issues panel.
Syncing lookup tables is experimental and off by default. To turn it on, add lookuptablesync to the FEATURE_TOGGLES_ENABLE environment variable (comma-separated) and restart Factry Historian.
Example
You make several products on one line, and each product has its own maximum temperature. You create a lookup table called Thresholds:
ProductCode | MaxTemperature |
|---|---|
A100 | 72.5 |
B200 | 80 |
C300 | 65 |
A calculation has two inputs: the current product code as _A, and the lookup table as _Thresholds. Its script returns the maximum temperature for the product that is running:
rows := _Thresholds.query(["MaxTemperature"], "ProductCode == params.code", {
code: _A.Value
})
if len(rows) == 0 {
return undefined // no matching row for this product code
}
return rows[0].MaxTemperatureWhen a new product is introduced, you add one row to the table. The calculation does not change.
Common misconceptions
- A lookup table is not a time series. It holds the current reference values, with no history. Changing a row does not recalculate values that were already written.
- A lookup table is not a place for process data. Values that change with the process belong in measurements.