Menu

Documill Dynamo Query Library

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

SELECT Id, Name 
FROM Attachment 
WHERE ParentId = '${id}' 
AND ContentType = 'application/pdf'

Query PDF saved as Salesforce Files

SELECT ContentDocument.Id, ContentDocument.Title 
FROM ContentDocumentLink 
WHERE LinkedEntity.Id = '${id}' 
AND ContentDocument.FileExtension = 'pdf'

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

SELECT Id, Name 
FROM Attachment 
WHERE Parent.Id = '${i.Id}' 
AND ContentType IN ('image/png', 'image/jpg', 'image/jpeg')

For example: Query Opportunity Product's images from Attachments

SELECT Id, Name 
FROM Attachment 
WHERE Parent.Id = '${i.Product2.Id}' 
AND ContentType IN ('image/png', 'image/jpg', 'image/jpeg')

Query images saved as Salesforce Files

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

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

SELECT ContentDocument.Id 
FROM ContentDocumentLink 
Where 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

SELECT Product2.Name, Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPrice 
FROM OpportunityLineItem 
WHERE Opportunity.Account.Id = '${id}' 
AND Opportunity.IsWon = true 
GROUP BY Product2.Name, Product2.Family 
ORDER BY Product2.Family

Query projected products from on-going Opportunities

SELECT Product2.Name, Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPrice 
FROM OpportunityLineItem 
WHERE Opportunity.Account.Id = '${id}' 
AND Opportunity.IsClosed = false 
GROUP BY Product2.Name, Product2.Family 
ORDER 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

SELECT Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPrice 
FROM OpportunityLineItem 
WHERE Opportunity.Account.Id = '${id}' 
AND Opportunity.IsWon = true 
GROUP BY Product2.Family 
ORDER BY Product2.Family ASC

Query projected products from on-going Opportunities

SELECT Product2.Family, SUM(Quantity) totalQty, SUM(TotalPrice) totalPrice 
FROM OpportunityLineItem 
WHERE Opportunity.Account.Id = '${id}' 
AND Opportunity.IsClosed = false 
GROUP BY Product2.Family 
ORDER 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

SELECT Id, Name, CreatedDate 
FROM Account 
WHERE CreatedDate = LAST_N_MONTHS:2

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

SELECT Id, Name, Account.Name 
FROM Opportunity 
WHERE 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

SELECT Name, CloseDate 
FROM Opportunity 
WHERE CALENDAR_MONTH(CloseDate) = 12

Comments

0 comments