Power Automate – SharePoint Get Items Filter Query on Notes field

The Get Items action in Power Automate can be used for retrieving items from a specific list. If you want to have specific items from that list, you can use the Filter Query option to filter on certain fields. But if you want to use that on a Multiple lines of text / Notes field, you will get the following error message:

The field 'NoteField' of type 'Note' cannot be used in the query filter expression.

Luckily, there is a way to work around this, which I will describe in this blogpost.

Filter Query

First, let’s dive a bit deeper into the Filter Query option. I’m not going into full details because there is a lot to write about. If you do want extra information about this, please reach out and I will be happy to assist you.

As mentioned before, you can use it to return only items that satisfy specific conditions (e.g. show only requests from the Sales department). You can do this by entering the following filter query into the Filter Query textbox (which is hidden beneath Advanced options):

Department eq 'Sales'

Running this, will provide you with all items from the Multiline filtering demo list in SharePoint where ‘Sales’ is filled inside the Department field (which is a choice field by the way).

Now why would you do this instead of just pulling everything from this list?

The answer is simple;

First of all, the Get Items action will only return a maximum of 5000 items (configurable through the Top Count option). So if your list has more then 5000 items, the chances are you will not get every item back from your flow.

Second; if you’re planning on performing actions on each item in that list (using an Apply to each loop), your flow’s performance will increase if you’ve set a query and you will use less API calls which will benefit your action call limit.

Operators

You can filter on many field types (except for the Notes field, which will be addressed later). For this, you may need different operators (e.g. eq for equals or lt for less than).

Because you’re querying SharePoint, you’re bound to the SharePoint OData query operators. The full list of operators can be found here.

Combining filters

You can also combine different filters by using the and, or operators (e.g. Department eq ‘Sales’ and Amount lt 1000).

Multiple lines of Text / Notes

So, back to the beginning; how can I filter on a Multiple lines of text / Notes field if the Get Items action does not support it?

Well, basically it’s SharePoint that doesn’t support it. If you query against the SharePoint API, you will get the exact same error message:

_api/lists/getbytitle('ListName')/items?$select=NoteField&$filter=NoteField eq 'This is a test'

So if it’s not Power Automate that’s blocking you from filtering, there must be another way to do this.

Filter array action

The outcome to this problem is to use the Filter array action. You can use this action to filter the outcome of your unfiltered Get items action. It should be configured as follows:

  • From is the array you want to filter, which is the value outcome of your Get items action. This can be selected from the Dynamic Content.
  • The left operand is the field you want to filter against. The Dynamic Content should have all fields from your SharePoint list.
  • The middle operand is the operator you want to use, which is a simple dropdown. So you don’t have to use the operators I mentioned earlier.
  • The right operand is the value you want your field to be filtered on.

In my case, I want to get all items which have ‘test’ inside the Request field (which is my Multiple lines of text field):

When I run the flow now, you can see that it will filter without any error:

Please note that this is against the advice to query all items from within your SharePoint list and it may affect the overall performance. Power Automate will also want you about this when you run your flow:

But in some cases you just can’t help it.

Leave a Reply

Your email address will not be published.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

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