Table of contents

• The basics
• Filter by query
• Comparison operators
• Logical operators
• SharePoint examples by data type
• Comparing queries from other services
• Excel - List rows present in table
• Outlook - Get events
• Order By
• Managing results size with top count or pagination
• Getting as many items/files as possible
• Limit output to only a few items/files
• Examples for complex filter queries
• Taxonomy or Managed Metadata
• Single select person Title / E-Mail
• Author or Editor with specific E-Mail
• Approval status
• List only folders or files
• Version numbers

The basics

There are two rules, when working with any kind of OData queries like filtering or sorting.

1. Rule: Always use the internal column names for the SharePoint queries. These internal names are automatically set when you create a column. The internal name never changes, even we you change the name again (its only the display name then). You can check your internal names from the list or library settings.
⛔

You should never have spaces or special characters with your columns. In case you do, check out this page: https://www.sharepointdiary.com/2011/06/sharepoint-field-display-name-vs-internal-name.html

If you need to filter or order SharePoint columns, you can find their internal names here: List of Internal Names for SharePoint Fields | SoftArtisans

2. Rule: Respect the data type. If you are checking for a text field, use single quotes. If you are filtering for number, do not use single quotes. See examples below.

Filter by query

The filter by query can be used to only request items or files, that match your condition(s). Your results will be more refined and help SharePoint to work with large lists or libraries (with more than 5000 items/files). The filter by query is a string and is typed into the Filter Query under advanced parameters.

Comparison operators

Comparison operator compare the field value to what your condition. Not all comparisons are always possible. There is even differences from service to service in Microsoft 365.

Logical operators

You can connect different filter expressions by using OR, AND as well as rounded brackets to define order.

SharePoint examples by data type

//equals string
StringField eq 'Test'
//equals number
NumberField eq 1

//startswith string
startswith(StringField,'This is my text')
startswith(Title,'MS 365 Roadmap')

//all items or file where the datetimefield is the specified date or in the future in SPO
//the format for date fields is always YEAR-MONTH-DAY
DateField eq '2024-03-23'
//in Dataverse a Date only field means to leave the quotes around your data
DateField eq 2024-04-23

//all items or files where datetimefield is the specified date AND time. Be aware that sharepoint stores date and time in utc.
DatetimeField ge '2020-09-14T13:22:12Z'
//Here dataverse will need the timestamp with quote, just like SPO

//Checks for all items or files where the string field is empty
StringField eq null

//boolean is 1 = true, 0 = false
BooleanField eq 1
BooleanField eq 0

//Contains works in the get events action for outlook
contains(Subject,'Vacation')

//substring with value, column name
substringof('Robert',NameOfPerson)

//Lookup values, also works with multi select lookup columns. In case of a multiselect lookup the eq acts as contains.
LookupFieldName/ID eq 5

//Combination
StringField eq null and startswith(ContentTypeId,'0x0101')

Comparing queries from other services

Excel - List rows present in table

Outlook - Get events

Order By

With the order by clause you can order your query results. This is pretty straight forward. Write down the columns internal name followed by asc (ascending) or desc (descending).

DateColumn asc
DateColumnB desc

Managing results size with top count or pagination

Getting as many items/files as possible

Usually SharePoint will return up to 100 items/files if you do not specify anything else. This might not be the complete data, even when using a filter query. In some cases you might want to have more than 100 items. For this use pagination. Use can configure this under the advanced settings within the action. You can turn it on and then add a number, that you want like 500.

The SharePoint action might take longer than usual to return the data. If you want to know more about how to handle large lists or libraries, read here: https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-get-items-and-get-files.

Limit output to only a few items/files

In some cases, you might only want to have the top 5 results or even just one item/file returned. With Top you can specify the max amount of data being returned by SharePoint. Enter a whole number into the input field.

Examples for complex filter queries

Taxonomy or Managed Metadata

Those types of columns are a little special. For context, the keyword “TaxCatchAll” refers to all managed metadata columns in your list or library and the “Term” is a general part of this query.

//Needs the Label|Guid (Value) property
TaxCatchAll/Term eq 'VALUE YOU ARE SEARCHING FOR'
//Needs the TermGuid property
TaxCatchAll/IdForTerm eq 'TERM GUID'

Single select person Title / E-Mail

Title is the display name of that person. Please note that the “EMail” is specifically with capital E and M.

Person/EMail eq 'tony.stark@avengers.com'
Person/Title eq 'Stark, Tony'

Author or Editor with specific E-Mail

Created by = Author, Modified by = Editor

Author/EMail eq 'tony.stark@avengers.com'
Editor/EMail eq 'Stark, Tony'

Approval status

OData__ModerationStatus eq 'Draft'
OData__ModerationStatus eq 'Pending'
OData__ModerationStatus eq 'Rejected'
OData__ModerationStatus eq 'Approved'

List only folders or files

//Only Folders
FSObjType eq 1 
//Only Files
FSObjType eq 0 

Version numbers

//Check version of page or document
OData__UIVersionString eq '1.0'
startswith(OData__UIVersionString,'0')

//even though it is saved as a string, greater equals seems to be working
OData__UIVersionString ge '1.0'