You can learn more about sending email, using templates, and using expressions with workflow in the Workflow: Sending Email section.

Start Expressions are used in templates to display a list of records. The Start Expression appearing at the beginning of the template controls which records are displayed. The template is used to format each record.

Each template starts with <<Start:StartExpression>> and ends with <<End>>. The Start Expression following the colon yields a list of records to be formatted using the template. To be more specific, the Start Expression yields the list of key values of the table rows to be formatted using the template.

A template may contain one or more embedded templates. You can display a hierarchy of records by embedding one template within another. For example, you can display Customers, Orders, and Order Details records in a three level hierarchy by creating a template for Customers. Then creating an embedded template for Orders within the Customers template. Then creating an embedded template for Order Details within the Orders template.

An Embedded template with a Start Expression is used in these cases.

  1. 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 is triggered when an Order is changed. The workflow template is evaluated in the context of that specific changed record. Your template can display both the specifics of that Orders record and the Order Details records associated with that Orders record. Your template must include an embedded template with a Start Expression to display the Order Details records.
  2. Top-level expression in a scheduled workflow rule: in a scheduled workflow rule, you can display a set of records. For example, you can display all Orders or all "open" Orders. Your template must include an embedded template with a Start Expression to display the Orders records.
  3. Embedded expression in a scheduled workflow rule: in a scheduled workflow rule, you can display a hierarchy of records. For example, you can display all "open" Orders records along with their related Order Details records. Your template must include an embedded template with a Start Expression to display the Orders records. Then create an embedded template for Order Details within the Orders template.

 An example:

Consider this template which is used in a change 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 By 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. Therefore, 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 context of each child record. In our example, since the Start Expression refers to Order Details records, the expressions between <<Start>> and <<End>> are evaluated in the context of a child Order Details record. The expressions between <<Start>> and <<End>> normally refer to the columns of the Order Details record.

The embedded template is evaluated once for each child record returned by the Start Expression. For example, if there are five child Order Details records, the embedded template is evaluated five times.

Accessing Columns in Parent and Grandparent Records

Within an embedded template, you can refer to the columns of tables of your outer level templates. For example, you might have a three level hierarchy of templates consisting of a Customer record, an embedded child Order record, and a further embedded grandchild Order Detail record.

Accessing a Column in a Parent Record

In the embedded Order Detail template, you refer to columns of the "parent" Order record by specifying a dereference expression of the form:

 [_THISROW-1].[ParentRecordColumnName]

For example, [_THISROW-1].[Order Date] retrieves the value of the Order Date column of the parent Order record.

Note that you must write exactly [_THISROW-n]. There cannot be any embedded white spaces in the string. The number must be a constant. In other words, the value "n" is a constant number value. It can never be an expression.

Accessing a Column in a Grandparent Record

In the embedded Order Detail template, you refer to columns of the "grandparent" Customer record by specifying a dereference expression of the form:

 [_THISROW-2].[GrandparentRecordColumnName]

For example, [_THISROW-2].[Phone] retrieves the value of the Phone column of the grandparent Customer record.

[_THISROW]  => Goes up to the top level.
[_THISROW-1] => Goes up one level to the "Parent".
[_THISROW-2] => Goes up two levels to the "Grandparent".
[_THISROW-3] => Goes up three levels to the "Great Grandparent".
[_THISROW-n] => Goes up n levels.

Start Expression Variations

A Start Expression may take several forms.

An Entire Table

The simplest Start Expression is the name of a table and its key column:

<<Start:Orders[Order Id]>>

This form of Start Expressions is typically only meaningful as the top-level Start expression in a scheduled workflow rule.

Reverse Reference

A common Start Expression is the name of a Reverse Reference virtual column. For example:

<<Start:[Related Order Details By Order Id]>>

The Orders record contains the Reverse Reference virtual column "Related Order Details By 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 By Order Id" is enclosed in square brackets.

Reverse Reference with a Condition

Another common Start Expression is the name of a Reverse Reference virtual column that selects a subset of the referenced records. You can achieve this by enclosing the Reverse Reference in a SELECT expression. For example:

<<Start:SELECT([Related Order Details By Order Id][OrderDetail Id], [Order Status] = "Open")>>

The Orders record contains the Reverse Reference virtual column "Related Order Details By Order Id". In the example above, the column name "Related Order Details By Order Id" is enclosed in square brackets.

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. AppSheet originally named this column "Related Order Details [Order Id]". I needed to rename the column to "Related Order Details By Order Id" to use it in the SELECT expression because the square brackets around '[Order Id]" in the original AppSheet generated name prevent the expression from working.

A SELECT expression in a Start expression must always return a list of key values, so the first argument of the SELECT expression is "[Related Order Details By Order Id][OrderDetail Id]". The value [Related Order Details By Order Id] is the name of the reverse reference column. The value [OrderDetail Id] is the name of the key column of the Order Details table. Taken together, the first argument returns the keys of all referenced Order Details records. 

The second argument of the SELECT expression includes only those referenced Order Details records where the Order Status is "Open".

Select Expression

You can display a subset of the child records by specifying 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 By Customer Name]),  [Order Status] = "Open"))>>

The Select expression must yield a list of key values. In this example, the "Order Id" column is the key of the "Orders" table.

Controlling Record Order

You can use OrderBy to control the order in which records are displayed. 

The parameters to the OrderBy() function are OrderBy(ListOfRecords, SortColumn, SortDescending).

You can 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 By Order Id], [Total], FALSE)>>

You can display "Open" Orders records by Order Date using this Start Expression.

<<Start:OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],TRUE)>>

You can display "Open" Orders records by inverse Order Date using this Start Expression.

<<Start:OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],FALSE)>>

Controlling the Number of Records Displayed

You can limit the maximum number of records displayed by using the Top function. 

The parameters of the Top function are Top(OrderedListOfRecords, MaxNumberOfRecords). Note that Top only works with OrderBy and cannot be used in isolation.

You can display at most 3 records as follows:

<<Start:Top(OrderBy([Related Orders By Customer Name], [Order Date]), 3)>>

You can display the most recent "Open" Orders record using this Start Expression.

<<Start:Top(OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],TRUE),1)>>

You can display the oldest "Open" Orders record using this Start Expression.

<<Start:Top(OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],FALSE),1)>>

Troubleshooting Start and End Expressions

If your Start Expression contains an error and the displayed error message only contains part of your start expression, ensure that your Start Expression does not contain an embedded carriage return. Embedded carriage returns will confuse the expression parser. This is especially common when the Start Expression is contained in a table cell. In this case it is easy to inadvertently enter a carriage return within the Start Expression. 

The error message "Found 1 unmatched 'Start' and 0 unmatched 'End' fragment elements." indicates that the expression parser found a Start Expression but could not find its matching End Expression. Ensure that your End Expression does not contain an embedded carriage return. Embedded carriage returns will confuse the expression parser. This is especially common when the End Expression is contained in a table cell. In this case it is easy to inadvertently enter a carriage return within the End Expression. For example, you may find a carriage return between "<<" and "End" or between "End" and ">>".

Did this answer your question?