Power Automate filter on Multiple lines of text SharePoint column
Posted On December 16, 2020
“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)?
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’.
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.
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.
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'.
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?