Use Start
expressions as described in the following sections:
- Display a list of records using Start expressions in templates
- Access columns in parent and grandparent records
- Common Start expressions
- Control the record order
- Control the number of records displayed
- Troubleshoot Start and End expressions
Start
expressions must appear within a <p>
or heading element.Display a list of records using Start expressions in templates
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. Then, 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
. - Creating an embedded template for
Orders
within theCustomers
template. - Creating an embedded template for
Order Details
within theOrders
template.
An Embedded template with a Start
expression is used in the following use cases.
- Embedded expression in a data change bot
A data change bot is fired when a specific record in a table is modified. For example, a data change bot associated with theOrders
table is triggered when anOrder
is changed. The template is evaluated in the context of the changed record. Your template can display both the specifics of theOrders
record and theOrder Details
records associated with thatOrders
record. Your template must include an embedded template with aStart
expression to display theOrder Details
records. - Top-level expression in a scheduled bot
In a scheduled bot, you can display a set of records. For example, you can display allOrders
or all openOrders
. Your template must include an embedded template with aStart
expression to display theOrders
records. - Embedded expression in a scheduled bot
In a scheduled bot, you can display a hierarchy of records. For example, you can display all openOrders
records along with their relatedOrder Details
records. Your template must include an embedded template with aStart
expression to display theOrders
records. Then create an embedded template forOrder Details
within theOrders
template.
Example
Consider this template which is used in a data change bot 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 the following Start
expression:
<<Start:[Related Order Details]>>
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
expression, are evaluated in the context of each child record. In our example, since the Start
expression refers to the 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.
Access 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.
Access 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: There cannot be any embedded white spaces in the [_THISROW-n]
string. The value n is a constant number value; it cannot be an expression.
Access 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]
=> Top level
[_THISROW-1]
=> One level up to the parent
[_THISROW-2]
=> Two levels up to the grandparent
[_THISROW-3]
=> Three levels up to the great grandparent
[_THISROW-n]
=> n levels up
Common Start expressions
A Start
expression may take several forms. The following provide some common examples.
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 bot.
Reverse reference
A common Start
expression is the name of a reverse reference virtual column. For example:
<<Start:[Related Order Details]>>
The Orders
record contains the reverse reference virtual column Related Order Details
. 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
must be 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][OrderDetail Id], [Order Status] = "Open")>>
The Orders
record contains the reverse reference virtual column Related Order
Details
. In the example above, the column name Related Order Details
must be 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]
. In the example, we needed to rename the column to Related Order Details
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][OrderDetail Id]
. The value [Related Order Details]
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]), [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.
Control the record order
You can use OrderBy
to control the order in which records are displayed.
The parameters to the OrderBy()
function are OrderBy(keys, sort-key, descending-order?)
.
You can display the Order Details
records in order from most expensive to least expensive using the following Start
expression. The Total
column contains the total value of each Order Details
record:
<<Start:OrderBy([Related Order Details], [Total], FALSE)>>
You can display "Open" Orders
records by Order Date
using the following Start
expression:
<<Start:OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],FALSE)>>
You can display "Open" Orders
records by inverse Order Date
using the following Start
expression:
<<Start:OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],TRUE)>>
Control 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: Top
only works with OrderBy
and cannot be used in isolation.
Display at most 3 records as follows:
<<Start:Top(OrderBy([Related Orders], [Order Date]), 3)>>
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)>>
Display the oldest "Open" Orders
record using this Start
expression:<<Start:Top(OrderBy(SELECT(Orders[Order Id], [Order Status]="Open"),[Order Date],FALSE),1)>>
Troubleshoot Start and End expressions
The following list common error messages and how to address them.
Incomplete Start expression
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 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.
Found 1 unmatched 'End'
The error message Found 1 unmatched 'End'
indicates that the expression parser found an End
expression but could not find its matching Start
expression.
Ensure that your Start
expression does not contain an embedded carriage return. Embedded carriage returns 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 somewhere within the Start
expression. For example, you may find a carriage return in the middle of the Start
expression, between <<
and Start
, following Start:
, or before the closing >>
.
Found 1 unmatched 'Start'
The error message Found 1 unmatched 'Start'
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 >>
.
Found n '<<' values but m '>>' values
Ensure that all of your Start
and End
expressions begin with <<
and end with >>
.