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 (Not(Name LIKE '%jpg%'))
      AND (Not(Name LIKE '%jpeg%')) AND (Not(Name LIKE '%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 Query: SELECT ContentDocument.id FROM ContentDocumentLink WHERE LinkedEntityID = '{pv0}'
    • Button URL syntax: &Qvar0ID=[id of Conga Query (above)]&TemplateID={QVAR0}

Files

  • Retrieve 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
    • 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
    • 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 OpportunityWHERE 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
    • 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
    • SELECT Reason, Type, IsClosed, ClosedDate, SuppliedCompany, Description, SuppliedName, Status
      FROM Case WHERE AccountId = '{pv0}'
  • Retrieve all Opportunities for an Account
    • SELECT Amount, CloseDate, Name, Type, Probability, StageName FROM Opportunity WHERE AccountId = '{pv0}'
  • Retrieve all Contacts for an Account
    • SELECT Birthdate, Phone, Email, Name, LastName, Salutation, Title
      FROM Contact WHERE AccountId = '{pv0}'