View source for Common:Processing Related Records in HTML

<noinclude>__NOINDEX__</noinclude>
===About Related Record Variables===
In a {{type|}}, 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 and Using Related Record Variables===

====Finding Related-Record Variables====
Related Object variables are found using the [[Template Variable Tool]]. 

Here, the related OrderItems object is being chosen from the category list:
:[[File:TemplateVariableToolRelatedObjects.png]]

Within that group, the Item Name field is listed, along with other fields in the OrderITems object. Once selected, the variable name appears in the Variable area, along with the loop it needs to be surrounded by for processing: 
:<tt>#foreach( $OrderItems_record in $OrderItems ) $OrderItems_record.item_name #end </tt>

If a loop already exists, you would use only the variable name part:
:<tt>$OrderItems_record.item_name</tt>

{{Note|<br>For a ''many-to-many'' relationship like the one between Orders and Tags, the variable looks like this:
:<tt>$Orders_Tags_record.related_to_Tags.tag_name</tt>
where:
:* <tt>$Orders_Tags</tt> is the [[Junction Object]] that creates the many-to-many relationship
:* <tt>related_to_Tags</tt> is the name of the Lookup field in the Junction Object that references a Tags record
:* <tt>tag_name</tt> is the field to display
:* The "dot" separator (<tt>.</tt>) joins each of the segments in the variable name}}

====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 <tt>#foreach</tt> 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. 

For example (with formatting attributes removed for simplicity):
:<syntaxhighlight lang="xml" enclose="div">
<table>
<tbody>
<tr>
    <th>Quantity</th>
    <th>Product </th>               
    <th>Unit Price</th>
    <th>Amount</th>
</tr>
</tbody>

#foreach( $OrderItems_record in $OrderItems )
<tr>        
    <td>$OrderItems_record.item_quantity</td>
    <td>$OrderItems_record.related_to_ProductInventory.product_name</td>
    <td>$OrderItems_record.item_price</td>
    <td>$OrderItems_record.total</td>
</tr>
#end

...
</table>
</syntaxhighlight>

where:
:* The <tt><nowiki><tbody></nowiki></tt> element is required around the header row.

:* The Velocity <tt>#foreach</tt> directive does the looping.
:: ''Learn more:'' http://velocity.apache.org/engine/releases/velocity-1.5/user-guide.html
:* The looping construct defines <tt>$OrderItems_record</tt> as a loop variable.
:* The loop variable iterates over the <tt>$OrderItems</tt> array.
:* The <tt>$OrderItems</tt> 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 <tt>$Orders Fields</tt> category as <tt>Total $Amount</tt> and <tt>Shipping for $Amount</tt>, respectively. (The final total is shown as <tt>Net Total $Amount</tt>.)

===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 <tt>#foreach</tt> processing to process OrderItems records for an Order
:* Uses Velocity <tt>#if</tt> 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">

<div style="width:100%">

<img  width="100" height="100"  src="http://justanothermobilemonday.com/Wordpress/wp-content/uploads/2009/10/android-robot-logo2.jpg" alt="Company logo" />&nbsp;&nbsp;&nbsp; <---- Company Logo Here<br/>
<font size="5"><b>$company.name</b></font><b><br />
<br/>
$company.street&nbsp;&nbsp;<br>
$company.city, $company.state, <br>
$company.country - $company.zip
 
&nbsp;<br>
&nbsp;(Phone): $user.phone
<hr width="100%" size="1" />
<br />

<h2 align="center">Order<b> Invoice</b></h2>
<p></p>
<p align="center"><b>Invoice Date:</b> $Orders.date_created </p>

<br/><br/>
<b><u>Bill To: </u></b><br />
<font size="2">$Orders.account.name - $Orders.account.number</font></b>
<br />$Orders.account.street<br />
$Orders.account.city,&nbsp;$Orders.account.state, <br>
$Orders.account.country - $Orders.account.zip<br /> 

(Phone): $Orders.account.phone <br />
<br />
<br />
<b>Invoice #: $Orders.order_number <br />
</b>&nbsp;
</p>

<table border="1" cellpadding="5" cellspacing="0" width="600">

<!-- HEADER -->
<tbody>
<tr>
    <th>Quantity</th>
    <th>Product </th>               
    <th>Unit Price</th>
    <th>Amount</th>
</tr>
</tbody>

#foreach( $OrderItems_record in $OrderItems )
<tr>
<td width="350">$OrderItems_record.item_quantity</td>
<td width="100" align="CENTER">$OrderItems_record.related_to_ProductInventory.product_name</td>
<td width="100" align="CENTER"> $OrderItems_record.item_price</td>
<td width="100" align="CENTER"> $OrderItems_record.total    </td>
</tr>
#end

<tr>
<td colspan="2">&nbsp;</td>
<td style="border: solid 1px #000000;" align="Right">Sub Total</td>
<td style="border: solid 1px #000000;" align="center">$Order.grid_comptn_f5631e34b39f4ba39a98559c7215a3b4</td>
</tr>

#if( $Orders.grid_surcharge_031f67dd4b3946949df2da276e5c82a6 > 0 )
<tr>
<td colspan="2">&nbsp;</td>
<td style="border: solid 1px #000000;" align="Right">Surcharge</td>
<td style="border: solid 1px #000000;" align="center">$Orders.grid_surcharge_f5631e34b39f4ba39a98559c7215a3b4</td>
</tr>
#end 

#if( $Orders.grid_discount_031f67dd4b3946949df2da276e5c82a6 > 0 )
<tr>
<td colspan="2">&nbsp;</td>
<td style="border: solid 1px #000000;" align="Right">Discount</td>
<td style="border: solid 1px #000000;" align="center">$Orders.grid_discount_f5631e34b39f4ba39a98559c7215a3b4</td>
</tr>
#end 

<tr>
<td colspan="2">&nbsp;</td>
<td style="border: solid 1px #000000;" align="Right">Tax</td>
<td style="border: solid 1px #000000;" align="center">$Orders.grid_tax_f5631e34b39f4ba39a98559c7215a3b4</td>
</tr>

<tr>
<td colspan="2">&nbsp;</td>
<td style="border: solid 1px #000000;" align="Right">Shipping</td>
<td style="border: solid 1px #000000;" align="center">$Orders.grid_shipping_f5631e34b39f4ba39a98559c7215a3b4</td>
</tr>

<tr>
<td colspan="2">&nbsp;</td>
<td style="border: solid 1px #000000;" align="Right"><b>TOTAL AMOUNT DUE</b></td>
<td style="border: solid 1px #000000;" align="center"><b>$Orders.grid_net_total_f5631e34b39f4ba39a98559c7215a3b4</b></td>
</tr>     
</table>

<br /><br />
Payment Due upon reciept<br />
Thank you for your business!
</div>
       
<p></p>
</body>
</html>
</syntaxhighlight>

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

:[[File:OrderInvoiceSampleSegment.png]]

(The [{{DOCHOST}}/files/OrderInvoiceTemplate.html Order Invoice template] and the [{{DOCHOST}}/files/OrderInvoiceSample.html Order Invoice sample] file are both available in the downloads area.)