Wow thats a lot of data. Never fear, ODATA is here!
Hi, and welcome to another tutorial from myself and the team here at NextStep Creations. Today we’re going to be talking about ODATA queries.
ODATA queries are a fantastic way to filter or organize your data in PowerAutomate. They can be used for many different sources, such as:
- SharePoint
- SQL
- API calls
- Dynamics
In this guide, we’ll run through how to use OData queries and some example use cases.
Basic ODATA queries
The general syntax of an ODATA query is:
fieldname operation value
The fieldname is the column or field that you wish to search (something like customerName, customerType, mobileNumber)/
The operation is one of the following:
- eq: Equal
- gt: Greater Than
- lt: Less Than
- ge: Greater Than or Equal To
- le: Less Than or Equal To
- ne Not Equal
You can also stack more than one operator/query, by using “AND” or “OR” to specify multiple conditions.
And finally, value is what you are comparing your column to. This can be a string, a date, an integer, a floating point number, or even a boolean (true/false).
Note: Flow requires single quote (‘ ‘) around text in the value section – customer eq ‘Jacob’
Using an ODATA Query
Alright, lets get into it. First, create a brand new flow. Just use the “Manually trigger a flow” start point for now.
In this example, I’m going to add a “Get Items” step, that will pull all my items from a SharePoint list. I’ll run the Flow, so we can see what (unfiltered) data we get:
There’s a fair few lines of data there. I don’t need all of them…..
Let’s say I only want the items from the list where the title is “odata”. Go back and edit your Flow, and you’ll see an option in the Get Items step called “Filter Query”. In that box, put the following query:
Title eq ‘odata’
Save it, and run your flow:
Voila! That’s a much smaller list, with only the matching items in it.
Think of the possibilities….no more having to loop across all of your data trying to find a certain value., you can just make sure you only have items that match your criteria in the first place.
Multiple ODATA Queries
The fun doesn’t stop there! Let’s say I need to find items with the title of “odata”, and I also need to find the one with the ID of 11.
Edit your Flow again, and change your filter query to:
Title eq ‘odata’ and ID eq 11
Time to run that flow! Let’s see what we get:
Awesome! One item and one item only. That’s a nice way to filter out data before we even operate on it.
Wrap up & thoughts
This is a basic intro to using filter queries in PowerAutomate. Stay tuned for a deeper dive blog into some more of the intricate functionality.
Reach out to us on Twitter at NextStep Aus, we love to see what you make out of our tutorials!