OData Query String Options
The OData adapters for Salesforce Connect use a subset of the OData 2.0 and 4.0 and 4.01 system functions and filter expression constructs to query external systems.
Required Editions
| Available in: both Salesforce Classic and Lightning Experience (not for high-data-volume external objects) |
Available in: Developer Edition Available for an extra cost in: Enterprise, Performance, and Unlimited Editions |
Salesforce automatically creates the OData queries so that you, as an administrator or developer, don’t have to. However, understanding how OData queries are generated—or even attempting manual OData queries—can help you troubleshoot issues with the external system’s OData producer. For details about each system query option, go to www.odata.org.
Salesforce Connect supports only the following OData system query options. All other options in the OData 2.0, 4.0, and 4.01 specifications are unused.
-
$count(OData 4.0 and 4.01) -
$filter -
$inlinecount(OData 2.0 only) -
$orderby -
$search(OData 4.0 and 4.01) -
$select -
$skip -
$top
A query search string to an external system is sent as a case-sensitive single phrase after removing all ASCII punctuation characters except hyphens (-). For example, if the search string is Sales & Marketing, the external system receives Sales Marketing.
$count (OData 4.0 and 4.01)
Specifies that the response must include the number of rows that the URI identifies
after $filter system query options are applied,
but before $top and $skip system query options are applied.
For OData 4.01, $count=true is hard coded and
for OData 4.0, $count=true when
Request Row Counts is enabled on the external data source.
The total items in the result set is the same as the LIMIT value in the query for
values less than 202. If the LIMIT value is greater than 202, then 202 items are
returned to indicate that more records exist in the next batch. For OData 4.0, if
Request Row Counts is disabled, Salesforce includes $count=false in all OData 4.0 queries of the
external data source, and 2000 items are returned in each result set.
| Examples | |
|---|---|
| User action in Salesforce | View or access an external object. |
| SOQL query | Any SOQL query of an external object |
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$select=SupplierID&$count=true&$top=26
|
$filter
Filters the collection of resources that’s addressed by a request URL. The response
contains the results that evaluate to true.
| Examples | |
|---|---|
| User action in Salesforce | Open a list view of cities from supplier records that are filtered so that the country is USA. |
| SOQL query |
SELECT City__c FROM Suppliers__x
WHERE Country__c = 'USA' ORDER BY City__c ASC LIMIT
26
|
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$orderby=City&$select=City,SupplierID&$inlinecount=allpages&$filter=Country+eq+'USA'&$top=26
|
$inlinecount (OData 2.0 only)
Specifies that the response must include a count of the number of rows that the URI
identifies after $filter system query options
are applied but before $top and $skip system query options are applied.
When Request Row Counts is enabled on the external data source,
Salesforce uses $inlinecount in all OData 2.0
queries of that external data source to determine the total number of items in each
result set. If Request Row Counts is disabled, $inlinecount is excluded from all OData 2.0 queries
of the external data source.
| Examples | |
|---|---|
| User action in Salesforce | View or access an external object. |
| SOQL query | Any SOQL query of an external object. |
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$select=SupplierID&$inlinecount=allpages&$top=26
|
$orderby
Sorts the result set in ascending or descending order. The fields in the ORDER BY
clause of the SOQL query don't always match the properties used by the $orderby option in the resulting OData query. If
you use the OFFSET clause in the SOQL query, the entity key property is added in the
resulting OData query.
| Examples | |
|---|---|
| User action in Salesforce | Open a list view of supplier records that are ordered by company name. |
| SOQL query |
SELECT CompanyName__c,ContactName__c
FROM Suppliers__x ORDER BY CompanyName__c ASC LIMIT
26
|
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$orderby=CompanyName&$select=CompanyName,ContactName,SupplierID&$inlinecount=allpages&$top=26
|
$search (OData 4.0 and 4.01)
Requests entities that match the search query string as a free-text search
expression. For OData 4.0, enable this option by selecting Use Free-Text
Search Expressions on the external data source. By default,
Use Free-Text Search Expressions isn’t enabled. The search
query string is used as the contains value in
the $filter system query option.
| Examples | |
|---|---|
| User action in Salesforce | View or access an external object. |
| SOQL query | Any SOQL query of an external object |
| Resulting OData query |
http://services.example.org/my.svc/Shippers?$select=CompanyName,Phone,ShipperID&$count=true&$search=Acme&$top=25
|
$select
Requests a limited set of properties for each entity.
| Examples | |
|---|---|
| User action in Salesforce | Open a list view of supplier records where the page layout displays the company name and contact name. |
| SOQL query |
SELECT
CompanyName__c,ContactName__c FROM Suppliers__x ORDER BY
CompanyName__c ASC LIMIT 26
|
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$orderby=CompanyName&$select=CompanyName,ContactName,SupplierID&$inlinecount=allpages&$top=26
|
$skip
Specifies the number of items in the queried collection to skip in the result set.
| Examples | |
|---|---|
| User action in Salesforce | Click to view the second page of a list view of supplier records that are ordered by city. |
| SOQL query |
SELECT City__c,CompanyName__c FROM
Suppliers__x ORDER BY City__c ASC OFFSET
25
|
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$orderby=City&$select=City,CompanyName,SupplierID&$inlinecount=allpages&$top=25&$skip=25
|
$top
Specifies the number of items in the queried collection to include in the result. The
value in the LIMIT clause of a SOQL query doesn’t
always match the requested $top value, because
the latter is modified as needed for client-driven paging and queryMore() calls.
| Examples | |
|---|---|
| User action in Salesforce | Open a list view of the top 25 supplier records. |
| SOQL query |
SELECT SupplierID__c FROM Suppliers__x
LIMIT 25
|
| Resulting OData query |
http://services.example.org/my.svc/Suppliers?$select=SupplierID&$inlinecount=allpages&$top=25
|
- OData 2.0 Query Options
By default, the search query string is used as thesubstringofvalue in the$filtersystem query option. - OData 4.0 and 4.01 Query Options
By default, the search query string is used as thecontainsvalue in the$filtersystem query option.
