When outputting child records, output child records by grouping the field values.
*This feature is available in Office File Creator Pro.
Overview
Examples of Use
- Output grouped by product type.
- Output the total amount for each group field.
Required settings
- Merge Fields Settings
- OFC_Child Object Option Record Settings
Merge Fields Settings
Group Layout Settings
Set header rows, detail rows, and footers rows in the template file. The first to last line of the child object merge field becomes one block of the child record.
Header rows: Set grouping merge field and table headers.
Detail rows: Set the item to be inserted in the child record's details. The number of child records belonging to the group is output.
Footer rows: Set Amount merge field, remarks, etc.
To insert blank lines between blocks of child records, use the blank() function for the merge field. In this example, one line of white space is set between groups, so the footer has two lines.
Excel Consideration
When outputting to Excel, if the cells in the last row are merged, cell merging is not taken into account in determining the last row. Adjust the last row of the block by setting the Blank() function outside the display range of the last row.
Word/PowerPoint/PDF Consideration
When outputting Word/PowerPoint/PDF, all merge fields of the same child object should be set in a single table.
NG example: The following case will be an error because the child object merge fields "{!OpportunityLineItems.Blank()}"
is outside the table.
OFC_Child Object Option Record Settings
Open the OFC_Child Object Option record detail page and enter the field values in the "(Pro) Group Data" section.
Group Field API Name: Enter the field API name to be grouped. e.g. ProductCode
Number of Group Header Rows: Enter the number of header rows for the group. Enter at least 1 for the number of rows.
Number of Group Footer Rows: Enter the number of footer rows for the group. If blank, the default of "0" is applied.
Aggregate Functions
Aggregate functions can be used in grouped merge fields.
Merge Fields Format
{!Childrelationship Name.Aggregate Function(Field API Name)}
| Aggregate Function | Description | Example of Merge Field |
| Sum() | Returns the total sum of a numeric field. | {!OpportunityLineItems.Sum(TotalPrice)} |
| Count() | Returns the number of group rows. | {!OpportunityLineItems.Count(Id)} |
| Avg() | Returns the minimum value of a field. | {!OpportunityLineItems.Avg(TotalPrice)} |
| Min() | Returns the minimum value of a field. | {!OpportunityLineItems.Min(TotalPrice)} |
| Max() | Returns the maximum value of a field. | {!OpportunityLineItems.Max(TotalPrice)} |
Memo
If the group field is a formula field, the aggregate function cannot be used.
Output Sample
Samples grouped by product type.
Excel
Word
Considerations
- If the group field is a formula field, the aggregate function cannot be used.
- The "Number of Output Rows (min)" cannot be set for group output.
- When outputting groups, Office features such as Excel formulas, data validations, and conditional formatting cannot be used. If they are used, they will be misaligned or cause an error in the output.
Reference
Group child records by record values and output to documents
Output the total amount of child records using the aggregate function in the merge field