Today we are covering Odata filter in Power Automate and how it will ease your life while developing flows in Power Automate.
Microsoft says that "OData (Open Data Protocol) is an OASIS standard that establishes best practices for designing RESTful APIs". Furthermore they write that "One of the capabilities of OData is providing the ability to filter data using a standardized method across RESTful APIs, regardless if they are vendor provided or custom developed". (Source)
Enough theory, let's jump into practice and see how we can use this in our daily life. In my last article I used the OData filter already in my flows. Here is a quick reminder:
I used the the OData filter to list only the records that match my specific criteria. In this case I wanted to list only the records that are not equal a specific option set. However, we have more possibilities. To create a filter query you need three things:
Technical field name
Operator
Field value you want to check in your query
I created a table to give you a quick overview and a reference to which you always can come back:
Operator | Description | Example | Comments |
eq | equal to | tppd_orderstatus eq 1 | Can be used for all types Use "eq null" to check for blank fields |
ne | not equal to | tppd_orderstatus ne 1 | Can be used for all types Use "ne null" to check for not blank fields |
contains | contains | contains(tppd_name, 'order') | For text fields |
not contains | does not contain | not contains(tppd_name, 'order') | For text fields |
startswith | starts with the defined value | startswith(tppd_name,'Important') | For text fields |
endswith | ends with the defined value | endswith(tppd_name,'order') | For text fields |
substringof | substring in field | substringof('that', Title) | Does not work for Dataverse! Use contains instead |
gt | greater than | tppd_id gt 3; tppd_createdon gt ('05/07/2023 8:17') | For number and date fields |
lt | less than | tppd_id lt 3; tppd_createdon lt ('05/07/2023 8:17') | For number and date fields |
ge | greater than or equal | tppd_id ge 3; tppd_createdon ge ('05/07/2023 8:17') | For number and date fields |
le | less than or equal | tppd_id le 3; tppd_createdon le ('05/07/2023 8:17') | For number and date fields |
and | and | tppd_id le 3 and tppd_createdon le ('05/07/2023 8:17') | Combines different filters and checks if both are true |
or | or | tppd_id le 3 or tppd_createdon le ('05/07/2023 8:17') | Combines different filters and checks if at least one the filters is true |
I got asked if it is possible to you use the logic of brackets here as well.
Yes, of course! You could also use brackets and the "and/or" operator to advance your filtering even more.
There is one more thing to mention to ease your life with Power Automate and SharePoint even more. Therefore, you have to activate the experimental features for Power Automate.
Follow these steps to achieve this:
Click on the settings icon and then on "View all Power Automate Settings".
Afterwards you need to turn on the experimental features and click on save.
When you now go to the advanced options to filter your SharePoint list, you will see that the appearance of the Filter Query field has changed.
You can now select the field you want to compare and the operator directly from a drop down and do not need to write the filter query on your own.
You can always switch the appearance of the field by clicking the two blue arrows. After changing this you can see how your selected filter look like in the OData format.
One important thing to keep in mind is when you want to filter a lookup column in Dataverse you need to write it the following way: _publisher_technicalFieldName_value eq GUID.
To sum this article up, OData filter gives you the possibility to limit the records you get from list rows or get items actions. There are different operators you can use. Use the table above as a quick reminder whenever you need one.
Comments