cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Helper V
Helper V

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

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

Highlighted
Memorable Member
Memorable Member

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

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

 

Highlighted
Community Support
Community Support

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

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.
Highlighted
Memorable Member
Memorable Member

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

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

 

 

 

 

 

Highlighted
Community Support
Community Support

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

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.
Highlighted
Memorable Member
Memorable Member

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

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

 

 

 

 

 

Highlighted
Kudo Kingpin
Kudo Kingpin

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

@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

Highlighted
Advocate IV
Advocate IV

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

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

Highlighted
Memorable Member
Memorable Member

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

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

 

 

 

 

Highlighted
Advocate III
Advocate III

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

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

Highlighted
Continued Contributor
Continued Contributor

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

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
Highlighted
New Member

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

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. 

Highlighted
Frequent Visitor

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

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
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new 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!

Users online (6,498)