Negative Filter

OData Filters

Yesterday I was asked about OData filters in Microsoft Flow. In the past I wrote a post about OData filters.  When you try to query with the startswith function  it is possible to get all the items in a SharePoint that have a title that start with ‘Get’.

Possitive OData Filter

Negative queries

But sometimes we need to run a negative query and we want all the items that don’t start with Get.

First I thought about the post by Pentalogic where a view is created based on a calculated field.

Create Calculated column in SharePoint

I created my view based on the calculated field.


Negataive filter on the calculated field

Without configuring a Filter query in Flow you can use the view created to configure which fields Flow will look at, however when running the Get Items action the view is NOT used to filter the data. This is correct behaviour as the Flow Designer tells you that you can use the view to select the fields used. It would be nice though if we could use views to filter the data.

Still see data

I configured my Flow OData Filter query

Negative filter

Although this works in SharePoint views in OData filter queries in Flow this doesn’t work.

Doesn't work in OData Filters

Time to create a normal text field. I called this IsGetNotCalculated, but you can call this anything you like of course.

Create normal field

Then I filter items using the IsGetNotCalculated field and my query is happy.

Filter is working

Now there is just one challenge left, the IsGetNotCalculated  needs to be set. You could do this with a Flow that runs every time an item is created or modified. Ok, I agree it isn’t great having to update the items just to run a query, but at the moment this is the only option that I have found that actually works.  Please feel free to find better options and post them in the comments below.

By Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using

4 thoughts on “Negative queries with OData in Microsoft Flow”
  1. An Alternative would be to use ‘FIlter Array’ action in flow to do the filtering instead. Although it is quite messy inside flow, it avoid updating items in sharepoint.

    1. Hi Ariel,

      That would be an option, however for large lists that might cause other troubles as you would pull down the whole list.

      For smaller lists I do like the idea.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.