Table functions

Function	Description	Example Expression	Example Result
Count(table)	Returns the number of rows in a table.	Count([col: "A"] & [col: "B"])	2
Any(table)	Returns a truth (Boolean) value indicating whether the table has any rows.	Any([col: "A"] & [col: "B"])	TRUE
Skip(table, n)	Returns a table where first n rows have been excluded.	Skip([col: "A"] & [col: "B"] & [col: "C"], 1)	<[col: "B"] & [col: "C"]>
Take(table, n)	Returns a table where first n rows are included in result.	Take([col: "A"] & [col: "B"] & [col: "C"], 2)	<[col: "A"] & [col: "B"]>
First(table)	Returns a record representing the first row of a table.	First([col: "A"] & [col: "B"])	<[col: "A"] as Record>
Sum(table.column)	Returns the summarized value of the specified table column.	Sum([col: "4"] & [col: "7"])	11
All(table, predicate)	Returns a Boolean value indicating whether the predicate expression holds tree for all rows in the table.	All([a: 1] & [a: 2], {a > 1})	false
Eval(table)	Forces evaluation of a lazy table expression. Rarely necessary.	Eval(numbers where x < 0)	<numbers table unchanged>
CsvFill(prototype, data, separator)	Creates a Flow table from a string value containing CSV data.	See below	See below
XmlFill(prototype, data, rootXpath)	Creates a Flow table from a string value containing XML data	See below	See below

The WHERE keyword

You can use the WHERE keyword to create a filtered version of a table variable. Since this filtering happens locally on the device where the Flow client is run, it is not suitable for large amounts of data.

The WHERE keyword takes a table on the left hand side and a logical expression (with the scope of the table's columns) on the right hand side.

Since the WHERE keyword returns a new table, you can nest it in any table function, such as First, Any, Count, etc.

let orders = [id: 1, isConfirmed: 0, plannedDate: Now()] & 
             [id: 2, isConfirmed: 1, plannedDate: Now() + 1] & 
             [id: 3, isConfirmed: 0, plannedDate: Now() + 2];
let futureOrders = orders where isConfirmed and plannedDate > Now();
let nextOrderToProcess = first(orders where isConfirmed);
let numberOfOrder = count(orders where isConfirmed);

The ORDER BY keyword

ORDER BY sorts a table by one column and returns a new sorted table. As default the order is asending but by adding the keyword DESC the order can be changed to descending (ASC can be added for explicitly when sorting ascending). Only simple types can used for sorting.

let items = [id: 1, price: 10, name: 'Soda'] & 
            [id: 2, price: 100, name: 'Burger'] & 
            [id: 3, price: 50, name: 'Fries'];

let cheapItem = First(items order by price);
let expensiveItem = First(items order by price desc);

The SUM function

The Sum function calculates the sum of the values in a numeric column in a table.

let projectedProfit = sum((orders where isConfirmed).value) - sum(expenses.cost);

The JOIN function

The Join function concatenates all values of a table column into a single textual value with a given separator.

In the example below, the orderNo column of the orders table is aggregated into a comma-separated string in the format <orderNo 1>, <orderNo 2>, <orderNo 3> etc.

let orderNumbers = join(orders.orderNo, ', ');

The MAP-AS construct

The Map-As construct is used to create a new table variable based on an existing table variable, changing the column names and adding or removing new columns. The syntax of the map-as construct is MAP <source table> AS <record literal>. The record literal to the right of the AS keyword has the scope of the table.

The example below shows an orderLines table being transformed, keeping the salesPart column but adding a new column called cost, which is calculated using the price and discountPercent columns of the orderLines table.

let newOrderLines = map orderLines as [salesPart, cost: price * discount];

CSV data

The CsvFill function can be used to parse CSV data into a Flow table. In order to do this, the function needs a prototype table, which is an empty table specifying the columns expected in the CSV data. If not all columns are present in the CSV data, the function will fail.

Given the following semicolon-separated CSV:

name;lastName;age
Bob;Smith;50
Alice;Smith;60
Per;Lagerqvist;40

...you can use the CsvFill function as such:

let prototype = table(name, lastName, age);
let result = CsvFill(prototype, csvData, ";");

// the 'result' variable will be a table containing the data from the CSV block

XML data

Similarly, the XmlFill function can be used to parse simple XML data into a Flow table. In order to do this, the function needs a prototype table, which is an empty table specifying the columns expected in the XML data. If not all columns are present in the XML data, the function will fail.

The third argument of the XmlFill function can be used to specify an XPath expression pointing out the root element under which each row is found.

Given the following XML:

<animals>
    <animal>
        <name>cat</name>
    </animal>
    <animal>
        <name>dog</name>
    </animal>
</animals>

...you can use the XMLFill function as such:

let prototype = table(name);
let result = XmlFill(prototype, xmlData, "");

// The 'result' variable will contain two rows corresponding to the <animal>
// elements in the XML above.