References for Odata filters
In this article
Use the $filter system query option to set criteria for which entities will be returned.
The Web API supports the standard OData filter operators listed in the following table.
| Operator | Description | Odata Filter field example |
|---|---|---|
| Comparison Operators | ||
eq |
Equal | revenue eq 100000 |
ne |
Not Equal | revenue ne 100000 |
gt |
Greater than | revenue gt 100000 |
ge |
Greater than or equal | revenue ge 100000 |
lt |
Less than | revenue lt 100000 enddate lt utcnow() (comparisons work with dates) |
le |
Less than or equal | revenue le 100000 |
| Logical Operators | ||
and |
Logical and | revenue lt 100000 and revenue gt 2000 |
or |
Logical or | contains(name,'(sample)') or contains(name,'test') |
not |
Logical negation | not contains(name,'sample') |
| Grouping Operators | ||
( ) |
Precedence grouping | (contains(name,'sample') or contains(name,'test')) and revenue gt 5000 |
Note
This is a sub-set of the 11.2.5.1.1 Built-in Filter Operations. Arithmetic operators and the comparison has operator are not supported in the Web API.
The Web API supports these standard OData string query functions:
| Function | Odata Filter field example |
|---|---|
contains |
contains(name,'(sample)') |
endswith |
endswith(name,'Inc.') |
startswith |
startswith(name,'a') |
Note
This is a sub-set of the 11.2.5.1.2 Built-in Query Functions. Date, Math, Type, Geo and other string functions aren’t supported in the web API.
In the Odata filter, there's a nuance to filtering on null values. The "null" expression from the assistant is not needed. Instead, just use plain text in the odata query.
The following example will include rows where ctd_myfield contains data
ctd_myfield ne null
Dataverse provides a number of special functions that accept parameters, return Boolean values, and can be used as filter criteria in a query. See xref:Microsoft.Dynamics.CRM.QueryFunctionIndex for a list of these functions. The following is an example of the searching for accounts with a number of employees between 5 and 2000.
GET [Organization URI]/api/data/v9.1/accounts?$select=name,numberofemployees
&$filter=Microsoft.Dynamics.CRM.Between(PropertyName='numberofemployees',PropertyValues=["5","2000"]) More information: Compose a query with functions.
The Web API allows you to use two lambda operators, which are any and all to evaluate a Boolean expression on a collection.
The any operator returns true if the Boolean expression applied is true for any member of the collection, otherwise it returns false. The any operator without an argument returns true if the collection is not empty.
By using $apply you can aggregate and group your data dynamically. Possible use cases with $apply:
| Use Case | Example |
|---|---|
| List of unique statuses in the query | accounts?$apply=groupby((statuscode)) |
| Aggregate sum of the estimated value | opportunities?$apply=aggregate(estimatedvalue with sum as total) |
| Average size of the deal based on estimated value and status | opportunities?$apply=groupby((statuscode),aggregate(estimatedvalue with average as averagevalue) |
| Sum of estimated value based on status | opportunities?$apply=groupby((statuscode),aggregate(estimatedvalue with sum as total)) |
| Total opportunity revenue by Account name | opportunities?$apply=groupby((parentaccountid/name),aggregate(estimatedvalue with sum as total)) |
| Primary contact names for accounts in 'WA' | accounts?$apply=filter(address1_stateorprovince eq 'WA')/groupby((primarycontactid/fullname)) |
| Last created record date and time | accounts?$apply=aggregate(createdon with max as lastCreate) |
| First created record date and time | accounts?$apply=aggregate(createdon with min as firstCreate) |
The aggregate functions are limited to a collection of 50,000 records. Further information around using aggregate functionality with Dataverse can be found here: Use FetchXML to construct a query.
Additional details on OData data aggregation can be found here: OData Extension for Data Aggregation Version 4.0. Note that Dataverse supports only a sub-set of these aggregate methods.
Odata introduction - Jon Levesque and Ahmad Najjar on YouTube
- What is Odata
- What things do you use it for
- How does this apply to Flow?
- Operators (29:26)
- Functions (30:05)
Effective November 2020:
- Common Data Service (CDS) has been renamed to Microsoft Dataverse. Learn more
- Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more