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

[yoshihirok]

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 ?

Regards,

Yoshihiro Kawabata

[mmangiante]

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]

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

[v-micsh-msft]

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.

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

Try it again and see if it would work.

Regards,

Michael

[yoshihirok]

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.

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

[v-micsh-msft]

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

[yoshihirok]

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.

and, Test List "Names" have "Name" column.

Regards,

Yoshihiro Kawabata

[efialtes]

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

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

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.

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]

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]

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.

[Anonymous]

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. 

[Anonymous]

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.