Dynamic Tables

Dynamic tables, which you can also think of as lookup tables or cross-reference tables, are a data structure that can be accessed by all flows under the same organization. A dynamic table consists of a list of rows containing a list of cells, where each cell is associated with a column name. The structure of the table (i.e., the columns) is entirely up to the user and can be dynamically altered during lookup operations.

Dynamic tables are useful for providing common lookup values (e.g., addresses, abbreviations) and cross-referencing IDs or translating values between systems. For example, you might want a table where you can look up a postal code given a city name. Visually, this table would look like the following:

city code

Oslo

0155

San Francisco

94016

Remember, though, that dynamic tables are lists of rows and cells. If you were to retrieve this data using the Customers Management API, it would actually look like the following:

[
  {
    "cells": [
      {
        "columnName": "city",
        "value": "Oslo"
      },
      {
        "columnName": "code",
        "value": "0155"
      }
    ]
  },
  {
    "cells": [
      {
        "columnName": "city",
        "value": "San Francisco"
      },
      {
        "columnName": "code",
        "value": "94016"
      }
    ]
  }
]

Limits

Dynamic tables are not a replacement for a database and are not meant to store messages. Be mindful of the following limits when using dynamic tables:

Property Upper Limit

Table ID

30 characters

Column Name

30 characters

Cell Value

100 characters

Cells per Row

50 cells

Script Access

Dynamic tables can be accessed from the map processor using the following functions:

Function Description 
tableRowLookup(
    tableName,
    keyColumn,
    keyValue,
    autoCreate,
    cellPatch,
    columnsToReturn
)

Retrieves a single row in the table as a JavaScript object. Returns null if not found and autoCreate is false. Throws an error if more than one row is found.

 
deleteTableRows(
    tableName,
    keyColumn,
    keyValue
)

Deletes all rows that match the given key data.

The functions support the following arguments, when applicable:

Argument Description

tableName

Name of the table to access.

keyColumn

Column name of the cell used to identify the row.

keyValue

Value of the cell used to identify the row.

autoCreate

Specifies whether to automatically create a new row if the key data did not find a match. Optional and true by default.

cellPatch

Object containing cells to be patched onto the identified row, or cells to create for a new row if autoCreate is on. Optional and empty by default.

columnsToReturn

Array containing which columns of the row to return. Empty by default (i.e., returns all columns).

Example Usage

The following mapping uses the incoming payload to look up and return a postal code from a postCodes table:

let #row := tableRowLookup("postCodes", "city", #input.payload)
return #row.code

Note that tableRowLookup() returns a JSON object containing every cell that belongs to the identified row. In the above example, this would look like the following:

{
  "city": "Oslo",
  "code": "0155"
}

If you only wanted code returned, the function could be updated with a columnsToReturn array, as the following demonstrates:

tableRowLookup("postCodes", "city", input, true, null, ["code"])

Patching

By default, autoCreate is set to true, so the tableRowLookup() function will create a new row if one couldn’t be found with the given key data. Additional cells can be added during row creation by providing a cellPatch object as the function’s fifth argument. For example, given the following (unsuccessful) lookup attempt:

tableRowLookup("postCodes", "city", "Stockholm", true, {
    code: "103 16"
})

The following data would be created in the table and returned as a JavaScript object:

{
  "city": "Stockholm",
  "code": "103 16"
}

Whether autoCreate is set to true or false, a provided cellPatch object will still update or add cell values to a successfully identified row. In the following example, the code cell is updated while the country cell is created for the first time:

tableRowLookup("postCodes", "city", "Oslo", false, {
    code: "0010",
    country: "Norway"
})

When constructing cellPatch objects, you also have access to the following auto-created values:

Value Description

@@copyFromKey@@

Copies the value of the key used for lookup.

@@number@@

Generates a sequential number, starting at 1.

@@uuid@@

Generates a random v4 UUID.

These @@ values only apply to new cells and won’t overwrite existing data. For example, the following table lookup would create an id cell if one doesn’t already exist for the identified row:

tableRowLookup("postCodes", "city", "Oslo", false, {
    id: "@@uuid@@"
})

Testing

The Utilihive SDK includes special builders and functions to help with testing table lookups locally. Temporary table data is created with the tableRow builder and cell() function. These are used in conjunction with functions that are specific to unit or functional tests.

Unit Tests

When testing mappings outside of a flow, use the addDynamicTableRow() function to add table data to the test context. In a mapping unit test, this would look like the following:

withMap(myMapping) {
    addDynamicTableRow(
        "postCodes",
        tableRow {
            cell("city", "Oslo")
            cell("code", "0155")
        }
    )

    ...
}

Functional Tests

When writing tests for actual flows, use the tableData() function to add table data to the test context. Unlike unit tests, however, the table name is established as part of the tableId() function. For example:

ctx.addFlowTestConfig {
    tableData(
        tableId(OWNER_ID, "postCodes"),
        tableRow {
            cell("city", "Oslo")
            cell("code", "0155")
        }
    )

    ...
}
If you are running concurrent tests on the same table, the SDK will establish a unique table ID per test, but only if each test context was set up with a table. Even if one of the tests is intended to start with no data in the table, you would still need use the tableData() function (with no tableRow data) to register the table name. This will prevent interference with other tests.