This article introduces the procedure for outputting the total amount of a child record using the aggregate function in the merge field in Office File Creator.
*This feature is available in Office File Creator Pro.
Output Image
Use the aggregate function on the total row to output the total amount of the child records.
Output the aggregate value of child records. Aggregate functions can only be used for child objects for which grouping has been set in the OFC_Child Object Option record. If you set up the following child record detail and total rows without grouping, the OpportunityLineItems child object merge fields are treated as a block and all are output as detail lines since they are on lines 5 and 6.
Let's set the total rows to output only one line.
Merge Fields Settings
Set the child relationship name of the total to any name other than the child relationship name of the detail row, so that it is not recognized as a single block identical to the detail row. The child relationship name is used only for tying to the OFC_Child Object Option record to be set up later. Like the Custom Item API Name, it must begin with a Roman numeral, and only alphanumeric characters and underscores are allowed.
This time, use "ProductsTotal" as the child relationship name and the Sum() function to output the total of "TotalPrice".
e.g. {!ProductTotals.Sum(TotalPrice)}
OFC_Child Object Option Record Settings
Create an OFC_Child object option record.
Name: Enter "Products Total".
Child Relationship Name: Enter the child relationship name "ProductsTotal" set for the merge field of the grand total row.
Next, enter the field values in the "(Pro) Advanced Relationship Settings" section. Normally, child records are retrieved from the child relationship name, but when advanced relationships are set up, child records are retrieved from the entered "Object API Name" and "Master Object Id Field API Name ".
Object API Name: Enter the child object API name "OpportunityLineItem". *Not a child relationship name.
Master Object Id Field API Name: Enter the parent reference field "OpportunityId".
Next, enter the field values in the "(Pro) Group Data" section. In this case, the purpose is not to group child records and output them, but to obtain the total amount of the entire child record, so the group field should be the parent's reference field.
(Excel) Group Field API Name: Enter the parent reference field "OpportunityId".
(Excel) Number of Group Header Rows: For total rows of the entire child record, enter 0 for the number of header rows.
(Excel) Number of Group Footer Rows: Enter the total number of rows. Enter 1 here.
Output File
After output, the child relationship name OpportunityLineItem will output four rows, and the child relationship name ProductTotal will output one row for the footer total row only.
