Common:Processing Related Records in MS Office

Finding Related-Record Variables

Related Object variables are found using the Template Variable Tool.

Here, in the Email Template for Claims, the related ClaimItems object is being chosen from the category list:

Within that group, the Item Name field is listed, along with other fields in the ClaimITems object. Once selected, the variable name appears in the Variable area, along with the loop it needs to be surrounded by for processing:

#foreach( $ClaimItems_record in $ClaimItems ) $ClaimItems_record.item_name #end

If a loop already exists, you would use only the variable name part:

$ClaimItems_record.item_name

Note: For a many-to-many relationship like one between Claims and Tags, the variable looks like this: $Claims_Tags_record.related_to_Tags.tag_name where: $Claims_Tags is the Junction Object that produces the many-to-many relationship related_to_Tags is the name of the Lookup field in the Junction Object that references a Tags record tag_name is the field to display The "dot" separator (.) joins each of the segments in the variable name

Using Related-Record Variables

For HTML, you insert related records into a table by putting loop-controls around code that specifies the format for a row:

#foreach($i in $Order_Items)

     <tr>...row contents here...</tr>

#end

The problem in MS Office tables is that there is no place "outside the row" to put those loop controls.

The solution is to use two special constructs that go into the first cell of the row:

• @before-row#foreach($i in $Order_Items)
• @after-row#end

Considerations

• The constructs only need to be specified once in the row, in the first cell.
• Between them, include the field that will be displayed in the cell
• Like the fields, the loop construct are inserted into the cell as fields

• Insert > Quick Parts > Field, Category: Mail Merge, Field type: MergeField
• Or copy an existing field, right click, and select the Edit Field... option

• For other cells in the row, specify the field to include in that cell, without the extra constructs

Example

Cell 1:

«@before-row#foreach($i in $Order_Items)»
«$i.item»
«@after-row#end»

Cell 2:

«$i.quantity»

and so on...

The resulting table will look something like this (with line breaks included in the first cell to make it more readable):

Sample Template: OrderInvoiceWordTemplate.docx

Learn more: Solution: use @before-row @after-row