Skip to main content
Conga Support

Conga Query Library

Below are sample queries you can copy and use for your solutions.

ATTACHMENTS


Retrieve Attachments as Templates or eMail Attachments

The query collects the attachments from the record to be used as templates.

SELECT Id
FROM Attachment
WHERE ParentId = '{pv0}' AND Name NOT IN ('jpg', 'tiff', 'png')

Get Attachments as Images

The query collects the image attachments from the selected record. The images can be merged into your template.

SELECT Id
FROM Attachment
WHERE ParentId = '{pv0}'
AND (Name LIKE '%png%' OR Name LIKE '%jpg%' OR Name LIKE '%jpeg%' OR Name LIKE ‘%tiff%’)

Query Attachments for Images (Content Type)

SELECT Id
FROM Attachment
WHERE ParentId = '{pv0}'
AND ContentType IN ('image/bmp', 'image/gif', 'image/jpeg')

Select Attachment Images from Multiple Parent Records

The query collects image attachments from one or more parent IDs.

SELECT Id
FROM Attachment
WHERE ParentId IN ({pv0})
AND (Name LIKE '%png%' OR Name LIKE '%jpg%' OR Name LIKE '%jpeg%')

Select Attachments Two Levels Down

The query collects attachments from contact records related to the selected Account.

SELECT Id
FROM Attachment
WHERE ParentId IN (SELECT Id FROM Contact WHERE AccountId = '{pv0}')

Select Attachment from Contact in a Campaign

Selects the ID from Contacts that are part of a specific campaign where the length of the attachment body exceeds a specific number of characters.

SELECT Id, BodyLength
FROM Attachment
WHERE parentid IN (SELECT contactid FROM campaignmember WHERE campaignid = '701o0000000JrJX')
AND bodylength > 15000

Select Files Stored as Attachments in Salesforce

Select attachments stored as Files in Salesforce. Requires the use of a QVAR (query variable) to pass the result of the query into the value of the TemplateID parameter. The below query and button URL will include the file in the output (along with whatever merged documents were generated).

FILES Related List
SELECT ContentDocument.id FROM ContentDocumentLink WHERE LinkedEntityID = '{pv0}'

Button URL:
&Qvar0ID=id of Conga Query (above)
&TemplateID={QVAR0}

 

FILES


Retrieve an image from Salesforce Files

Retrieves an image from Salesforce files.

SELECT ContentDocumentId, ContentDocument.FileExtension
FROM ContentDocumentLink
WHERE LinkedEntityId = '{pv0}'
AND (ContentDocument.FileExtension LIKE '%jpg%'
OR ContentDocument.FileExtension LIKE '%png%'
OR ContentDocument.FileExtension LIKE '%gif%')

OPPORTUNITY QUERIES


Retrieve all Opportunities for an Account

Retrieves all Opportunity records for an account.

SELECT Amount, CloseDate, Name, Type, Probability, StageName
FROM Opportunity
WHERE AccountId = '{pv0}'

Product Summary Query by Family

Retrieves all products from a Opportunity, groups by family, and sums the total price for each family.

SELECT PricebookEntry.Product2.Family, SUM(Quantity) Qty, SUM(TotalPrice) Total
FROM OpportunityLineItem
WHERE OpportunityId = '{pv0}'
GROUP BY PricebookEntry.Product2.Family
ORDER BY PricebookEntry.Product2.Family ASC

Date Functions with Opportunity Query

The query demonstrates how to use a calendar year filter.

SELECT Campaign.Name, SUM(Amount), MAX(Amount), AVG(Amount) FROM Opportunity
WHERE Campaign.Name != ''
AND Calendar_Year(CloseDate) = THIS_YEAR
GROUP BY Campaign.Name
ORDER BY Campaign.Name

Date Query with Calendar Year Filters

The query retrieves data from last year and this year using calendar year filters.

SELECT Campaign.Name FROM Opportunity
WHERE Campaign.Name != ''
AND (Calendar_Year(CloseDate) = LAST_YEAR OR Calendar_Year(CloseDate) = THIS_YEAR)
GROUP BY Campaign.Name
ORDER BY Campaign.Name

Date Query with Calendar Year Filters

The query retrieves opportunity fields and groups based on calendar month of the close date.

SELECT SUM(Amount), CALENDAR_MONTH(CloseDate)
FROM Opportunity
GROUP BY CALENDAR_MONTH(CloseDate)

Primary Contact Role from Opportunity

The query retrieves the primary Contact Role from Opportunity

SELECT Id
FROM OpportunityContactRole
WHERE opportunityid =’{pv0}’

Aggregated Opportunity Query (Sum)

The query retrieves an aggregated Amount field from Opportunity and groups the result set by Name.

SELECT Name, Sum(Amount)
FROM Opportunity
WHERE id = ‘{pv0}’
GROUP BY Name

Aggregated Opportunity Query (COUNT)

The query retrieves all opportunities, counts the total records, and then groups by StageName.

SELECT StageName, COUNT(Id)
FROM Opportunity
GROUP BY StageName

Aggregated Opportunity Query

The query retrieves Opportunity Line Item info from the selected opportunity record and provides a count of unique record values in the results set.

SELECT COUNT_DISTINCT(PricebookEntryId)
FROM OpportunityLineItem
WHERE OpportunityId = '{pv0}'

 

CONTACT QUERIES


Query with Limit

The query retrieves the first fifty records of the results set.

SELECT ID, FirstName, LastName
FROM Contact
ORDER BY Last Name ASC
LIMIT 50

Query with Limit and Offset

The query retrieves the second fifty records of the results set.

SELECT ID, FirstName, LastName
FROM Contact
ORDER BY Last Name ASC
LIMIT 50
OFFSET 50

Query with Limit and Offset, Ascending Order

The query retrieves a specific number of records in the position specified by the values passed through the pv1 and pv2 filters.

SELECT ID, FirstName, LastName
FROM Contact
WHERE AccountId = ‘{pv0}’
ORDER BY Last Name ASC
LIMIT {pv1}
OFFSET {pv2}

Query to Retrieve Data from Chatter

The query retrieves data from Chatter linked to the selected record.

SELECT ContentDocumentId
FROM ContentDocumentLink
WHERE LinkedEntityId = '{pv0}'

Semi-Join to Select Images from Chatter

The query uses a semi-join to select images from a Chatter feed.

SELECT LatestPublishedVersion.Image_URL_Content_Formula__c
FROM ContentDocument
WHERE LatestPublishedVersionId
IN (SELECT RelatedRecordId FROM AccountFeed WHERE ParentId = '{pv0}')

 

LOGIC QUERIES


Using AND or OR Logic

The query retrieves fields from an Account record where the annual revenue is over $1 million and the Name matches one of two values.

SELECT ID, Name, AnnualRevenue
FROM Account
WHERE annualrevenue > 1000000
AND (Name Like 'B%' OR Name Like 'E%')

Multiple OR values using IN Clause

The query uses multiple OR values in query by using the IN clause.

SELECT id
FROM Case
WHERE use_case__c IN ('Quotes','Contracts','Proposals')

 

NESTED QUERIES


Nested Select Query

The query retrieves the primary Contact Role and all of the quotes from the Opportunity record.

SELECT Id,
(SELECT IsPrimary, Role, Contact.Name FROM OpportunityContactRoles
WHERE Role = 'Technical Buyer'),
(SELECT GrandTotal, LineItemCount, OpportunityId FROM Quotes)
FROM Opportunity
WHERE Id = '{pv0}'

Nested Select with Semi-Join from Child

The query retrieves all of the contacts and custom object records based on filter criteria that are related to a particular contact record.

SELECT Id,
(SELECT AccountId, AssistantPhone, CleanStatus, Account.Name FROM Contacts WHERE Title LIKE '%facilities%'),
(SELECT Account__c, Biceps__c FROM Custom_Objects__r WHERE Biceps__c = 'Jacked')
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact where id IN ('{pv0}'))

Semi-Join Query with Filter on Child

The query retrieves contacts that are related to the same account as the selected opportunity.

SELECT Id, name
FROM Contact
WHERE AccountId IN (Select AccountId from Opportunity where stagename = 'closed won' )
WORK.COM QUERIES

Feedback Request Query

The query retrieves fields from the work.com feedback request object.

SELECT Id
FROM WorkFeedbackRequest
WHERE PerformanceCycleId
IN (SELECT Id FROM WorkPerformanceCycle WHERE Name = 'PSC 2014')

Performance Cycle Query

The query retrieves fields from the work.com performance cycle object.

SELECT Id,Name,CreatedDate
FROM WorkPerformanceCycle
WHERE Name = 'PSC 2016’

Approval Process Query

The query retrieves approval information from the selected record.

SELECT ActorId, Comments, CreatedById, CreatedDate, OriginalActorId, ProcessInstanceId, Id, StepStatus, SystemModstamp
FROM ProcessInstanceStep
WHERE ProcessInstanceId IN (SELECT Id FROM ProcessInstance WHERE TargetObjectId = '{pv0}')

 

ACCOUNT BRIEF QUERIES


Retrieve all Cases for an Account

The query retrieves all cases for an account.

SELECT Reason, Type, IsClosed, ClosedDate, SuppliedCompany, Description, SuppliedName, Status
FROM Case
WHERE AccountId = '{pv0}'

Retrieve all Opportunities for an Account

The query retrieves all opportunities for an account.

SELECT Amount, CloseDate, Name, Type, Probability, StageName
FROM Opportunity
WHERE AccountId = '{pv0}'

Retrieve all Contacts for an Account

The query retrieves all contacts for an account.

SELECT Birthdate, Phone, Email, Name, LastName, Salutation, Title
FROM Contact
WHERE AccountId = '{pv0}'

 

MAIL MERGE QUERIES via QUICKMERGE


These queries are to be used with Conga Mail Merge solutions.

EXAMPLE 1

Master

The Master query retrieves the fields selected as the master data set in the Mail Merge process. We are grouping by an account ID and name to give us a unique document per account.

SELECT Id, Name, BillingCity, BillingCountry, BillingState, BillingStreet, BillingPostalCode FROM Account
GROUP BY Account.Id, Account.Name

Detail

The Detail query gathers child data (Opportunity in this example) that will be related to the master records. The grouping identifies which parent records to relate to the child data.

SELECT Account.Id, Account.Name, SUM(Amount) FROM Opportunity
GROUP BY Account.Id, Account.Name

EXAMPLE 2

Master

The Master query retrieves the fields selected as the master data set in the Mail Merge process. This query groups by a contact to give us a unique document per Contact.

SELECT Id, Email, Name, HomePhone, MailingCity, MailingCountry, MailingState, MailingStreet, MailingPostalCode FROM Contact
GROUP BY BillingContact__c, Account.Name

Detail

The Detail query gathers child data (Opportunity in this example) that will be related to the master records. The grouping identifies which parent records to relate to the child data.

SELECT BillingContact__c, SUM(Amount), Account.Name FROM Opportunity
GROUP BY BillingContact__c, Account.Name