Building blocks

The Pack includes multiple different options for how to run a query, returning data as lists, JSON, a table, etc.

⁠

QueryGrid() formula

This formula runs a query on a set of tables, and returns the results as a List of Lists. The outer list has one entry for each row, and the inner lists have one entry for each columns. Use list methods (First(), Last(), Nth(), Filter(), etc) to work with the results.

Query

⁠

Employees that start with “M”

⁠

SELECT `First Name`, Title FROM Employees WHERE `First Name` LIKE 'M%';

⁠

Result

⁠

[MargaretSales Representative][MichaelSales Representative]

⁠

=Result.First().Last()

⁠

Sales Representative

⁠

QueryJSON() formula

This formula runs a query on a set of tables, and returns the results as a JSON string. The JSON contains an array of objects, with one object for each row in the results. The object has a key/value pair for each column, using the column name as the key. Use the ParseJSON formula to work with the results.

Query

⁠

Employees that start with “M”

⁠

SELECT `First Name`, Title FROM Employees WHERE `First Name` LIKE 'M%';

⁠

Result

[ { "First Name": "Margaret", "Title": "Sales Representative" }, { "First Name": "Michael", "Title": "Sales Representative" } ]

⁠

=ParseJSON(Result, "$[0].Title")

⁠

Sales Representative

⁠

QuerySingle() formula

This formula runs a query over a set of tables, and returns a string containing the value of the first column of the first row. It’s useful when your query returns a single value and you want to access it directly.

Query

⁠

Count of employees

⁠

SELECT COUNT(*) FROM Employees;

⁠

Result

9

⁠

QueryTable sync table

This sync table runs a query over a set of tables, and returns the result as a table. This is often the most natural way to work with a set of results, but also requires that you resync the table every time you change your query.