Filter by, order by in SharePoint actions

Filter by, order by in SharePoint actions

☝
TL;DR - If you are using get files or get items in your Power Automate workflows, you really want to make every filter by or order by queries as efficient as possible. Requesting large and even small amounts of data from a service like SharePoint is quite taxing and you always want to make sure the request only the data you need. The queries follow the OData standard, so you might already be familiar with some terms.

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.
    2. ⛔
    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.

S
Screenshot of a sample get items action in Power Automate, showing a date query.

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.

Equals
eq
Text, Person, Number, Choice, Null
Not Equals
ne
Text, Person, Number, Choice
Greater
gt
Number, DateTime
Greater Or Equals
ge
Number, DateTime
Less
lt
Number, DateTime
Less Or Equals
le
Number, DateTime
Starts With
startswith(column,string)
Text, Choice
Substring
substringof(string,column)
Text, Choice, Lookup
Contains
contains(field,string)
Text

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

⛔
For filtering excel tables its even more weird. Excel only supports eq, ne, contains, startswith and endswith. Also columns names cannot contain any special characters or spaces. Furthermore, concatenating with AND or OR is also not supported. If you want to filter further, you would need to list all rows and then use a filter array action. Therefore, Excel is not a database.

Outlook - Get events

⛔
Sometimes Microsoft makes our life difficult by having to use different syntax depending on the service. If you want to order by the start date from the action Get Events in Outlook Office 365 you have to use this format Start/DateTime asc. At the end the connector is just connecting to the corresponding Graph endpoint. Therefore, you can see the correct JSON schema in the response example here: https://learn.microsoft.com/en-us/graph/api/user-list-events?view=graph-rest-1.0&tabs=http#response-1

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.

💡

The http request to SharePoint action also provides other options.

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.

image
💡
If you use a top count of “1” the action will still return the results as an array. The Apply to Each action will be required and automatically added for the values you use from this action. Otherwise, you can use the expressions first() to not require a loop.

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'