Power Automate filter on Multiple lines of text SharePoint column

“Since I changed the column to “Multiple lines of text”, it appears that I can no longer use “substring” to filter on that column in Power Automate.”

“The field ‘MultipleLoT’ of type ‘Note’ cannot be used in the query filter expression.”


‘Multiple lines of text’ is one of the columns that doesn’t support filtering. It’s not possible in SharePoint, and it’s the same also in Power Automate. The ‘Get items’ (or ‘Get files’) actions will return an error message that the filed of type ‘Note’ can’t be used. How can you get around this error (and manage to filter)?

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

Add ‘Filter array’ action(s) instead

Power Automate has an action called ‘Filter Array’. And it does what the name suggests, it’ll take an array and filter the results. So, instead of using a filter directly in the ‘Get items’ action, let it get all the items. You can filter them later using the ‘Filter array’ action. Actually, it might be safer to use two of them.

Filter ‘null’ values

In case you ‘Multiple lines of text’ column is mandatory, you can skip this step. But if it’s optional, and it’s possible it might be empty, you’ll have to remove the items where it’s empty.

Use the ‘value’ dynamic content from the ‘Get items’ action as the ‘From’ array. That’s the array with ALL items in the list. The condition should filter only items where the ‘column is not equal to null’ (null is an expression). Having only items with values, you can add the second ‘Filter array’.

Power Automate filter empty values from array

Filter the actual string

The second ‘Filter array’ action will finally filter by the values in the ‘Multiple lines of text’ column.

Start with the ‘Body’ output from the previous ‘Filter array’ action as the ‘From’ array. For the condition, you’ll need to reference the ‘MultipleLoT’ column manually as the dynamic content might not be available. Use an expression as below to reference the column by its internal name, select ‘contains’ operator and put the string to search for on the right side. All items returned by the second ‘Filter array’ will contain that string.

e.g
item()?['MultipleLoT']

Note:
replace 'MultipleLoT' with the column internal name

Use items from the array

The last step is to add ‘Apply to each’ action with the ‘Body’ output from the second ‘Filter array’. In this loop, you can access any column by using its internal name (‘dynamic content’ is not available) and do whatever you need with it.

e.g.
@items('Apply_to_each_4')['MultipleLoT']

Note:
make sure the 'Apply_to_each' part corresponds to the 'Apply_to_each' where you're processing the 'Body'. On the screenshot below it is 'Apply_to_each_4'.

Update: or you can use ‘Parse JSON’ action to get access to the dynamic content data as described in a newer post.

Summary

As you can see, it’s possible to filter on ‘Multiple lines of text’ column, but it’s not that easy. You must get ALL items from your list. Then you’ll filter them twice to avoid errors in your flow – ‘contains’ operation will fail on empty column. And using this double filtering on SharePoint items/files you’ll loose the dynamic content, you’ll need to reference the columns manually.

Therefore, the first question I’d ask before going this way is: do I really need to filter on Multiple lines of text column? Can I use different column type for the filter? Wouldn’t 255 characters of the ‘Single line of text’ column be enough?


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

3 Comments

Add a Comment

Your email address will not be published. Required fields are marked *