Negative queries with OData in Microsoft Flow

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’.

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.

I created my view based 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.

I configured my Flow OData Filter query

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

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

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

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.