Skip to main content

Lookup tables

A lookup table is a list of keys and values your flows can read, such as carrier names to SCAC codes, store numbers to warehouses, or partner SKUs to your own. Keeping these in a table instead of in each flow means you change a value once and every flow picks it up on its next step, with no new version to publish.

Flows read a table with the lookup function:

{{ lookup('carrier_scac', trigger.payload.carrier, 'UNKN') }}

The first argument is the table's slug, written as quoted text. The second is the key, usually a reference. The optional third is what to use when the key is not in the table; without it, a missing key resolves to nothing.

Tables and rows​

LimitValue
Tables per organization50
Rows per table50,000
Key1 to 256 characters, unique within the table
ValueAny JSON value (text, number, true or false, object, or list) up to 4 KB
SlugLowercase letters, digits, hyphens, and underscores, starting with a letter, up to 64 characters

Keys match exactly, including case and spaces. A number used as a key is matched as its text, so lookup('zones', 3) reads the key 3. Combine with upper or trim when the incoming data varies: {{ lookup('carrier_scac', upper(trim(trigger.payload.carrier))) }}.

A value can be an object, and the whole object comes back:

{
"key": "map_carrier",
"type": "transform.js",
"config": {
"input": "{{ lookup('carrier_map', trigger.payload.carrier) }}",
"code": "return { scac: data.scac, service: data.default_service }"
}
}

The slug cannot change after the table is created, because flows refer to it. A table that a published flow uses cannot be deleted; publishing a flow that uses a table that does not exist is refused.

How lookups run​

Before a step runs, Orderly finds every lookup call in its settings and loads just the keys it needs, with one query per table. A collection.map step loads the keys for all of its items at once. Lookups are read when each step runs, so a run that is waiting or retrying sees the table as it is at that moment.

Manage tables with the API​

All routes are under /api/v1/flows/lookup-tables. Reading needs the flows:read scope and changing needs flows:write.

MethodPathDescription
GET/api/v1/flows/lookup-tablesList tables, each with its row_count
POST/api/v1/flows/lookup-tablesCreate a table: slug, name, optional description
GET/api/v1/flows/lookup-tables/{tableId}Get one table
PATCH/api/v1/flows/lookup-tables/{tableId}Change name or description
DELETE/api/v1/flows/lookup-tables/{tableId}Delete a table and its rows. Refused with 409 while a published flow uses it
GET/api/v1/flows/lookup-tables/{tableId}/rowsList rows by key: prefix, after, and limit (1 to 500, default 100)
PUT/api/v1/flows/lookup-tables/{tableId}/rowsAdd or update up to 1,000 rows: { "rows": [{ "key", "value" }] }
POST/api/v1/flows/lookup-tables/{tableId}/rows/deleteDelete up to 1,000 rows: { "keys": [...] }
POST/api/v1/flows/lookup-tables/{tableId}/importImport a CSV file
GET/api/v1/flows/lookup-tables/{tableId}/exportDownload the table as CSV
curl -X POST https://api.orderly-hub.com/api/v1/flows/lookup-tables \
-H "Authorization: Bearer $ORDERLY_API_KEY" \
-H "X-Organization-Id: $ORG_ID" \
-H "Content-Type: application/json" \
-d '{ "slug": "carrier_scac", "name": "Carrier SCAC codes" }'

curl -X PUT https://api.orderly-hub.com/api/v1/flows/lookup-tables/$TABLE_ID/rows \
-H "Authorization: Bearer $ORDERLY_API_KEY" \
-H "X-Organization-Id: $ORG_ID" \
-H "Content-Type: application/json" \
-d '{ "rows": [ { "key": "FEDEX", "value": "FDEG" }, { "key": "UPS", "value": "UPSN" } ] }'

Rows are listed in key order, 100 at a time by default. When there are more, pagination.next_after holds the last key; pass it as after to get the next page.

Every row in a write is checked before anything is saved. When any row is invalid, nothing is written and the 422 response lists up to 20 problems in details.errors, each with its row, key, and message.

Import and export CSV​

POST /import takes the CSV file itself as the request body (Content-Type: text/csv), up to 5 MB. The first row is the header and must include a key column.

  • When the only other column is value, each row's value is that cell, as text. Add ?value_type=json to read each cell as JSON instead, so 42, true, and {"scac":"FDEG"} keep their types.
  • With any other columns, each row's value is an object of those columns:
key,scac,default_service
FEDEX,FDEG,FEDEX_GROUND
UPS,UPSN,GROUND

imports FEDEX as { "scac": "FDEG", "default_service": "FEDEX_GROUND" }.

Choose how the file is applied with mode:

  • merge (the default) adds new keys and updates existing ones, leaving other rows alone.
  • replace makes the table exactly the file: rows not in the file are removed. A file with only a header empties the table.

The whole file is checked first (one value per header column, keys present, unique, and within limits, values within 4 KB, at most 50,000 rows), and then written in one step: either every row is saved or none is. The response reports rows_written and the table's new row_count.

curl -X POST "https://api.orderly-hub.com/api/v1/flows/lookup-tables/$TABLE_ID/import?mode=replace" \
-H "Authorization: Bearer $ORDERLY_API_KEY" \
-H "X-Organization-Id: $ORG_ID" \
-H "Content-Type: text/csv" \
--data-binary @carrier_scac.csv

GET /export downloads a key,value CSV named after the table's slug. Text values are written as they are and other values as JSON; add ?value_type=json to write every value as JSON, which an import with value_type=json reads back exactly.