This article introduces the procedure for outputting records of grandchild objects (3 levels) to documents in Office File Creator.
*This feature is available in Office File Creator Pro.
Output Image
Outputs the products (3 levels) of the Opportunity related to the Account.
The master object is the Account. Outputs the Products of grandchild records grouped by the Opportunity.
Merge Fields Settings
Header Row
Set the merge field for Products. This row is the header row to group by the Opportunity. Use a cross-object formula to set the Order Number of the Opportunity as the merge field.
Order Number: {!OpportunityLineItems.Opportunity.OrderNumber__c}
Opportunity Name: {!OpportunityLineItems.Opportunity}
Detail Row
Set the merge field for Products.
Footer Row
Set the total amount merge field using the aggregate function.
{!OpportunityLineItems.Sum(TotalPrice)}
Set the child relationship name of the grand 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.
As an example, use "ProductsTotal" as the child relationship name and the Sum() function to output the total of "TotalPrice".
e.g. {!ProductsTotal.Sum(TotalPrice)}
OFC_Child Object Option Record Settings
Create an OFC_Child object option record. In this case, create two records, one for the group output of detail rows and one for the grand total output.
Create OFC_Child Object Option record for detail rows
Enter basic information.
Name: Enter "Products".
Child Relationship Name: Enter the child relationship name "OpportunityLineItems" for the Product.
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 Product object API name "OpportunityLineItem". *Not a child relationship name.
Master Object Id Field API Name: Enter the cross-object formula "Opportunity.AccountId" that references the Account Id of the master object.
Next, enter the field values in the "(Pro) Group Data" section.
(Excel) Group Field API Name: Enter the parent reference field "OpportunityId".
(Excel) Number of Group Header Rows: Enter 1.
(Excel) Number of Group Footer Rows: Enter 1.
Next, enter the field values in the "(Pro) Sort Order" section.
Ascending order by Order Number and Product Code. Cross-object formulas can be used for sorting fields from Ver.2.
Create OFC_Child Object Option record for grand total
Enter basic information.
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.
Object API Name: Enter the Product object API name "OpportunityLineItem". *Not a child relationship name.
Master Object Id Field API Name: Enter the cross-object formula "Opportunity.AccountId" that references the Account Id of the master object.
Next, enter the field values in the "(Pro) Group Data" section. The group field is the Account Id in order to retrieve the total value of the Opportunity of the Account.
(Excel) Group Field API Name: Enter the cross-object formula "Opportunity.AccountId" that references the Account Id of the master object.
(Excel) Number of Group Header Rows: Enter 0.
(Excel) Number of Group Footer Rows: Enter 1.
Output File
The products are grouped by the Opportunity, and the total for each Opportunity is output. The total amount of products for the Account is output to the General Total row.
Reference
Output 3 or More Levels of Child Records
Group Output of Child Records (Excel)
