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 footer rows in the template file. The first to last row 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 merge fields for the child record details. The rows are repeated for the number of child records in the group.
Footer rows: Set total merge fields, 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 last row of the table.
Word/PowerPoint/PDF Consideration
When outputting Word/PowerPoint/PDF, all merge fields of the same child object should be set in a single table.
Incorrect 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.
Reference: Create OFC_Child Object Option Record
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. If blank, the default of "0" is applied.
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
{!Child Relationship 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 records. | {!OpportunityLineItems.Count(Id)} |
| Avg() | Returns the average 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 Records (Min)" cannot be set for group output.
- When outputting groups, Office features such as Excel formulas, Data Validation, 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