Apply query to template (Legacy)

To print the Query value in the template, the Query needs to be under a loop using the forEach tag or assigned to the Repeat in Dynamic Attributes of an element. This article includes general instruction to apply query and an example to create a table with queried data.

1. Assign Query to an element

2. Map query data to template

3. Query vs Related List

4. Example: List opportunity products grouped by product family

1. Assign Query to an element

After a query has been created, its name will appear in the Queries list under Data Source in the Data tab. To assign a query to an element:

• Click on the element which you would want to assign the query to. In the example below, the query is to be applied to the table element.

• Go to Data tab > Queries > Click on the query whose results you would want to use

• Choose the element to assign the query to.

After a query has been assigned to an element, Dynamo will automatically add the query name to the Repeat attribute and an iteration variable (often i, j or k) to the Repeat: Variable attribute of the selected element (to see these values, go to Tag -> Attributes on the right pane).

2. Map query data to template

In the template, select the placeholder text, click Insert Span in the editing bar to create a Span element

On the right pane, select Tag -> Attributes

In the Content attribute, type the field you call in Query with the format Repeat:Variable.FieldAPIname

3. Query vs Related List

Both queries and related lists are methods to retrieve Salesforce data that is needed in a template. However there are distinct differences between them and they are meant to be used to different situations:

• Related list: as the name suggests, is a list of items that is related to a parent object. For example each opportunity has a list of opportunity products (aka opportunity line items). Related list should be used if there is a need to map multiple fields from the related object to the document and these fields will be displayed repeatedly for each line item that is related to the record. The command that is used to fetch data from a related list is relatedList.

• Query: query should be used when there’s the need to apply more complex conditions or the result of the query will be used as an input in the template logic. The command used to create a query is query.

4. Example: List opportunity products grouped by product family

The example solution illustrates:

• How to use query in combination with related list to create an elaborated product table

• How to use element logic

• When to use query vs related list

• How to work with aggregation in query

Expected outcome:

The expected outcome (image below) is a list of tables, in which each table represents a family of products related to the current opportunity record.

The general how-to guide:

To achieve this table, a combination of related list and query can be used:

• The related list Opportunity Product can be used to map all the fields needed to display for each line item. As we would want each row to display the fields of one line item, the related list should be assigned to the row element of the table.

• Related list, while being convenient for mapping line item fields, is not capable of dealing with aggregates and grouping. In order to display groups of opportunity products by product families, a query needs to be created to fetch and organize necessary data. This query should then be assigned to the table element, so that a table is created for each product family.

Step-by-step instructions:

1. Create a table of 3 rows and 2 columns. First row is the table header. The table structure should look like the below screenshot in the template. Text with brackets '< >' around is placeholder for dynamic fields.

2. Create a new query with the following details:

Var: productFamilies

Select: 

SELECT Product2.Family Family, sum(TotalPrice) Total FROM OpportunityLineItem where Opportunity.Id = '${id}' group by Product2.Family

In the query above, Family is an alias notation for 'Product2.Family' and sum(TotalPrice) is an aggregate function that creates a sum of the TotalPrice field.

3. Assign the query to the table element and map the query data for product family name in the first row and the product family group sum in the third row of the table: 

• The mapped product family field should be in the form Repeat:Variable.FieldAPIname (in this case: i.Family).

• Similarly, the mapped product family group sum should be i.Total
  

4. Assign the Opportunity Product

related list to the table row:

• Click on a cell in the row that you would want to add the related list to. 

• On the right pane, go to Data > Opportunity > Related Lists, search for 'Opportunity Product' in the Filter box.

• Click on 'Opportunity Product' and assign the list to the Row element.
  

5. To get opportunity line items displayed under each product family, we need to set the '

Where' condition of the related list to connect the line items with the productFamilies query result:

• Click on the row in which the related list is mapped

• On the Data tab of the right pane, click to the 3-line icon next to the 'Opportunity Product' list name and choose Where to open the Where editor.

• Enter the condition: Product2.Family = '${i.Family}'