Important

Using pv0 functionality in External Data queries is available for preview in sandboxes during the Preview Period (October 8th - November 4th). This functionality will be available for production orgs upon the Winter '20 Release on November 4th, 2020.

This article walks through creating a Conga Query using OData syntax to query your external data sources. Each OData Conga Query you create will be appended to an individual external data source previously created.

Important

The OData service must support the necessary URL components query syntax. For more information, see Sections 2 and 5 in OData's documentation.

Behind the scenes, Conga Composer will combine each OData Endpoint with a Composer OData query to gather your data. The resulting url used to call your OData data source will take the form:

https://<your-domain>.com/yourODataEndpoint/Products?$orderby=Name&$format=json

Important

The query syntax for the OData query must contain $format=json to retrieve and merge data with Conga Composer.

Example External Data Queries

External Data query syntax is different than the SOQL Query syntax used to gather data in Salesforce. See the examples below for example External Data query syntax.

Use pv functionality to dynamically filter the results of your query. When using pv functionality in an OData query, you must define the pv in both the query syntax and in the xid parameter value. For more information on using pv functionality with queries, see Dynamically Filter Reports and Queries with pv Values, xid, and the examples with pv functionality below.

  • Query that returns Products ordered by Price
    • Products?$format=json&$orderby=Price
  • Query that returns only the first 10 Product records ordered by Price
    • Products?$format=json&$orderby=Price&$top=10
  • Query that returns the Name and Price of all Products
    • Products?$format=json&$select=Name,Price 

      The SOQL equivalent of this query is SELECT Name, Price FROM Products

  • Query that returns Products that have more Units in Stock than a specific field value on an Account record
    • Products?$format=json&$filter=UnitsInStock gt {pv0]
    • syntax for the xid parameter in the Conga Solution:
      •  &xid=ExternalProduct:CongaData:a045000001DjTva?pv0={!Account.AverageStock}

For more information on the external data query syntax, see Section 5 (Query Options) of OData's documentation. For information on additional OData query filters and functionality, see the Querying Data section of OData's Basic Tutorial.

Conga's External Data feature is currently set up to retrieve a list of object records (Entity Collections) only, as opposed to just a single object record (Individual Entity by ID, Individual Property, and Individual Property Raw Value). Do not use query syntax, such as (1) or (3) to attempt to return 1 or 3 specific records. Instead, use additional filters in the query syntax to filter the returned list of data. For more information, see OData's Basic Tutorial.

Creating an External Data Query

  1. Navigate to the Conga Queries tab.
  2. Click New.
  3. Enter the information for your Conga Query using the following:
    1. Name: A name for this Conga Query.
    2. Description: (Optional) A description for this Conga Query.
    3. SOQL Select Statement: This is where your OData query will reside. Use OData query syntax here instead of SOQL. The OData query you enter here will be related to an endpoint when you build your Composer solution in Step 3.
      • OData Query Example: Products?$orderby=Name&$format=json
  4. Click Save.

Repeat steps 1 – 4 for each OData query you’d like to integrate.