Documill Dynamo Query Library (Legacy)

Below are sample queries you can use in the templates. Due to the variability of the field names and data model, it's best to use this library as reference source, while you select the fields and objects by using the Query Editor.

This library includes queries to:

Retrieve PDF files attached to the main record
Retrieve PDF attached to the related object of the main record
Retrieve PDF files attached to the related list's line item
Retrieve images attached to the main record.
Retrieve images attached to the related object of the main record.
Retrieve images attached to the line item.
Query product summary for an Account
Query product summary for an Account, group by Family
Query with Date Literals
Query with Date Functions

1. Retrieve PDF files attached to the main record

The queries collects all PDF attachments of the main record. The collected files will be added to the list for end-users selection or merged to the final PDF. These queries are included by default in the template's logic.

Query PDF saved as Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE ParentId = '${id}'AND ContentType = 'application/pdf'

Query PDF saved as Salesforce Files

CODE

SELECT ContentDocument.Id, ContentDocument.TitleFROM ContentDocumentLinkWHERE LinkedEntity.Id = '${id}'AND ContentDocument.FileExtension = 'pdf'

2. Retrieve PDF attached to the related object of the main record

These queries collect PDF from the related object of the main record.

Similar query to retrieve PDF linked to the main record. However, the value ${id} is changed to the preferred field API.

For example: When the document is generated from Opportunity object, and you want to merge PDF attachments from the Account object.

Query PDF saved as Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE ParentId = '${Opportunity.Account.Id}'AND ContentType = 'application/pdf'

Query PDF saved as Salesforce Files

CODE

SELECT ContentDocument.IdFROM ContentDocumentLinkWHERE LinkedEntity.Id = '${Opportunity.Account.Id}'AND ContentDocument.FileExtension = 'pdf'

3. Retrieve PDF files attached to the related list's line item

Similar query to retrieve PDF attached to the main record. However, the value ${id} is changed to the field API of the line item ID . The query must be created under the related list tag.

For example:

Include product description of the Opportunity Line Item to the quote
Include service terms & conditions of the Work Order Line Item to the report
Include data sheet or related attachments of a Knowledge Article to the case response

Query PDF saved as Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE ParentId = '${i.Product2.Id}'AND ContentType = 'application/pdf'

Query PDF saved as Salesforce Files

CODE

SELECT ContentDocument.IdFROM ContentDocumentLinkWHERE LinkedEntity.Id = '${i.Product2.Id}' AND ContentDocument.FileExtension = 'pdf'

4. Retrieve images attached to the main record.

The queries collects all images of the main record.

Use cases:

Include your customer logo to the document generated from Account object
Include your employee image to the document generated from Contact object

Query images saved as Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE Parent.Id = '${id}'AND ContentType IN ('image/png', 'image/jpg', 'image/jpeg')

Query images saved as Salesforce Files

CODE

SELECT ContentDocument.IdFROM ContentDocumentLinkWHERE LinkedEntity.Id = '${id}'AND ContentDocument.FileType IN ('png', 'jpg', 'jpeg')

5. Retrieve images attached to the related object of the main record.

Similar query to retrieve images linked to the main record. However, the value ${id} is changed to the preferred field API.

For example: Generate a quote from Opportunity object and include the customer logo linked to the Account

Query images saved as Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE Parent.Id = '${Opportunity.Account.Id}'AND ContentType IN ('image/png', 'image/jpg', 'image/jpeg')

Query images saved as Saleforce Files

CODE

SELECT ContentDocument.IdFROM ContentDocumentLinkWHERE LinkedEntity.Id = '${Opportunity.Account.Id}'AND ContentDocument.FileType IN ('png', 'jpg', 'jpeg')

6. Retrieve images attached to the line item.

Similar query to retrieve images linked to the main record. However, the value ${id} is changed to the field API of the line item ID . The query must be created under the related list tag.

Use cases:

Generate a quote from Opportunity object and include images of the Opportunity Line Item
Generate a report from Work Orders object and include images of the Work Order Line Item

Query images saved as Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE Parent.Id = '${i.Id}'AND ContentType IN ('image/png', 'image/jpg', 'image/jpeg')

For example: Query Opportunity Product's images from Attachments

CODE

SELECT Id, NameFROM AttachmentWHERE Parent.Id = '${i.Product2.Id}'AND ContentType IN ('image/png', 'image/jpg', 'image/jpeg')

Query images saved as Salesforce Files

CODE

SELECT ContentDocument.IdFROM ContentDocumentLinkWhere LinkedEntity.Id = '${i.Id}'AND ContentDocument.FileType IN ('png', 'jpg', 'jpeg')

For example: Query Opportunity Product's images from Salesforce Files

CODE

SELECT ContentDocument.IdFROM ContentDocumentLinkWhere LinkedEntity.Id = '${i.Product2.Id}'AND ContentDocument.FileType IN ('png', 'jpg', 'jpeg')

7. Query product summary for an Account

These queries retrieve all products across Opportunities for an Account. At the same time, you can print out the total quantity and total price of product sold.

Use case:

Create a holistic Account Report or Strategic Account Plan

Query sold products from Won Opportunities

CODE

SELECT Product2.Name, Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPriceFROM OpportunityLineItemWHERE Opportunity.Account.Id = '${id}'AND Opportunity.IsWon = trueGROUP BY Product2.Name, Product2.FamilyORDER BY Product2.Family

Query projected products from on-going Opportunities

CODE

SELECT Product2.Name, Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPriceFROM OpportunityLineItemWHERE Opportunity.Account.Id = '${id}'AND Opportunity.IsClosed = falseGROUP BY Product2.Name, Product2.FamilyORDER BY Product2.Family

8. Query product summary for an Account by Family

These queries retrieve all opportunity products for an Account, groups by family, and sums the total price for each family.

Query sold products from Won Opportunities

CODE

SELECT Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPriceFROM OpportunityLineItemWHERE Opportunity.Account.Id = '${id}'AND Opportunity.IsWon = trueGROUP BY Product2.FamilyORDER BY Product2.Family ASC

Query projected products from on-going Opportunities

CODE

SELECT Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPriceFROM OpportunityLineItemWHERE Opportunity.Account.Id = '${id}'AND Opportunity.IsClosed = falseGROUP BY Product2.FamilyORDER BY Product2.Family ASC

9. Query with Date Literals

Use Date Literals to filter the query results in a relative range of time, such as last month, last week, next year, or next (numbers of) days. See list of date literal expression in SOQL

Retrieve Account created in the last 2 months

CODE

SELECT Id, Name, CreatedDateFROM AccountWHERE CreatedDate = LAST_N_MONTHS:2

Retrieve Opportunity created by end-users in the last 2 weeks

CODE

SELECT Id, Name, Account.NameFROM OpportunityWHERE CreatedDate = LAST_N_WEEKS:2 AND Owner.Id = '${User.Id}'

10. Query with Date Functions

Use Date Functions Date to group or filter data by date periods such as day, calendar month, or fiscal year. See list of date functions in SOQL

Retrieve Opportunity closed in December

CODE

SELECT Name, CloseDateFROM OpportunityWHERE CALENDAR_MONTH(CloseDate) = 12