Processing Related Records in a Template

About Related-Record Processing

Data in objects that have a Lookup to the current record need to be processed in a loop, because multiple records in that object could be pointing to the current record. The Template Variable Tool displays the entire loop syntax for such variables.

In a Claims record, for example, a variable for related Claims Items records might appear like this:

#foreach( $ClaimsItems_record in $ClaimsItems ) $ClaimsItems_record.item_amount #end

Paste the entire line of code into the template or, if a loop is already in place, paste only the part you need:

$ClaimsItems_record.item_amount

Processing Related Records in JSP Page Templates

JSP pages used standard Java programming techniques to define loops. The Java Record Handling APIs are then used to access data.

Processing Related Records in HTML

HTML loops are used in Email Templates, Quick Text, and HTML Document Templates.

About Related Record Variables

In a Custom Object, fields from related records can be added to a Document Template. (That is, records from an Object that has a Lookup to the current object.)

For example, OrderItems has a lookup to Orders, so in a Document Template for Orders, related OrderItems records can be accessed. (In an object Form, related records are typically displayed in a Subform, although they need not be.)

When formatting a record in the current object for printing, those related records and the fields they contain can be processed in a loop.

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

Processing Related Records in a Loop

Typically, you'll create a table for the related records, define its headings, and then create a row for a single record, putting the related-record variables into the cells of the row. You then enclose that row in a loop, using the #foreach instruction defined by the Velocity templating engine.

Related records are returned in an array, so the loop creates a new table row for each record in the array.

Here is an example that processes OrderItems for an Order (with formatting attributes removed for simplicity):

<syntaxhighlight lang="xml" enclose="div">

<tbody> </tbody>

1. foreach( $OrderItems_record in $OrderItems )

1. end

...

Quantity	Product	Unit Price	Amount
$OrderItems_record.item_quantity	$OrderItems_record.related_to_ProductInventory.product_name	$OrderItems_record.item_price	$OrderItems_record.total

</syntaxhighlight>

where:

• The <tbody> element is required around the header row.

• The Velocity #foreach directive does the looping.

Learn more: http://velocity.apache.org/engine/releases/velocity-1.7/user-guide.html

• The looping construct defines $OrderItems_record as a loop variable.
• The loop variable iterates over the $OrderItems array.
• The $OrderItems array is automatically available in the template, because OrderItems is a Related Object. (All related objects are available. Each is an array.)

Special Considerations for Subform Variables

Totals and additional computation fields defined for a Subform are also available in the Template Variable Tool. To find them, you access the main object (not the object displayed in the Subform), because those fields are added to the object that contains the Subform.

For example, the Subform for OrderItems totals the $Amount column, and then does additional computations on it to add a surcharge. Those values are shown in the $Orders Fields category as Total $Amount and Shipping for $Amount, respectively. (The final total is shown as Net Total $Amount.)

Sample Template for an Order Invoice

This sample:

• Creates an invoice for an order, showing all items in it
• Gets the name and price of order items from the ProductInventory object
• Uses Velocity #foreach processing to process OrderItems records for an Order
• Uses Velocity #if conditionals to display surcharge and discount rows only if those values are present

<syntaxhighlight lang="xml" enclose="div">

<html><head> <title></title> <style>

div {  
     padding-top: 5px;  
     padding-bottom: 5px;  
     padding-right: 5px;  
     padding-left: 30px;  
     border: 3px;  

margin-top: 5%; margin-right: 40%; margin-bottom: 5%; margin-left: 5%;

   }

</style>

</head><body bgcolor="#FFFFFF" text="#000000" link="#0000FF" vlink="#800080" alink="#FF0000">

<img width="100" height="100" src="http://justanothermobilemonday.com/Wordpress/wp-content/uploads/2009/10/android-robot-logo2.jpg" alt="Company logo" />    <---- Company Logo Here
$company.name

$company.street  
$company.city, $company.state,
$company.country - $company.zip

 
 (Phone): $user.phone

---

Order Invoice

Invoice Date: $Orders.date_created

Bill To:
$Orders.account.name - $Orders.account.number
$Orders.account.street
$Orders.account.city, $Orders.account.state,
$Orders.account.country - $Orders.account.zip

(Phone): $Orders.account.phone


Invoice #: $Orders.order_number
 

<tbody> </tbody>

1. foreach( $OrderItems_record in $OrderItems )

1. end

1. if( $Orders.grid_surcharge_031f67dd4b3946949df2da276e5c82a6 > 0 )

1. end

1. if( $Orders.grid_discount_031f67dd4b3946949df2da276e5c82a6 > 0 )

1. end

Quantity	Product	Unit Price	Amount
$OrderItems_record.item_quantity	$OrderItems_record.related_to_ProductInventory.product_name	$OrderItems_record.item_price	$OrderItems_record.total
		Sub Total	$Order.grid_comptn_f5631e34b39f4ba39a98559c7215a3b4
		Surcharge	$Orders.grid_surcharge_f5631e34b39f4ba39a98559c7215a3b4
		Discount	$Orders.grid_discount_f5631e34b39f4ba39a98559c7215a3b4
		Tax	$Orders.grid_tax_f5631e34b39f4ba39a98559c7215a3b4
		Shipping	$Orders.grid_shipping_f5631e34b39f4ba39a98559c7215a3b4
		TOTAL AMOUNT DUE	$Orders.grid_net_total_f5631e34b39f4ba39a98559c7215a3b4

Payment Due upon reciept
Thank you for your business!

</body> </html> </syntaxhighlight>

The Subform part of the resulting invoice then looks something like this:

(The Order Invoice Template file is available in the downloads area.)

Processing Related Records in Word and PowerPoint Templates

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