SOQL Query Relationships

Use SOQL A specially designed SQL query language for querying the database in Salesforce orgs. Query Relationships to add data to your DocGen Packages A customizable package that consists of your Salesforce data, documents, and delivery configurations for documents you want to generate. that are complex to create with the point-and-click Salesforce object relationship editor. SOQL provides support to relationship queries against standard and custom objects. You can use relationship queries to return objects of one type based on the criteria that applies to other type of objects.

For example, say you want to return all accounts created by one user and all the contacts associated with those accounts. You'll need to create a parent-to-child relationship creating the connection between the two objects, which you can do with a SOQL Query Relationship.

To watch video tutorials on how to use this Nintex capability, take the CERT: Nintex Document Generation Expert - Nintex DocGen for Salesforce in Nintex University.

Create a SOQL Relationship

  1. Navigate to the App Launcher by selecting the app launcher in the upper left corner, select Nintex DocGen.
  2. Select the DocGen Packages tab.
  1. Open the DocGen Package you want to create a SOQL relationship for.

  2. From the Data Tab, access the Relationships window.

  3. In the Add Relationships section of the window, click SOQL Query.
  4. In the SOQL field, type in the SOQL query.
    • Remove any line breaks in the query. Line breaks work in the Developer Console, but cause errors in Nintex DocGen.
    • Only use field tags in the WHERE clause of a SOQL query.
    • Use an inner query inside of a select to get table and row replication.
    • TYPEOF keyword is not supported.

    • Use dot notation to get data through a lookup.

  5. From the Relationships section, you can edit the Repeat By field to:
Section Copies a specific section in your document that has unique data.
Table Copies tables. Each table has unique data.
Row Copies rows that have unique data in each row.

The default value is Row.

You cannot use Group or Hierarchy as a Repeat By option for SOQL Query Relationships.

  1. Click Save.

Use the SOQL Query relationship

After you create the SOQL Query relationship, you can create a template with field tags from the relationship. Load this template into your DocGen Package and then use a record from the starting object of the DocGen Package to generate a document.

To test the SOQL Query relationship:

  1. Open Nintex DocGen and click DocGen Package.

  2. In the Field Tagger for the DocGen Package with the SOQL Query relationship you implemented, select the object you queried as the Main Object.

Working with SOQL in Nintex DocGen for Salesforce

This section offers tips and limitations.

Use of field tags

Only use field tags in the WHERE clause of a SOQL query.

Copy 
SELECT Name, FROM Account WHERE ParentAccountId = '<<Account_Id>>'

Tip: This includes WHERE clauses for inner queries as well. For an example, see the sample table replication query below.

When using a tag for a field that does not require quotes (e.g. booleans and dates), omit the quotes and use the appropriately formatted tag from the Field Tagger (dates, datetimes):

Copy 
SELECT Name FROM Invoice__c WHERE Invoice_Date__c = <<Contract_EndDate__y>> AND Fulfilled__c = <<IncludeFulfilled>>

Copy 
SELECT Name FROM Invoice__c WHERE Invoice_Fulfilled__c < <<Contract_LastModifiedDate__g>>Z

Filtering with dates

From above, for date comparisons, use the `YYYY-MM-DD` date format (suffix `__y`); for datetime comparisons, use the `YYYY-MM-DDTHH:mm:ss` format (suffix __g) with a `Z` at the end. Do not surround these tags with quotes.

Table replication

Use an inner query inside of a select to get table and row replication.

The sample below allows for a table for each Account and a row in each table for each Opportunity associated with the Account:

Copy 
SELECT Name, (SELECT Name FROM Opportunities WHERE Region__c = '<<User_Region>>') FROM Account WHERE ParentAccountId = '<<Account_Id>>'

Dot notation

Use dot notation to get data through a lookup.

Copy 
SELECT Account.Name, My_Object__r.My_Field__c FROM Contact WHERE ...

Limitations

Remove any line breaks in the SOQL query before you save the relationship. Line breaks work in the Developer Console, but cause errors in Nintex DocGen.

Keywords not supported:

TYPEOF

Test and troubleshoot SOQL queries

Salesforce provides an interface in the Developer Console that allows you to run SOQL queries against the data in your organization. Create a query, test it in the Developer Console, then add the query to the DocGen Package.

To create the SOQL qeury:

  1. Log into your Salesforce instance.

  2. From your Salesforce instance, open the Developer Console.

  3. For more information about the Developer Console in Salesforce, see Developer Console.

  4. From the Developer Console, click Query Editor.

  5. Type in your query and click Execute.