“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.
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 in Power Automate, 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?
Thank you for posting this. Do you have an example of how you apply this?
I have a SharePoint List(List A) that has list of websites. Each site has field that contains individual users for that site in a multiple line free text field (“Individual User Accounts”). I want flow to search that field for each ‘active’ site, based on a person’s name added in a separate SharePoint list(List B).
If that user list field contains the designated name from List B, then I want the name of each site from List A with that user to be included in table to be emailed. I assume I need to use a filter array for that. But, not sure how to plug that array into the whole flow.
Hello Mike,
if I understand your flow correctly, you should get all ‘active’ sites from List A, then get all users from List B, and then filter the results from List A (sites) using the List B items (users). The flow should be like:
– Get items (A): from ‘List A’, get the sites, filter only the ‘active’ ones
– Get items (B): from ‘List B’, get all the users
– Apply to each: input should be the list of users from ‘Get items (B)’, you want to process users one by one and find the sites for each of them
– – Filter array: filter the array of sites from ‘Get items (A)’ as shown in the article, filter only the sites where the ‘individual users field’ contains the currently processed user
– – Build the table and send an email to the currently processed user
I hope this helps even without a specific example,
Tom
Thank you Tom! That helps.
Really it help us.
Thanks
Awesome, Tom. Great idea and very helpful. Thank you!