cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenDaneliuk
Frequent Visitor

Filtering Sharpoint List Data using ODATA

Hi All,

 

I'm trying to get a value from a Sharepoint List and looking to filter down the data set using ODATA filters.

This does'nt seem to be as intuitive as the rest of the flow structure.

 

I'm getting an error stating the expression is not valid. I can only presume that my query ont he column name is wrong as the data value i'm testing for is correct.

 

Can anyone help?

 

Header:

{ "Pragma": "no-cache", "x-ms-request-id": "f3c67e80-745d-4555-a956-23b76cdc2749", "Cache-Control": "no-cache", "Date": "Thu, 13 Apr 2017 16:17:25 GMT", "Set-Cookie": "ARRAffinity=6f87b92787b7f3b45b17b2f525a670883ff8d34bcd24b2d0eecfa033b8e3e94f;Path=/;Domain=127.0.0.1", "Server": "Microsoft-IIS/8.0,Microsoft-HTTPAPI/2.0", "X-AspNet-Version": "4.0.30319", "X-Powered-By": "ASP.NET", "Content-Length": "369", "Content-Type": "application/json; charset=utf-8", "Expires": "-1" }
 
Body:{ "status": 400, "message": "The expression \"Employee Eq Ben Daneliuk\" is not valid.", "source": "<siteURL>_api/SP.APIHubConnector.GetListItems(listName='37197a96-dbd6-4a03-923a-2b418ff43cad',queryOptions='%2524filter%3dEmployee%25C2%25A0Eq%2520Ben%2520Daneliuk')", "errors": [ "-1", "Microsoft.SharePoint.Client.InvalidClientQueryException" ] }

List Settings.pngFlow Error.png

 

7 REPLIES 7
Highlighted
Community Support
Community Support

Re: Filtering Sharpoint List Data using ODATA

Hi @BenDaneliuk,

 

Would it be available to filter the SharePoint lists with Condition under Microsoft Flow?

Add a condition to a flow

 

Currently OData filters are not implemented yet by all connectors.

See the response from SameerCh in the following thread:

ODATA Filters - are these implemented yet?

 

 

But the filter query works for SharePoint lists.

53.PNG

Currentlly the Filter Query under the Action Act as the 

$filter in OData Query,

The Order By act as the:

$orderby

There is no $select query implemented under the Action.

So if you would like to return special fields from the SharePoint list (See OData reference for SharePoint REST query), you may consider submit this as an idea, to add the $Select as an available Query under the Get Items Action.

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Regards,

Michael

 

 

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BenDaneliuk
Frequent Visitor

Re: Filtering Sharpoint List Data using ODATA

Thanks Michael,

 

Was hoping to keep the control all within flow rather than drop out into SP designer to develop the REST commands.

Understand the ODATA filters aren't working for all connectors. Thanks to the info.

 

I'll have a dabble with the conditions,  but may have to bite the bullet and use the SP designer to create this work flow.

 

Thanks

 

Ben

Re: Filtering Sharpoint List Data using ODATA

Curious if you have gotten this to work.  I have tried every permutation imaginable and have not been able to get this to work

 

Field Name eq 2 Part Value

Field Name eq 2%20Part%20Value

Field Name eq "2%20Part%20Value"

Field Name eq '2%20Part%20Value'

Field Name eq "2 Part Value"

Field Name eq '2 Part Value'

Field_x0020__Name eq 2 Part Value

Field_x0020__Name eq "2 Part Value"

Field_x0020__Name eq '2 Part Value'

Field_x0020__Name eq 2%20Part%20Value

Field_x0020__Name eq "2%20Part%20Value"

Field_x0020__Name eq '2%20Part%20Value'

 

Every single one has been tried, none of them work, so do Odata filters really work?

Community Support
Community Support

Re: Filtering Sharpoint List Data using ODATA

Hi @WiggityZwiggity,

 

Yes, it works.

Please add a condition, put the corresponding field dynamic content into the first Input Box, then click advanced mode, then you should be able to find the proper field identifier:

73.PNG

The field name here is Testing Text, while the workable column field is marked as 'haiz'

So if we would like to take use of this column to filter query, the formula should be:

 

haiz eq 'TextValue'

 

For the value part, it is the actual text, no need to replace the Space with other charactor.

Regards,

Michael

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Filtering Sharpoint List Data using ODATA

Hi @BenDaneliuk and @WiggityZwiggity

 

I use in most of my flows the GetItens action in SharePoint and this realy works!

 

Somethimes i and my team wrong the rest filter and the solution for us if before put in flow test in browser using the following url

https://SHAREPOINT SITE URL/_api/web/lists/getByTitle('LIST NAME')/items?$filter=FILTER

 

if the result in browser is ok, tou can put the FILTER inside the field in MSFlow and see the magic 🙂

 

A MSDN page can help you if u need about using rest in SharePoint

 

Regards,

 

Gustavo Moraes


Did I answer your question? Mark my post as a solution!

Gustavo Moraes, o astronauta brasileiro do Flow!


satya Helper II
Helper II

Re: Filtering Sharpoint List Data using ODATA

how to compare persons or group fields in OData Query.  i am getting the error as below.

 

 "status"400,
  "message""The expression \"DelegationFrom eq i:0#.f|membership|venkat@infyevonik.onmicrosoft.com\" is not valid.\r\nclientRequestId: 638efdb5-b91a-4009-8cfd-20a0c3beec48\r\nserviceRequestId: 6127959e-d0b6-0000-128e-3c5f386ffa78"

CrispExcel
Frequent Visitor

Re: Filtering Sharpoint List Data using ODATA

Hi,

Am Facing the same issue.

Does anyone know how to resolve this?

It was working before

"status": 400,
"message": "The expression \"ID eq https://base365.sharepoint.com/sites/BasePowerApps/Lists/Customer\" is not valid.\r\nclientRequestId: f5922589-d2dc-4165-8438-e8c47865e1bc\r\nserviceRequestId: f5922589-d2dc-4165-8438-e8c47865e1bc"
 
 

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Users online (7,065)