Common:Processing Related Records in MS Office

From AgileApps Support Wiki
Revision as of 23:01, 23 June 2015 by imported>Aeric (→‎Using Related-Record Variables)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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:

TemplateVariableToolRelatedObjects.png

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

Notepad.png

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):
WordTemplateRelatedRecordVaraibles.png
Sample Template: OrderInvoiceWordTemplate.docx
Learn more: Solution: use @before-row @after-row