cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Memorable Member
Memorable Member

Are there example of "Filter Query" of SharePoint service - Get Items ?

I want to use "Filter Query" of SharePoint services - Get Items,

to get specific items from my SharePoint Online - List.

 

Are there any document or example of "Filer Query" of SharePoint services ?

1.png

 

 

 

Regards,

Yoshihiro Kawabata

13 REPLIES 13
Helper V
Helper V

Hello @yoshihirok

 

great question that interested me in the past..do you this is a good starting point https://msdn.microsoft.com/en-us/library/hh169248(v=nav.90).aspx ?

 

I'd like to find other people that found documentation on this. I think this is a topic, with also Workflow Defination Language, that Microsoft may document more, with more sample.

 

--

Regards,

 

Marco Mangiante

Hi @mmangiante, thank you for the document of OData URI

 

and How can I set Filter Query parameter ?

"Name eq 'Bill'"  or other ? for get Name column Bill item

 

My flows are running so long time,

or completed soon but returning nothing.

 

Regards,

Yoshihiro Kawabata

 

Hi @yoshihirok,

 

You may take a reference of the following ODara filter query for SharePoint REST Service:

Use OData query operations in SharePoint REST requests

 

I tried the following:

Title eq 'Hellow'

Under the Filter query, which works from my side.

59.PNG60.PNG

61.PNG

Just type the expression directly, and for text value single quoted.

Try it again and see if it would work.

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.

Hi @v-micsh-msft, thank you for your reply.

 

My flow are running over 20 second, and error.

 

Filter: Name eq 'Bill'

Target List have one row with a column Name = Bill.

 

1.png

 

Outputs - Body.

 {
  "status": 502,
  "message": "列 'Name' が存在しません。他のユーザーが削除した可能性があります。",
  "source": "https://****.sharepoint.com/****/_api/SP.APIHubConnector.GetListItems(listName='be9dc4b6-ebbd-4074-a66c-20639b46b921',queryOptions='%2524filter%3dName%2520eq%2520%2527Bill%2527')",
  "errors": [
    "-2146232832",
    "Microsoft.SharePoint.SPException"
  ]
}

 

 

-- **** mask real site/list name.

 

Regards,

Yoshihiro Kawabata

 

 

 

 

 

Hi @yoshihirok,

 

Would you please share about the SharePoint strucutre here?

The filter query would only work with existing column, and its corresponding value.

For example, we have a Title column, then under the filter query, we would like to query the item where the Title column field equals to 'Hellow', then we could add the 

Title eq 'Hellow' 

in the filter query part.

The error message you posted should mean:

"Column 'Name' does not exist, possibly deleted by another user.

If the 'Name' is not a column name in your SharePoint list, then the query will not work.

 

Further, if you would like to select which column from SharePoint would show up under Microsoft Flow, currently this is not available, as it uses the $Select , which didn't pre-defined under the Get-Items Action.

The Filter Query in the Get-item action equals to $filter, more information, please check the MSDN articel I shared in my first post.

See my another posts for reference in the following thread:

Filter SharePoint list Data using ODATA

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.

Hi @v-micsh-msft

 

I share the List setting screenshot.

 

The "Name" column exist,

which rename from "タイトル".

-- "タイトル" is "Title" in Japanese, and default column name.

1.pngand, Test List "Names" have "Name" column.

 

Regards,

Yoshihiro Kawabata

 

 

 

 

 

@yoshihirok Have you tried to run the flow with no ODATA filter in GetItems action; and then verify in the outputs if SP Column Internal Name is the same you selected when configuring the SP List Columns? 

From my personal experiences, sometimes the Internal Column Name retrieved by Microsoft Flow connector does not exactly match the Column Name you configured on SP.

Hope this helps

@yoshihirok

 

What I typically do to find the correct value for the column name is create a fake condition under the Get Items action in the flow, and select edit in advanced mode.

Hi @Grahambo05 , thank for your reply and information.

 

Yes, for getting exact column name is useful the condition action with advanced mode.

and Now,

I'm using new way for it. 

That is using Dynamic content tab.

 

Step 1. Select 'Expression' tab.

Step 2. Select expression like if ()

Step 3. Move cursor for inserting your column name, like in ()

Step 4. Select 'Dynamic content' tab.1.png

 

 

 

Step 5. Select content which you want to use, like 'Name'

After that, fx became column name,

like 'if(triggerBody()?['UserDetails']?['FullName'])'.

 

This way is easy.

 

Regards,

Yoshihiro Kawabata

 

 

 

 

I've run into the same problem. The File-related columns such as Name, Path, Size etc exist in the form of "{Name}","{Path}","{Size}" - e.g. in curly brackets. The problem is when you are trying to address them like that in the quesry, you get a 400 "Bad request". No idea why MS is doing it and how to work around it.

 

I created a post on this here:  https://powerusers.microsoft.com/t5/Connecting-To-Data/SharePoint-quot-Get-Files-Porperties-Only-quo...

Continued Contributor
Continued Contributor

Hello,

 

I agree it would be pretty nice to have some more documentation on this. I'm wondering as well how to add more than just one filter? 

 

One thing I've done to sort of get around this problem has been to "Limit Columns by View" and I just create a view with the filtering that I want and that seems to do the trick. 

 

Thanks everyone! Hope something in here helps.

If this solved the problem for you, feel free to hit "Accept as Solution" so others can find the answer easier. 🙂

Kind regards,

Ben Fetters
Power Apps/Power Automate Developer
www.sovereignsp.com
Anonymous
Not applicable

review your data of previus run, there are the correct names of your columns, sometimes the name of a column in a flow that you not created, hava differente names. 

Hi,

 

I was having the same issue and I just googled my problem and got a link to your thread...

 

I think I've found a way to solve the problem.

 

Run your flow without the filter. This would cause 'Get items' to run on your entire list and from the results, check what the column name is.

 

I found out I had a column called "User" in my sharepoint list, but the result showed it as "Title".

 

This might help.

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (6,132)