---
title: Lookup Tables
slug: reference/lookup-tables
docTags: 5fJw2a1qRamk_WQBiogL7
createdAt: 2026-10-30T09:00:00.000Z
---

*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](https://docs.factry.io/reference/calculations) and calculated [event properties](https://docs.factry.io/reference/event-property-configuration) 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](https://docs.factry.io/reference/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](https://docs.factry.io/reference/lookup-tables#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](https://docs.factry.io/reference/lookup-table-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](https://docs.factry.io/reference/scripting) 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](https://docs.factry.io/reference/lookup-table-sync#scheduling).
- 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:

1. Select an `.xlsx` or `.csv` file. Leave **The first row holds the column names** checked if the file has a header row.
2. Check the columns. Each column can be renamed, given another type, or left out with **Include**. The dialog shows the first 20 rows.
3. 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:

:::CodeblockTabs
Tengo

```go
rows := _Thresholds.query(["MaxTemperature"], "ProductCode == params.code", {
  code: _A.Value
})

if len(rows) == 0 {
  return undefined
}
return rows[0].MaxTemperature
```
:::

A 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](https://docs.factry.io/reference/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](https://docs.factry.io/reference/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](https://docs.factry.io/reference/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](https://docs.factry.io/reference/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

- [Lookup table sync](https://docs.factry.io/reference/lookup-table-sync)
- [Scripting](https://docs.factry.io/reference/scripting)
- [Privileges](https://docs.factry.io/reference/privileges)
- [General settings](https://docs.factry.io/reference/general-settings)
