Skip to main content
Conga Support

Nested Detail Regions

In Composer Release 8, detail regions can be “nested” inside one another if the detail data is retrieved through a nested SOQL query.

 This functionality is only support in Microsoft Word templates.

{{TableStart:QueryData}}

{{ACCOUNT_NAME}}{{ACCOUNT_BILLINGADDRESS}}

Name

Title

{{TableStart:QueryData_SubQueryData}}{{First_Name}}
{{Last_Name}}

{{Title}}{{TableEnd: QueryData_SubQueryData}}

{{TableEnd:QueryData}}

Benefits of Using Nested Queries

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

A classic example of this would be the standard Account object with many child objects that are unrelated to one another: Opportunities, Contacts, Cases, and Attachments.  A Joined report would be necessary in order to create a Salesforce report incorporating all of these objects.

Conga Composer® does not support joined reports.

When using nested queries, you should be familiar with writing SOQL queries. For assistance, see:

The Conga Query Manager section of the Conga Composer Product Guide

Using Nested Queries in Word Templates

Perhaps you would like to create a Word® document showing the Account information and underneath that display all open Opportunities, recent Cases, active Contacts, and any attached photos (all related to that initial Account), followed by the next Account with all open Opportunities, recent Cases, active Contacts, and so on.  You could accomplish this using a nested query, which would look something like this:

SELECT Name, ID,

(SELECT Name, Amount FROM Opportunities),

(SELECT Id, CaseNumber FROM Cases),

(SELECT Id, LastName, Email FROM Contacts),

(SELECT Id FROM Attachments)

FROM Account

ORDER BY Name ASC

This query would return the following results:

In the Conga Composer View Data workbook, each aspect of the query is parsed into its own worksheet one tab for the outer query (the Accounts) and another tab for each nested query (Opportunities, Contacts, Cases, and Attachments).

The naming convention of the worksheets in the View Data workbook is "DatasetName_ 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. If this is the case you'll see something like, "QueryData_ 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

Use nested {{TableStart}} / {{TableEnd}} detail regions in the Word template to display the results of a nested query.

Some important factors to keep in mind:

  • Everything between the outermost TableStart / TableEnd detail region will repeat for every row of data in your outer query.  Continuing our previous example, each unique Account (dataset name QueryData) will display for the outmost detail region ({{TableStart:QueryData}} / {{TableEnd:QueryData}}).

  • Between the outermost TableStart and TableEnd merge fields, build a detail region for each sub-query dataset (e.g. QueryData_Opportunities, QueryData_Cases, etc.).

    • These are the nested detail regions and will repeat the rows of data from the corresponding sub-query, each of which is related to the outermost query (i.e. parent Account).
    • In other words, for the first Account, all Opportunities from the first Account will appear in the yellow table, Cases in the blue table, and Contacts in the green table.
  • The TableHide field is a nice optional feature which will prevent a table from merging (hide it) if the corresponding dataset returns no rows.  For example, if one of the Accounts has Opportunities and Contacts but no Cases, the Cases table will not display.  However, if the next Account does have Cases, the table will appear for that iteration.

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

You can add a Page Break just before the outermost TableEnd field (i.e. TableEnd:QueryData) in order to display the results from each Account on a separate page.

With regard to writing a nested query for custom objects, often, it can be challenging to find the correct child relationship name to use in the sub-queries.  We recommend using the Force.com Workbench in order to find these child relationship names.

Navigate to https://workbench.developerforce.com/login.php

Info tab > Standard & Custom Objects > Choose your custom object > Child Relationships > Choose the child object you'd like to query > relationshipName

The relationshipName is what you'll need to use in the FROM clause of the nested SOQL statement.