cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yoshihirok
Level 10

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

11 REPLIES 11
mmangiante
Level 8

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

yoshihirok
Level 10

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

 

Community Support Team
Community Support Team

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.
yoshihirok
Level 10

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

 

 

 

 

 

Community Support Team
Community Support Team

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.
yoshihirok
Level 10

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
efialtes
Level 8

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

Grahambo05
Level 8

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.

yoshihirok
Level 10

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

 

 

 

 

AnotherMSprofil
Level: Powered On

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

BenFetters
Level 8

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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,901)