Unanswered

ODATA filter query help needed

(0) Share

Report

Posted on by Community Power Pla...

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?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (2)

efialttes 14,756 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on at

Like (2)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@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. 😀

Was this reply helpful? Yes No