Group Output of Child Records (Excel)

When outputting child records in Excel, output child records by grouping the field values.

*This feature is available in Office File Creator Pro.

 

Overview

Supported File Formats

  Excel

 

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

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.

 

 

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.

 

(Excel) Group Field API Name: Enter the field API name to be grouped. e.g. ProductCode

(Excel) Number of Group Header Rows: Enter the number of header rows for the group. Enter at least 1 for the number of rows.

(Excel) 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.

 

 

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, excel functions such as Excel formulas, input rules, and conditional formatting cannot be used. If they are used, they will be misaligned or cause an error in the output.
  • Group output is available only for Excel output. Block output is not available for Word, PowerPoint, or PDF output.

 

 

Reference

Block Output of Child Records (Excel)

Functions for Merge Fields

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