ODATA filter query help needed

[Anonymous]

I'm using a SharePoint Get Items action in my flow, and I can't seem to get SharePoint to accept the ODATA filter query I'm using. I'm sure it's a minor syntax error I'm making, but I can't find the right form. I tried the syntax shown in the OData filter reference, but it isn't working. I want the action to return all list items that include the (example) string "program1" in the column "Rights JSON." That column is of the type "Multiple lines of text." My query looks like this...

contains(Rights_x0020_JSON,'program1')

And the error I'm receiving is, "The function operator 'contains' is not supported or its usage is invalid." Can anyone point out where I'm going wrong here?

[efialttes]

Hi!

Try with substringof()

Please read carefully how to use it (pattern first, column name next). Great stuff here

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

Hope this helps

[Anonymous]

The substringof() function was accepted, but SharePoint rejected the query, because the Rights_x0020_JSON column is of type "Note" (what SharePoint calls a multi-line text field, apparently). The contents of the Rights JSON column for some items is longer than the maximum 255 characters for a single-line text field, so that's not an option. Are there other OData functions I could use to filter on a string within a multi-line text column? The only other way I can think of would be to have the action return all the items, and then filter them manually with a for-each loop. This poses its own problems, though, since the Get Items action will return a maximum of 500 items.

If there aren't alternative functions, then I might need to rethink the design of this SharePoint list.

[efialttes · Accepted solution]

Hi again!

Did you considered  getting all items, then assign its output as the input for 'Filter array' action block instead? Better performance than 'Apply to each'

Hope this helps

[efialttes · Accepted solution]

Hi again!

"...Get Items action will return a maximum of 500 items..."

You should be able to increase 'GEt items' response limits over 5000 by activating pagination.

Great stuff here:

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

Hope this saves your day

[Anonymous]

@efialttes 

Thanks! I had it pull the whole SharePoint list, and then ran it through an array filter, which (like you said) was much faster than looping through each entry. I'll have to keep working on the pagination part to get all the items in the list, but I just need to read more about how to do that. The tricky part (filtering on a string within a JSON string) has been solved. 😀