Output Case records relate to the Account of the Opportunity (Output child records of lookup fields) – SOQL in Advanced Relationship Settings –

This article shows the procedure for outputting case records related to the Account of the Opportunity in Office File Creator. By setting a subquery in SOQL with advanced relationship settings, it is possible to output child records related to the master object's lookup fields.

*This feature is available in Office File Creator Pro.

*This feature requires knowledge of SOQL.

Table of contents

Output Sample
Overview of Settings
Merge Field Settings
Create OFC_Template Record
Create OFC_Child Object Option Record
Output File
(Reference) Output child records of the child object's look-up field
Note
Reference

Output Sample

As an example, when the Opportunity is the master object, output the case related to the Opportunity's parent Account.

Output the child records of the Product related to the Opportunity and the Case related to the Account.

Overview of Settings

Set the SOQL with subqueries to the Advanced relation Settings in the OFC_Child Object Option.

Merge Field Settings

Set any child relationship name for the merge field of the Case of the Account. The child relationship name must begin with a Roman numeral and only alphanumeric characters and underscores are allowed. In this example, set "AccountCases" as the child relationship name for the case.

Create OFC_Template Record

Create the OFC_Tempalate record.

Name: Opportunity Invoice with Account Cases

Template API Name: OpportunityInvoice

Object API Name: Opportunity

Create OFC_Child Object Option Record

Create the OFC_Child Object Option record.

Information

Name: Account Cases

Child Relationship Name: AccountCases

Sort Order

Set the CaseNumber in descending order.

SortField1 API Name: CaseNumber

SortField1 Order: Descending (NULLS FIRST)

(Pro) Advanced Relationship Settings

Set a subquery in SOQL.

Object API Name: Case

(Advanced) SOQL (Where): Enter a SOQL subquery. The subquery should specify the condition after Where and must include "=:recordId." The "recordId” must match up to lowercase.

e.g. AccountId In (Select AccountId From Opportunity Where Id =: recordId)

*It is not allowed to enter both "Master Object Id Field API Name" and "(Advanced) SOQL (Where)".

Check in Developer console

Check the developer console for the SOQL to get the Case record of Account from the record Id of Opportunity.

SOQL

Select CaseNumber,Status From Case Where AccountId In (Select AccountId From Opportunity Where Id='006J100000ABCDE')

Check that the record screen shows the same case records as the result in the developer console.

After confirming that the record can be retrieved correctly, cut out the back from Where and change "Id='0065g00000ABCDE'" to "Id =: recordId".

(Advanced) SOQL (Where)

AccountId In (Select AccountId From Opportunity Where Id =: recordId)

Output File

Output the child records of the Product related to the Opportunity and the Case related to the Account.

(Reference) Output child records of the child object's look-up field

The master object is Opportunity, the child object is OpportunityContactRole, the lookup field of OpportunityContactRole is Contact, the child object of Contact is Case, and output Case.