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

This query will collect the attachments from the record you are on to be used as templates.

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

Get Attachments as Images

This query collects the image attachments from the selected record. These 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

This 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

This 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}

 

OPPORTUNITY QUERIES


Retrieve all Opportunities 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

This 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

This 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

This 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

This query retrieves the primary Contact Role from Opportunity

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

Aggregated Opportunity Query (Sum)

This 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)

This 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

This 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

This 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

This 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

This 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

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

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

Semi-Join to Select Images from Chatter

This 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

This 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

This 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

This 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

This 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

This 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

This 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

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

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

Approval Process Query

This 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

This 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

This 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

This 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
  • Was this article helpful?