In this blog post, we will learn how to use OData filter query Power Automate with SharePoint.
OData filter query Power Automate and SharePoint list
How SharePoint get items action uses filter query in Power Automate
The “Get items” action is used to retrieve items from a SharePoint list. You can specify a filter query to limit the results to specific items based on certain criteria using the OData query options to filter and retrieve specific items from the data source.
Here’s how to use OData filter queries in the “Get items” action:
- Add the Get items action and specify the Site address and the list name.
- In the Advanced Parameter select Filter Query where you can specify your OData filter query to filter the items you want to retrieve.
- Write Your OData Filter Query in the provided textbox after you select the Filter Query.
Filter query on the classic design
Filter query in the modern design
Power Automate OData filter query Examples
The Power Automate filter query is written in OData. You can use logical operators and functions to build complex filter expressions such as.
StringColumn operator ComparedDAta
The operator can be
- eq for equals
- ne for not equals
- gt for greater than
- lt for less than
- ge for greater than or equal to
- le for less than or equal to
- startswith() [column value begins with…]
- substringof() [column value contains…]
- not contains
Get items filter query Examples
In the following examples we will learn :
- How to add filter query in power automate?
- How to add multiple filter query in power automate ?
- and condition in filter query power automate.
- or condition in filter query power automate.
- filter query power automate blank.
- filter query power automate by date ,…….etc
In all examples we will use the system name to use in the filter query , You should open list settings for the SharePoint list and select the column the end of the URL you will see a Field= That is what you need to use in your query
power automate odata filter query eq
You can use the “eq” operator to filter for items where a particular field or column is equal to a specified value.
<Column Name> eq ‘<Value>’
In this example we will filter the items that has title equal Annual Travel
Title eq 'Annual Travel'
power automate odata filter query person field
Filtering based on a “Person” or “User” field in an OData filter query in Power Automate is different from filtering on standard fields and it will depend on the DataSource.
Example: Filter the person field by Email: to filter the person field by email you can write the OData filter .. CoulmnName/EMail operator then the email.
Requester/EMail eq 'firstname.lastname@example.org'
If you are using dynamc content as I have for the email to match …. remember to use single quotes ‘ your dynamic property ‘ around the dynamic content
power automate odata filter query not equal
In Power Automate, you can use the “ne” (not equal) operator in an OData filter query to filter data where a field does not equal a specified value.
<Column Name> ne ‘<Value>’
Filter for items where the “Auto_ApprovalCEOLimit” column is not equal to 5000.
Auto_ApprovalCEOLimit ne 5000
Power Automate OData filter query greater than or equal
You can use the “ge” (greater than or equal) operator in an OData filter query to filter data based on values that are greater than or equal to a specified value.
<Column Name> ge <Value>
Filter for items where the “Quantity” column is greater than or equal to 1000
Quantity ge 1000
This will return the rows of data that has quantity greater than or equal 1000.
Power Automate OData filter query not equal null.
DMApprovalReason ne null
This will return the data where DMApprovalReason not null
power automate sharepoint odata filter query choice field
Let’s assume you have a choice field named “Status ” in your SharePoint list.
Here’s an example OData filter query for retrieving items where the choice field is equal to Approved by CFO
Status eq 'Approved by CFO'
Power Automate OData filter query startswith
The “startswith” operator in an OData filter query is used to filter records based on whether a specified property starts with a given value.
This will retrieve items where the title starts with Annual.
Power Automate OData filter query contains
If you want to retrieve items where the “Title” property contains the substring “Annual,” the OData filter query would be:
Power Automate OData filter query substringof
Note : Put the field name as the second parameter in substringof() function.
- How To Use Pdf Function In PowerApps?
- Working With PowerApps Nested Gallery Control
- How Power Automate Create Excel Table Dynamically? | Power Automate
- How To Make A Csv File Utf-8 Encoded? | Power Automate
- How To Format Text In PowerApps? PowerApps Convert Text To Other Data Types
- Alternate Row Color In Gallery PowerApps: Simplest Way
- Check If PowerApps Contains Substring
- PowerApps If Statement And Nested If With Example
- Environment Variable In Power Platform With Examples
- Model Driven App With SharePoint Data Source
- PowerApps Get Day Of Week Date
- Subscribe to Power Platform Geeks YouTube channel.
- Register to Saudi Arabia Power Platform User Group.
- Have a related question? Please ask it at deBUG.to Community.