Start Expressions are a mechanism used in a workflow template to "iterate" over a list of records, so that they content of each of them can be formatted using an "embedded template" and included in the workflow document (usually an attachment to a workflow email).
There are three common scenarios for Start Expression usage:
- Top-level expression in a scheduled workflow rule: in an attachment to a scheduled workflow rule, you may want to iterate over a set of records (eg: all Orders or all "open" Orders) and include them in a report document.
- Embedded expression in a scheduled workflow rule: within each Order being included in the report document, you may want to iterate over the Order Details line items associated with that Order and include them as well.
- Embedded expression in a change workflow rule: a change workflow rule is fired when a specific record of a specific table is modified. For example, a change workflow rule associated with the Orders table will be triggered when one Order is changed. So the workflow attachment document template is evaluated in the context of that specific changed record. In that document, you may want to include not just the specifics of the order, but also iterate over the Order Details line items associated with that Order.
Each embedded template starts with <<Start:StartExpression>> and ends with <<End>>. A workflow template may contain one or more embedded templates, and multiple levels of nesting are supported.
The Start Expression following the colon yields a list of child records to be formatted using the embedded template. To be more specific, the Start Expression yields the list of key values of the table rows to be formatted using the embedded template.
An app example:
Consider this template which is used in a chage workflow rule when an Orders record is updated. It contains an embedded template to display all of the Order Details records for the updated Orders record. The embedded template contains this Start Expression.
<<Start:[Related Order Details [Order Id]]>>
It is important to remember that the Start Expression is evaluated in the context of the template that contains it. In this case, the outer template is used to format the Orders record, so the Start Expression is evaluated in the context of that Orders record. For example, the Start Expression can make use of the columns in the current Orders record.
By contrast, the expressions between <<Start>> and <<End>>, other than the Start Expressions, are evaluated in the embedded context of each child record. In this case, since the Start Expression refers to Order Details records, the remaining expressions between <<Start>> and <<End>> are evaluated in the context of the child Order Details records. The embedded template is evaluated once for each child record in turn. For example, if there are five child Order Details records, the embedded template is evaluated five times.
Start Expression variations:
The Start Expression may take several forms.
An entire table
The simplest Start Expression is the name of a table and its key column
This form of Start Expressions is typically only meaningful as the top-level Start expression in a scheduled workflow rule
A common Start Expression is the name of a Reverse Reference virtual column. For example:
<<Start:[Related Order Details [Order Id]]>>
The Orders record contains the Reverse Reference virtual column "Related Order Details [Order Id]". This virtual column was automatically added by AppSheet to contain the reverse references from the Orders table to the child Order Details table. It contains the list of key values of the related child Order Details records. In the example above, the column name "Related Order Details [Order Id]" is enclosed in square brackets.
You can display a subset of the child records by specify a Select expression that yields the key values of the child records you wish to display.
<<Start: SELECT(Orders[Order Id], AND(IN([Order Id],[_THISROW].[Related Orders [Customer Name]]), [Order Status] = "Open"))>>
Please note that it is important that the select expression provide a list of key values.
Controlling and limiting the Start Expression:
In a report attached to a workflow email, you may want to control the order of the records listed. Further, you may want to control the total number of records listed.
Control the order of records
You can use OrderBy to control the order in which child records are displayed. For example, you could display the Order Details records in order from most expensive to least expensive using this Start Expression. The Total column contains the total value of each Order Details record.
<<Start:OrderBy([Related Order Details [Order Id]], [Total], FALSE)>>
The parameters to the OrderBy() function are OrderBy(ListOfRecords, SortColumn, SortDescending?).
Control the total number of records
You can limit the total number of records displayed by using the Top() function. For example, you could ask for no more than 3 records as follows:
<<Start:TOP(OrderBy([Related Orders [Customer Name]], [Order Date]), 3)>>
The parameters to the Top() function are Top(OrderedListOfRecords, MaxNumberOfRecords). Note that Top() only works with OrderBy and cannot be used in isolation.