OData filter query cheat sheet for SharePoint, Dataverse and SQL Server

A Filter Query makes the data source do the filtering, so the flow fetches only the rows it needs instead of everything followed by a Filter array. It's faster, uses fewer requests, and is often the only way to work with large lists. But each connector speaks its own dialect, and a filter that works on Dataverse fails on SharePoint. This page lists what each one accepts.

The basics that apply everywhere

• Text values go in single quotes: Title eq 'Contoso'. Numbers don't.
• To match a single quote inside a value, double it: LastName eq 'O''Brien'.
• To build a filter from dynamic content, type the fixed parts and insert the token where the value goes: Title eq '<Title token>'. Remember the quotes around text tokens.

SharePoint: Get items and Get files

Supported:

• eq, ne, lt, le, gt, ge
• and, or
• startswith(Column,'text')
• substringof('text',Column): "contains", with the arguments the other way round
• day(), month(), year(), hour(), minute(), second() on dates

Not supported: endswith, contains, tolower/toupper, trim, concat, substring and arithmetic. These fail or are ignored on SharePoint.

SharePoint traps:

• Use the internal name. Find it in the list settings: open the column and read the Field= part of the URL. Renaming a column doesn't change it, so a column now called "Due date" may still be Deadline internally.
• Multi-value lookup and multi-person columns can't be filtered with a filter query. Filter those after the fact with Filter array.
• Person columns: SharePoint's REST service matches users by display name, not email. That's brittle; filter on a lookup ID or after the fact where you can.
• Big lists: on a list over 5,000 items, a filter that matches nothing in the first 5,000 can return no results. Turn on Pagination in the action's settings. Get items also returns only 100 items unless you set Top Count (up to 5,000) or pagination.

Dataverse: List rows

Dataverse supports the most.

Supported:

• eq, ne, gt, ge, lt, le
• and, or, not, and brackets
• contains, startswith, endswith
• over 60 Dataverse query functions, named with the prefix Microsoft.Dynamics.CRM.

Dataverse traps:

• Type only the expression. Don't include $filter=; the action adds it.
• Use logical names (createdon, cr123_duedate), not display names. You'll find them in the table's column properties.
• Encode special characters in values: + → %2B, & → %26, # → %23. For example, contains(name,'+123') must be written contains(name,'%2B123').
• No leading wildcards. startswith(name,'%value') isn't supported.
• Text filters ignore case.
• Too many conditions: a query can have at most 500 conditions; more gives TooManyConditionsInQuery. Use Microsoft.Dynamics.CRM.In(...) for long lists of values.

SQL Server: Get rows (V2)

Get rows takes an OData filter such as stringColumn eq 'string' or numberColumn lt 123, plus $orderby and aggregation ($apply).

SQL Server traps:

• Date filters are unreliable through Get rows, especially over the on-premises gateway. If a date filter errors, try one of these instead:
  • Execute a SQL query (V2) with parameters. It's not supported for on-premises SQL Server or connections through a gateway.
  • A view that exposes the date you need.
  • A stored procedure.
• Complex filters hit a limit: "OData query syntax tree has exceeded nodes count limit of '100'." Simplify the conditions, for example use a range instead of many or values.

Errors that mean "fix your filter"

Sources

• Microsoft Learn: Working with Get items and Get files: filter queries
• Microsoft Learn: OData query operators in SharePoint REST
• Microsoft Learn: Use lists of rows in flows (Dataverse)
• Microsoft Learn: Filter rows by using OData (Dataverse)
• Microsoft Learn: SQL Server connector reference
• Microsoft Learn: Filter and copy data with Power Automate