Skip to content
Lainstack Docs

Look up reference data

A reference table holds data your automations look things up in: codes and their meanings, contacts, mappings between two systems’ identifiers. Reference tables live under Data Lake in the workspace rail. This guide is for authors and admins who set them up.

Creating tables and writing to them needs the Organization admin or Operations director role.

1. Create the table

Section titled “1. Create the table”
  1. Open Data Lake and click New reference table.
  2. Enter a Name and, if useful, a Description.
  3. Add the columns. For each, enter a Column name, pick a Type and click Add column. Columns accept empty values unless you untick Allow null.
  4. Choose the Primary key: the column that identifies each row, such as a code. It must be a text, number, yes or no, or choice column that cannot be empty.
  5. Click Create Reference Table.
The Data Lake page listing reference tables, with the New reference table button.
The Create a Reference Table page with its columns and primary key.

If you already have the data in a CSV file, Import CSV schema reads the file’s columns and types for you. Only the columns are taken; the rows are not imported, and the file stays in your browser.

2. Fill it from an automation

Section titled “2. Fill it from an automation”

You never type rows into a table. Rows come from an automation, so every change is repeatable and recorded in a run. When a row arrives with a primary key that already exists, it replaces that row; new keys add rows.

A typical loader:

  1. An Operation trigger with a file upload, so someone can submit a CSV file from Operations.
  2. Parse CSV to turn the file into a table.
  3. Write Reference Table: pick the Reference Table, and set Table to the parsed table. Columns are matched to the reference table’s columns.
A loader: someone submits a CSV file, and its rows land in the table, replacing rows with the same key.

For a handful of fixed rows, set Table to Build rows and enter them in the block instead.

One write takes up to 1,000 rows, and each primary key may appear only once in it. The write lands all at once or not at all. A draft run holds the write, so stored data changes only in a live run.

Publish the loader and run it. The table’s Data tab then shows the first 100 rows and the Last load attempt.

A product library reference table's Data tab, with its last load attempt and rows of SKUs, invoice wordings and commodity codes.

3. Look records up

Section titled “3. Look records up”

In the automation that needs the data, use one of these blocks:

Block Use it to
Find Required Reference Record Get the one row whose Match column equals a Match value, such as a code from the trigger.
Read Reference Table Get every row, for example to loop over them with For each item.
A lookup with the Not found exit wired. Without it, a miss pauses the run and asks a person for the right value.

Find Required Reference Record is meant for data the run cannot do without. If nothing matches:

  • with its Not found exit wired, the run takes that exit;
  • without it, the run pauses and asks a person for a corrected match value, then tries again. See Escalations and approvals.

More than one matching row fails the step, so match on a column whose values are unique.

Change or retire a table

Section titled “Change or retire a table”
  • On the Columns tab, Add nullable column adds a column that may be empty. Existing rows keep working.
  • Archive table makes it read only. Automations that use an archived table fail at that block until you click Restore table or point them at another table.

Next

Section titled “Next”

Last updated: