Hello Readers
This blog is to help fellow consultants to start their journey on Power Automate. We all know how easy it is to create a flow (Watch #TGIF Episode 2 here, if not already).
I am sure as a Business user or a functional consultant, you must have had a situation where you needed someone technical to complete your flow. Most of this bottleneck is because as non-technical people we don’t know what ‘ODATA Query’ is?
Coming from Dynamics 365 background, I never required such filters for native workflows of D365. But here we are moving forward and learning together to be able to work with Flows.
This post will talk about the following two filter types you need while building a flow:
- ODATA filter query
- Filter array
Before we commence with the filters, i will try to explain you the components of ODATA filter query:
1.Field or Column Name | 2.Operator | 3.Field value you want to check/filter |
Sequence: In most queries the sequence of the components remains like ‘fieldname operator fieldvalue’ but in some cases like contains/does not contains sequence and structure changes to ‘operator(fieldname,’fieldvalue’)’
A few operators:
Operator | Description |
eq | Equal to |
ne | Not equal to |
contains | contains |
not contains | Does not contains |
gt | Greater than |
lt | Less than |
ge | Greater than or equal to |
le | Less than or equal to |
and | And |
or | Or |
startswith | Start with the specified value |
endswith | End with the specified value |
ODATA filter query
1.Contains for text fields
This one is for text fields like Topic, Subject, Phone, City, Street 1 etc.
Filter query= contains(textfieldschemaname,’value’)
e.g. if I have to check whether the ‘Subject/Topic’ of a Lead record contains ‘New’ in it; my filter would be contains(subject,’new’)
2. Does not contains for text fields
This one is for text fields like Topic, Subject, Phone, City, Street 1 etc.
Filter query= not contains(textfieldschemaname,’value’)
e.g. if I have to check that the ‘Subject/Topic’ of a Lead record does not contains ‘New’ in it; my filter would be not contains(subject,’new’)
3.Contains data (Is not blank)
This one is for both text and option set fields
Filter query= textfieldschemaname ne null
Filter query= optionsetfieldschemaname ne null
e.g. if I have to filter where ‘Job title’ contains data or is not blank; my filter would be jobtitle ne null
e.g. if I have to filter where ‘Rating’ contains data or is not blank; my filter would be leadqualitycode ne null
4. Does not contains data (Is blank)
This one is for both text and option set fields
Filter query= textfieldschemaname eq null
Filter query= optionsetfieldschemaname eq null
e.g. if I have to filter where ‘Job title’ does not contains data or is blank; my filter would be jobtitle eq null
e.g. if I have to filter where ‘Rating’ does not contains data or is blank; my filter would be leadqualitycode eq null
5.Contains for option sets
This one is for option set fields like Rating, Lead Source, Industry, Type etc.
Filter query= optionsetfieldschemaname eq optionsetnumericvalue
e.g. if I have to filter lead’s with rating ‘Hot’ (value =1); my filter would be leadqualitycode eq 1
Note: As per my understanding, you can’t check option set label in ODATA filter but you can in filter array.
6.Does not contains for option sets
This one is for option set fields like Rating, Lead Source, Industry, Type etc.
Filter query= optionsetfieldschemaname ne optionsetnumericvalue
e.g. if I have to filter lead’s with rating ‘Hot’ (value =1); my filter would be leadqualitycode ne 1
7.Contains with ‘OR’ on same field
Filter query= contains(field1name,’value1′) or contains(field1name,’value2′)
Filter query= optionsetfieldname1 eq optionsetnumericvalue1 or optionsetfieldname1 eq optionsetnumericvalue2
e.g. if I have to filter where ‘Job title’ contains ‘Manager’ or ‘Consultant’; my filter would be contains(jobtitle,’manager’) or contains(jobtitle,’consultant’)
e.g. if I have to filter where ‘Rating’ contains either ‘Hot’ or ‘Warm’ data; my filter would be leadqualitycode eq 1 or leadqualitycode eq 2
8. Contains with ‘AND’ on same text field
Filter query= contains(textfield1name,’value1′) and contains(textfield1name,’value2′)
e.g. if I have to filter where ‘Topic’ contains ‘New’ and ‘Interested’; my filter would be contains(subject,’new’) and contains(subject,’interested’)
9.Filter an option set checking two or more values
Filter query= optionsetfieldname1 eq optionsetnumericvalue1 or optionsetfieldname1 eq optionsetnumericvalue2
e.g. if I have to filter where ‘Rating’ contains either ‘Hot’ or ‘Warm’ data; my filter would be leadqualitycode eq 1 or leadqualitycode eq 2
10. Filter by checking two different option sets
Filter query= optionsetfieldname1 eq optionsetnumericvalue1 or optionsetfieldname2 eq optionsetnumericvalue2
e.g. if I have to filter leads where ‘Rating’ contains ‘Hot’ and ‘Lead Source’ contains ‘Advertisement’; my filter would be leadqualitycode eq 1 and leadsourcecode eq 1
11.Starts with/Begins with
This is for text fields only
Filter query=startswith(fieldname,’startvalue’)
e.g. if I have to filter all Australian leads , I will look at ‘Business Phone’ starts with country code +61; my filter would be startswith(telephone1,’+61′)
e.g. if I have to filter leads from Australia or New Zealand, I will look at ‘Business Phone’ starts with country code +61 or +64; my filter would be startswith(telephone1,’+61′) or startswith(telephone1,’+64′)
e.g. if I have to filter leads having ‘Business Phone’ from Australia but ‘Mobile Phone’ from New Zealand, I will look at ‘Business Phone’ starts with country code +61 and +64; my filter would be startswith(telephone1,’+61′) and startswith(mobilephone,’+64′)
12.Ends with
This is for text fields only
Filter query=endswith(fieldname,’endvalue’)
e.g. if I have to filter all leads where ‘Website’ ends with ‘.org’; my filter would be endswith(websiteurl,’org’)
e.g. if I have to filter all leads where ‘Website’ either ends with ‘.org’ or ‘.com’; my filter would be endswith(websiteurl,’org’) or endswith(websiteurl,’com’)
e.g. if I have to filter all leads where ‘Website’ ends with ‘.org’ and email ends with ‘.com’; my filter would be endswith(websiteurl,’org’) and endswith(emailaddress1,’com’)
13.Greater than
This is for Numbers and date fields only
Filter query=datefield gt ‘specificdate’
Filter query=datetimefield gt ‘specificdatetime’
Filter query=numberfield gt specificnumber (No, ” here)
e.g. if I have to filter leads created after 10th August 2019
createdon gt ’10/08/2019′
e.g. if I have to filter leads created after 5AM on 10th August 2019; my filter would be
createdon gt ’10/08/2019 05:00′
e.g. if I have to filter leads created after 5:30AM on 10th August 2019; my filter would be
createdon gt ’10/08/2019 05:30′
e.g. if I have to filter leads created after 5PM on 10th August 2019; my filter would be
createdon gt ’10/08/2019 17:00′
e.g.if I have to filter leads created after 5:30PM on 10th August 2019; my filter would be
createdon gt ’10/08/2019 17:30′
e.g. if I have to filter leads where annual revenue is more than $2000000
revenue gt 2000000
e.g. if I have to filter leads where annual revenue is more than $2000000 and number of employees is more than 500
revenue gt 2000000 and numberofemployees gt 500
14.Less than
This is for Numbers and date fields only
Filter query=datefield lt ‘specificdate’
Filter query=datetimefield lt ‘specificdatetime’
Filter query=numberfield lt specificnumber (No, ” here)
e.g. if I have to filter leads created before 10th August 2019
createdon lt ’10/08/2019′
e.g. if I have to filter leads created before 5AM on 10th August 2019; my filter would be
createdon lt ’10/08/2019 05:00′
e.g. if I have to filter leads created before 5:30AM on 10th August 2019; my filter would be
createdon lt ’10/08/2019 05:30′
e.g. if I have to filter leads created before 5PM on 10th August 2019; my filter would be
createdon lt ’10/08/2019 17:00′
e.g.if I have to filter leads created before 5:30PM on 10th August 2019; my filter would be
createdon lt ’10/08/2019 17:30′
e.g. if I have to filter leads where annual revenue is less than $2000000
revenue lt 2000000
e.g. if I have to filter leads where annual revenue is less than $2000000 and number of employees is less than 500
revenue lt 2000000 and numberofemployees lt 500
15.Less than or equal to and Greater than or equal to
This is for Numbers and date fields only
Filter query=datefield ge ‘specificdate’
Filter query=datetimefield ge ‘specificdatetime’
Filter query=numberfield ge specificnumber (No, ” here)
Filter query=datefield lt ‘specificdate’
Filter query=datetimefield le ‘specificdatetime’
Filter query=numberfield le specificnumber (No, ” here)
e.g. if I have to filter leads created after or on 10th August 2019
createdon ge ’10/08/2019′
e.g. if I have to filter leads created after or at 5AM on 10th August 2019; my filter would be
createdon ge ’10/08/2019 05:00′
e.g. if I have to filter leads created after or at 5:30AM on 10th August 2019; my filter would be
createdon ge ’10/08/2019 05:30′
e.g. if I have to filter leads created after or at 5PM on 10th August 2019; my filter would be
createdon ge ’10/08/2019 17:00′
e.g.if I have to filter leads created after or at 5:30PM on 10th August 2019; my filter would be
createdon ge ’10/08/2019 17:30′
e.g. if I have to filter leads where annual revenue is more than or equal to $2000000
revenue ge 2000000
e.g. if I have to filter leads where annual revenue is more than or equal to $2000000 and number of employees is more than or equal to 500
revenue ge 2000000 and numberofemployees ge 500
e.g. if I have to filter leads created before or on 10th August 2019
createdon le ’10/08/2019′
e.g. if I have to filter leads created before or at 5AM on 10th August 2019; my filter would be
createdon le ’10/08/2019 05:00′
e.g. if I have to filter leads created before or at 5:30AM on 10th August 2019; my filter would be
createdon le ’10/08/2019 05:30′
e.g. if I have to filter leads created before or at 5PM on 10th August 2019; my filter would be
createdon ge ’10/08/2019 17:00′
e.g.if I have to filter leads created before or at 5:30PM on 10th August 2019; my filter would be
createdon ge ’10/08/2019 17:30′
e.g. if I have to filter leads where annual revenue is less than or equal to $2000000
revenue ge 2000000
e.g. if I have to filter leads where annual revenue is less than or equal to $2000000 and number of employees is less than or equal to 500
revenue le ‘2000000’ and numberofemployees le 500
e.g. if I have to filter leads where annual revenue is less than or equal to $2000000 and number of employees is more than or equal to 500
revenue le ‘2000000’ and numberofemployees ge 500
Filter array
These are very much similar to what we get in D365 native workflows except for puttin the value ourselves.
1.Option set label
Select the label field dynamically and not the value field. Then specify your label value on the right.
2. Option set value
Select the value field dynamically and not the label field. Then specify your option set value on the right.
3. Text fields
This one is for text fields like Topic, Subject, Phone, City, Street 1 etc.
4. Number and date fields
This is for number and date fields.
Those are enough filters to get you started. 🙂
Hope you find this helpful!
Subscribe to my YouTube
Thanks!
Let’s keep sharing!
How about filtering a related entity field? In classic mode its so easy but I cannot find an example for Power Automate when I search.
LikeLike
Hi CRM Question,
That’s a good question; you can filter related records by using the compose filter function. I have talked about it in the video of this blog https://diyd365.com/2019/09/13/how-to-update-an-opportunity-from-opportunity-close-and-send-an-email-on-opportunity-close-using-ms-flows-no-code-solution/
Have a look and if it doesn’t resolve your issue, do let me know and I ll provide you some screenshots.
Thanks!
LikeLike
You could apply a FetchXML query. However, this is only possible by using the List records action of the “Common Data Service (current environment)” Connector. Make sure to use the “(current environment)” Connector, not the “standard” one because FetchXML is only possible with the more powerful “(current environment)” Connector.
I suggest using either the FetchXML Builder of the Xrm Toolbox, or you can simply generate a regular Advanced Find in Dynamics 365 and download the FetchXML File (if you go with the latter, make sure to remove any “-” at the beginning of a row). Furthermore, any attributes you want to use in subsequent action steps need to be included in your FetchXML query within the “attributes section” where you specify the attributes included in the Fetch.
Hope this helps
LikeLike
Dear Florian
Here’s your comment and visible to everyone. I focus on questions more just so that you know. I can only answer what I know and i can only share what I know, please understand that. If you think this blog is not worth it ( as you have used pretty bad words, I don’t want to make it public for your reputation) please don’t visit this blog or my YouTube channel. I would highly recommend dedicating your time to the community by sharing your knowledge on the right platform. And I accept that you know so much more than I ever could. Hope that makes you feel better, please join the community here and try helping so many people who are looking for advice as compared to that my blog is tiny. Here’s a link: https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/
LikeLike
Also please note that my blog is not the community. People like you and me make the community together. So please channelise your knowledge in the right direction. You’re not my audience, so any comments from you (good or bad) now onwards will be censored. My blog is not for you and my channel is not for you.
Thank you for stalking though.
LikeLike
How about a situation where I want to select specific rows, for instance:
ID eq 2349 and 2372
where 2349 and 2372 are two row numbers or ID numbers so I can return both rows
LikeLike
See point 8
LikeLike
Thanks, but it seems this function or syntax is not supported to Get items in a SharePoint Online list in the Filter Query box
LikeLike
Let me check and get back to you with an answer soon.
LikeLike
I discovered the solution as:
ID eq 2349 or ID eq 2372
Thanks for your support, subscribed!
LikeLike
Of course, coz both id can’t be 2349 and 2372 together so u would use OR instead of AND. Point no. 8 and 9 shows you such examples.
Glad that your issue is resolved. Thanks for subscribing. 😊
LikeLike
Hi
Your help please
How to make a filter to return the maximum or highest value of a column
LikeLike
Order by descending (syntax is, without quotes: “ColumnName desc”), then set the Top count to 1. See more on Order by syntax in this help article: https://powerusers.microsoft.com/t5/Building-Flows/Sytnax-for-Odata-Order-By/td-p/55185
LikeLike
Hi Prashant! I am trying to filter a sharepoint list based on a Status column (Choice type), field name in url is Status and filtering for Review Needed. I know I can’t use those terms directly but I am stuck on what can be used instead. Any help is appreciated!
LikeLike
Continuation of last message – And I am not sure how to find the optionsetfieldschemaname and optionsetnumericvalue. Thank you so much again!
LikeLike
https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/I-want-using-Option-Set-field-choose-label-value/td-p/162486
LikeLike
A good one to add to this would be for using Two Option Set values in the filter expression. You cannot use “booleanfield eq 1”. You have to use “booleanfield eq true”. Otherwise you get the following error: A binary operator with incompatible types was detected. Found operand types ‘Edm.Boolean’ and ‘Edm.Int32’ for operator kind ‘Equal’.
LikeLike
Hi Prashant,
Tutorial is really good. However, when I use following query – xyz le ’10’ flow fails with error – Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.
My question is, will ms flow only support above filter operations with excel and not all the filters suggested in the article ?
Note, that I am new to whole power automate thing and trying to create a report which filters rows and sends and html table as email based on count number. I am using excel.
Any help is highly appreciated.
Chinmay
LikeLike
Hi Chinmay
Have you tried removing the quotes?
xyz le 10
LikeLike
hi,
We have a “Get Items” step, and we’re having trouble building the ODATA Filter Query.
Essentially we need an If expression if it’s possible, so if the ShopType field value = “All Shops” then no filter is applied, but if there’s any other value we have to use it like below:
Fascia eq @{triggerBody()?[‘ShopType’]?[‘Value’]}
is that possible
LikeLike
Hi Philip
Is there a reason u can’t use
ShopType ne ‘All Shops’
?
LikeLike
Thanks a lot ! Really helpful article.
LikeLike
Thanks a lot. Really helpful
LikeLike
What if I want to filter OData like… startswith(status, ”). I want to get all results in this case but this isn’t working. Can you help me out? startswith(status, ‘complete’) works but startswith(status, ”) doesnt.
LikeLike
perhaps a question that is too basic….if my field name is two words, do I use single or double quotes or none at all.
In your examples you have Annual Revenue. How would that be formatted?
LikeLike
Such helpful information! Any thoughts on how to filter query the results of an HTTP request that are currently stored as a Compose function? I get the dreaded “the expression is not valid” error. I know the syntax is correct if I use a field value, but it doesn’t work using the Compose value. Store as a variable? For reference, I’m evaluating the Title field of a list item against the previous value, and if it is the same, I look up the matching document set, and that works. But for the other half of the condition, I’m trying to look up the document set with the previous value, and that’s what fails. Any thoughts are appreciated. Now I’m off to scour the rest of your blog for cool ideas and information.
LikeLike
Hi
This page has been brilliant and helped me loads. However I have a problem with the ‘ (apostrophe) character in a string.
I’m trying to filter on a string column which is the email address of new starters, these have not yet been added to our system, so this is a string column.
My filter is EmailAddress eq {x}Email (where {x}Email is a variable in my flow and EmailAddress is a column in my list)
This works great for email addresses like firstname.lastname@whereever.com but…
We now have someone called firstname.o’lastname. The flow takes the ‘ as closing the string expression…
Example: EmailAddress eq ‘FirstName.O’lastname@whereever.com’
Is there a way around this. I’ve tried to replace the ‘ with ” but the replace function is PA uses ‘ as the text enclosure.
I’m a little bit lost
LikeLike
I am facing the same issue till date. Will reply here if I get a work around. Apologies.
LikeLike
Is there any way to filter by cell color? Say in a list?
LikeLike