Create a Query

1 Access Query Creation page
2 Query Fields
- 2.1 Name
- 2.2 SOQL Query
- 2.3 Attribute Mapping
- 2.4 Single Record
3 Full Example
- 3.1 IdealDoc Query Record
- 3.2 SOQL Queries
- 3.3 Attribute Mappings
- 3.4 Ideal Doc Template (DOCX File)
4 Query functions
- 4.1 Aggregate Queries
- 4.2 Sub Queries

The IdealDoc Query, which is stored in the IdealDocQuery__c custom object, defines the SOQL queries and data mappings required to provide the data needed to generate a document using a corresponding template.

Access Query Creation page

Navigate to the IdealDoc app.
Find the Query Tab.
Click the 'New' button
Fill in the Fields according to the next chapter.

Query Fields

An IdealDocQuery__c record consists of the following elements.

Name

The name of the query used for displaying in list views or lookup searches.

Example:
“Customer Invoice Query”

SOQL Query

One or multiple SOQL queries separated by a semicolon. In the queries the provided query parameters can be used for filtering the result. For each query there needs to be a corresponding attribute mapping entry (see below). If the “Single Record” checkbox is checked, the first query should contain exactly one record. This record will be used as the top level container for all the other queries.

Example:

SELECT Id, Name, Description, Amount__c FROM InvoiceLineItem__c WHERE Invoice__c = :recordId

Attribute Mapping

JSON Array data structure containing a mapping object (JSON) for each SOQL query.

Example 1:

[
  {
    "name": "<Mapping Name>",
    "parent": "<Name of the parent mapping>",
    "parentKey": "<Key Attribute from the parent mapping>",
    "childKey": "<Key Attribute from the child mapping>",
    "mapping": {
      "<Mapped Attribute Name>": "<original attribute name>",
      ...
    }
  },
  { ... },
  ...
]

There need to be exactly as many attribute mappings in the attribute mapping array as there are queries defined in the IdealDocQuery record.

Example 2:

[
{
  "name": "query0",
  "mapping" : {
    "Name": "Name",
    "AccountName": "Account__r.Name",
    "InvoiceDate": "InvoiceDate__c"
  }
},
{
  "name": "lineItems",
  "mapping" : {
    "Name": "Name",
    "AccountName": "Account__r.Name",
    "InvoiceDate": "InvoiceDate__c"
  }
}
]

Single Record

Checkbox to indicate that the first query in the SOQL Query field will exactly return one record and that this record should be treated as top level container.

e.g. the first query in the above example will return without SingleRecord checked:

But with SingleRecord checked the result will look like this:

This format is much more convenient to be used with the merge fields in the Template.

Full Example

IdealDoc Query Record

Single Record: Yes

SOQL Queries

Attribute Mappings

Ideal Doc Template (DOCX File)

TBD

Query functions

Aggregate Queries

In aggregate salesforce queries the first part of a related field gets skipped, e.g. for the query:

The result columns are “Name” (not “Workpackage__r.Name”) and “HoursBillable”. To be investigated.

Sub Queries

Usually, related child queries should be defined by a second query filtering for the parent recordId and using the parent-child attribute mappings (seen the Full Example above).

However, if the filtering of the parent records is more advanced than the current recordId or some simple query parameters it might be needed to use the standard SOQL subqueries. This can be done like this:

The “Contacts” in the subquery reference the name of the related list in Salesforce (see here).

The attribute mapping

IdealDoc Q