Nested SOQL Queries

Nested SOQL queries can be used with Composer to create nested detail regions in your Word document output.

• Building nested queries are not supported by Conga Support except as detailed in this article for Contracts for Salesforce sub-clause use cases. This functionality is supported in Microsoft Word templates only.
• TableGroup or TableGroupStart are great alternatives to using nested SOQL queries. TableGroup and TableGroupStart are not compatible with the nested query method shown in this documentation, but are supported by Conga Support.

Word Template Syntax

{{TableStart:QueryData}}

{{ACCOUNT_NAME}}

{{ACCOUNT_BILLINGADDRESS}}

{{TableEnd:QueryData}}

Benefits of Using Nested Queries

Nested queries, also known as subqueries, relationship queries, or left outer join relationship queries, are a top-down approach to creating a SOQL query.

They let you access child object data from a parent object while grouping all your output data so that child records are associated with their specific parent record.

As an example, consider a use case where we want to display an Account record along with all associated Opportunities, Contacts, and Cases. Using a nested query, we can group our output data like this:

• Account 1
  • Opportunities 1
  • Contacts 1
  • Cases 1
• Account 2
  • Opportunities 2
  • Contacts 2
  • Cases 2
• etc.

This is an advanced Salesforce feature, so you’ll need to make sure you are comfortable with all aspects of SOQL Queries as defined by Salesforce:

• Salesforce Developers SOQL Documentation
• SOQL Trailhead
• Nested SOQL
• Relationship Query Limits

Nested SOQL Query Syntax

Using our example above, let’s write a query to display all child Account information grouped with all open Opportunities, recent Cases, and active Contacts, followed by the next child Account with all open Opportunities, recent Cases, and active Contacts, etc.

SELECT Name, ID,
(SELECT Name, Amount FROM Opportunities WHERE Status = ‘Open’),
(SELECT Id, CaseNumber, Subject FROM Cases ORDER BY CreatedDate ASC LIMIT 10),
(SELECT Id, Name, Email FROM Contacts WHERE Type = ‘Active’),
FROM Account
WHERE ParentId = ‘{pv0}’
ORDER BY Name ASC

Example data from that query:

In the Composer View Data workbook, each object in the query is split into its own worksheet. One tab is for the parent object (Account), and the other is for each child object in the nested query (Opportunities, Cases, and Contacts).

The naming convention of the worksheets in the View Data workbook is [QueryAlias]_[API Name of the Child Object].

If the child object has a long API name, the worksheet in the View Data workbook will truncate the name because an Excel worksheet name can only contain so many characters. In this case, you'll see something like, "[QueryAlias]_ APXTConga4__Conga_Custom_Obj…" If there is an ellipsis (…) at the end of the worksheet name, it indicates that the dataset name has been truncated and will not work if referenced as such in the Word template. Instead, refer to the Conga Template Builder in order to retrieve the full dataset name.

Using Nested Query Data in a Word Template

Nested {{TableStart}} / {{TableEnd}} detail regions in the Word template display the results of a nested query. Some important factors to keep in mind:

• Everything between the outermost TableStart / TableEnd detail region repeats for every row of data in your outer query. Continuing our example, this means that each unique Account name displays first using {{TableStart:ChildAccts}} / {{TableEnd:ChildAccts}}.
• Between the outer-most TableStart and TableEnd merge fields, add TS/TE tags for each nested query dataset: ChildAccts_Opportunities, ChildAccts_Cases, ChildAccts_Contacts.
• Make sure to use Template Builder to get the proper syntax for each dataset.

The merged output file from the template above looks like this:

Tips & Tricks

• Add a Page Break just before the outermost TableEnd to display the results from each parent record on a separate page
• Use TableHide to prevent displaying empty tables in your output. For example, if one of the Accounts has Opportunities and Contacts but no Cases, the Cases table will not display when using TableHide. However, if the next Account does have Cases, the table appears for that iteration
• Use Workbench or Salesforce Inspector to find the child relationship names that are used in nested queries
  1. Log in to your environment.
  2. Navigate to the Info tab → Standard & Custom Objects → Choose your custom object → Child Relationships → Choose the child object you'd like to query → relationshipName.
  3. Use relationshipName in the FROM clause of the nested SOQL statement.