Email Change Event Templates

When you define a change workflow Email rule, you can specify a template file to use for the email body or attachment.

The example templates that follow are all based on the Order Capture sample application. This sample includes both an email body template and an attachment template. The variables in the example templates refer to the fields in the Order Capture sample application.

 

Template with Column Name Variables

Let's begin with this very simple template. It displays the field values from a record in the Customers table. The template contains formatted text and column name variables. Each column name variable specifies the name of one field from the Customers record.

This template produces the following output.

 

Template with Expression Variables 

This template contains both column name variables and expressions. It displays field values from the Orders table, field values from its parent Customers table, and computed values from its child Order Details table.

It displays the Customers "Name" field using this expression.

Customer Name: <<[Customer Name].[Name]>>

"[Customer Name]" is the "Customer Name" reference field in the Orders table. It refers to the parent Customers record. "[Customer Name].[Name]" displays the "Name" field from the parent Customers record.

This template computes the total value of all child Order Details records using this expression.

Order Total: <<SUM(SELECT(Order Details[Total], [Order Id] = [_THISROW].[Order Id]))>>

This expression computes the sum of all Order Details records having an "Order Id" field value that matches the "Order Id" in the current Orders record. That is, it computes the sum of all Order Details records for this Orders record.

 

Templates with Embedded Templates

A temple may contain one or more embedded templates within it. This is useful when you want to display a hierarchy of records. For example, you can display a single Customers record. Beneath the Customers record you can display all of its child Orders records. Beneath each Orders record you can display all of its child Order Details records.

Each embedded template that displays child records must be delimited by <<Start:>> and <<End>>. The <<Start:>> and <<End>> elements must match, much like parenthesis do within an arithmetic expression.

 

OrderTemplate in List Format

This template is used when an Orders record is updated.

It contains templates that display:

  1. The single parent Customers record for the updated Orders record displayed in list format.
  2. The single updated Orders record displayed in list format.
  3. The list of child Order Details records for the updated Orders record displayed in list format.

 

OrderTemplate in Table Format

This template is used when an Orders record is updated.

It contains templates that display:

  1. The single parent Customers record for the updated Orders record displayed in list format.
  2. The single updated Orders record displayed in list format.
  3. The list of child Order Details records for the updated Orders record displayed in table format.

The following example contains one header row, one data row, and one trailer row.

The first cell in the data row contains <<Start: Start Expression >> followed by an expression yielding the first data value to be displayed. The Start Expression following the colon yields the list of child records to be formatted using the data row template. In this case, the Start Expression yields the list of child Order Details records for the parent Orders record. The template values between <<Start:>> and <<End>> are used to format each child Order Details record returned by the Start Expression. 

Because the Start Expression refers to the Order Details table, the variables within the data row refer to fields in the Order Details table. For example, <<Quantity>> refers to the Order Details "Quantity" field. <<[Product Id].[Product Name]>> uses the Order Details "Product Id" reference field to retrieve the "Product Name" from the referenced row in the Products table.

The last cell in the data row contains the name of the last data field to be displayed. It is followed by <<End>>.

A trailer row can be used to display summary information. Here we display the total value of all preceding Order Details records. The outer enclosing template is not shown here, but it displays Orders records. Hence, <<Order Total>> refers to the "Order Total" field in the Orders table.

 

Customer Template in List Format

This template is used when a Customers record is updated.

It contains templates that display:

  1. The single updated Customers record displayed in list format.
  2. All child Orders record for the updated Customers record displayed in list format.
  3. All child Order Details records for the parent Orders records displayed in list format.

 

The part of the template that displays Orders records starts with this expression.

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

"<<Start: Start Expression >>" signifies the start of an embedded template. The Start Expression following the colon yields a list of child records to be formatted using the embedded template. The embedded template is terminated by <<End>>. Between <<Start: StartExpression >> and <<End>> you enter the formatted text, variables, and expressions to format each Orders record.

The Start Expression above includes an "OrderBy" to sort the child Orders records in ascending order by the "Order Date" field. If order was not important, you could omit the "OrderBy" and simply specify:

  <<Start:[Related Orders [Customer Name]]>>

You can limit the number of records displayed. Do this by including "TOP". The second argument to top specifies the maximum number of records to be displayed. This argument can be either a constant or an expression.

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

You can select the child records to display. Do this by specifying a SELECT expression. For example, to display Orders having an "Order Status" value of "Open" specify this expression:

  <<Start: SELECT(Orders[Order Id], AND(IN([Order Id],[_THISROW].[Related Orders [Customer Name]]),  [Order Status] = "Open"))>>

 

This template produces the following output.

 

 

Customer Template in Table Format

This template is used when a Customers record is updated.

It contains templates that display:

  1. The single updated Customers record displayed in list format.
  2. All child Orders record for the updated Customers record displayed in list format.
  3. All child Order Details records for the Orders records displayed in table format.

A Google Docs table is used to display the Order Details records. The table may contain zero or more header rows, one data row, and zero or more trailer rows.

The following example contains one header row, one data row, and one trailer row.

The first cell in the data row contains <<Start: Start Expression >> followed by the expression variable for the first data field to be displayed. The Start Expression following the colon yields a list of child records to be formatted using the data row template. In this case, the Start Expression yields the list of child Orders recordrecords for an individual Orders record. The template values between <<Start:>> and <<End>> are used to format each child Orders record. 

Because the Start Expression refers to the Order Details table, the variables and expressions within the data row refer to fields in the Order Details table. For example, <<Quantity>> refers to the Order Details "Quantity" field. <<[Product Id].[Product Name]>> uses the Order Details "Product Id" reference field to retrieve the "Product Name" from the referenced row in the Products table.

The last cell in the data row contains the expression variable for the last data field to be displayed followed by <<End>>.

A trailer row can be used to display summary information. Here we display the total value of all preceding Order Details records. The outer enclosing template is not shown here, but it displays Orders records. Hence, <<Order Total>> refers to the "Order Total" field in the Orders table.

This template produces the following output.

 

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.