Relation functions

Bring a value from another table, a piece of JSON or an array into this column. xlookup is the function the Match panel writes; json.select usually pairs with an HTTP connection or a JSON import.

xlookup

Searches a table, matches conditions to retrieve values

Formula Description

Searches the original table, matches conditions for equality, and retrieves the value from the "original table content column" to write into the result table.

Formula Syntax

In the result column, write: XLOOKUP(result table condition matching column, original table condition matching column, original table content column to lookup)

• All parameters can use data columns, i.e., the CODE at the top of each column in the table data, such as AA1
• Result table condition matching column: The column to match in the result table (the table where the current formula is being written)
• Original table condition matching column: The column to search and match in the original table
• Original table content column to lookup: The column in the original table from which to retrieve content

vlookup

Formula Description

Searches for a value in the first column of a table and returns a value in the same row from a specified column.

Formula Syntax

In the result column, enter: vlookup(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value to search for in the first column of the table
• table_array: The range containing the lookup value and return value
• col_index_num: The column number to return the value from
• range_lookup: Optional, specifies the match type, default is exact match

json.select

Formula Description

Retrieves a value from JSON data using a key.

Formula Syntax

json.select("JSON data string", "key to select")

• JSON data string: Must be a valid JSON string
• If the key to select doesn't exist in the data, it returns an empty value
• All parameters can use data columns, i.e., the CODE at the top of each column in the table data, such as AA1

valueat

Formula Description

Retrieves the nth value from an array-type string.

Formula Syntax

In the result column, enter: valueat(array-type string / column number, position n)

• Array-type string: Must be enclosed in "[]", e.g., [1,2,3]; if the string is not array-type, the formula returns an empty value
• Position n: Starts from 1; if the position exceeds the array length, it returns an empty value
• All parameters can use data columns, i.e., the CODE at the top of each column in the table data, such as AA1

union

No detailed description ships in the app for this one — the function help in the formula editor is authoritative.

convertunion

Formula Description

Combines values from multiple columns into a single column.

Formula Syntax

In the result column, enter: convertunion(column_number1, column_number2, ...)

• column_number: The columns to combine, e.g., AA1, AD12

convertselect

Formula Description

Selects specific columns from multiple columns.

Formula Syntax

In the result column, enter: convertselect(column_number1, column_number2, ...)

• column_number: The columns to select, e.g., AA1, AD12

groupby

No detailed description ships in the app for this one — the function help in the formula editor is authoritative.

JSONPath syntax

json.select, the "define the data structure" step of an HTTP connection, and JSON import all use JSONPath to point at a node inside JSON. It is to JSON what XPath is to XML, and accepts both dot notation $.store.book[0].title and bracket notation $['store']['book'][0]['title'].

Taking this JSON as the example:

{ "store": { "book": [
  { "category": "reference", "author": "Nigel Rees", "title": "Sayings of the Century", "price": 8.95 },
  { "category": "fiction", "author": "Evelyn Waugh", "title": "Sword of Honour", "price": 12.99 },
  { "category": "fiction", "author": "Herman Melville", "title": "Moby Dick", "isbn": "0-553-21311-3", "price": 8.99 }
], "bicycle": { "color": "red", "price": 19.95 } } }

When an API returns "an object wrapping an array", point the HTTP connection's data structure at the array level ($.data.list[*]) and each element becomes one row.