cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TechShed
Level: Powered On

OData Filter Query on SharePoint Lookup Site Column

I have successfuly used the OData Filter Query on the Get Items action for SharePoint but the following scenario does not work:

Country eq 'Australia'

Country is a Lookup Site Column on my list.

When I remove the Filter Query I can see the following JSON returned for the Country Site Column:

[
{
"@odata.etag": "\"1\"",
"ItemInternalId": "1",
"Title": "Test 1",
"Country": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 3,
"Value": "Australia"
},
"Country#Id": 3,
"Modified": "2016-07-13T03:38:40Z",
"ID": 1,
...

 

What is the correct format in the Filter Query to compare the Value of the Country Site Column?

Tried the following:

Country/Value eq 'Australia'

 

I read about having to use the Expand option for the site column to access the Id and Value properties but the Get Items action does not allow for the Expand property.

 

Thanks.

6 REPLIES 6
Community Support Team
Community Support Team

Re: OData Filter Query on SharePoint Lookup Site Column

Hi TechShed,

 

I am trying to find some related documentations or blogs about how to configure ODATA Filter Query in Flow. I will update on this thread once I got related articles.

 

As a workaround, please consider to add a Condition in your flow to filter items according to your needs.


You could configure the Condition in basic mode or advanced mode, and also you could combine Workflow Definition Language in the flow.


For example, you could configure the condition as Country is equal to “Australia”, then add actions after IF YES or IF No branch.
3.PNG

 

Please try it this will meet your needs. Please feel free reply if you need more help. I am working on finding some related docs.

 

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TechShed
Level: Powered On

Re: OData Filter Query on SharePoint Lookup Site Column

2017-05-22_165143.png

The same SharePoint Get Items action works in a Logic App because I am able to go into Code view and add the 'expand' property for my Lookup site column and then adjust my filter to refer to the Region/Title and compare it against my output from another variable.

Either allow access to Code view for all Flows or add another Textbox to expose the expand property on this action would resolve thsi issue.

I use Code view all the time in Logic Apps to build and resolve complex functions when working with collections, arrays, loops and nest JSON objects before copying the actions across to Flow.

TechShed
Level: Powered On

Re: OData Filter Query on SharePoint Lookup Site Column

2017-05-22_170806.png

WebSolProv
Level: Powered On

Re: OData Filter Query on SharePoint Lookup Site Column

Has this been fixed yet please? Filtering by lookup column in an odata query should be part of core functionality. Update please Microsoft.
JackWert
Level: Powered On

Re: OData Filter Query on SharePoint Lookup Site Column

Hi guys, 

 

Super simple solution to this:

InternalFieldName/Title eq 'Lookup Value'

or 

internalFieldName/Id eq 'Lookup Id'

 


@TechShed wrote:

I have successfuly used the OData Filter Query on the Get Items action for SharePoint but the following scenario does not work:

Country eq 'Australia'

Country is a Lookup Site Column on my list.

When I remove the Filter Query I can see the following JSON returned for the Country Site Column:

[
{
"@odata.etag": "\"1\"",
"ItemInternalId": "1",
"Title": "Test 1",
"Country": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 3,
"Value": "Australia"
},
"Country#Id": 3,
"Modified": "2016-07-13T03:38:40Z",
"ID": 1,
...

 

What is the correct format in the Filter Query to compare the Value of the Country Site Column?

Tried the following:

Country/Value eq 'Australia'

 

I read about having to use the Expand option for the site column to access the Id and Value properties but the Get Items action does not allow for the Expand property.

 

Thanks.


 

swolfsohn
Level: Powered On

Re: OData Filter Query on SharePoint Lookup Site Column

internalFieldName/Id eq 'Lookup Id' is still working today (Feb 26 2019), but one additional note: it seems like for large lists (>5000 items), this lookup column needs to be indexed in order for this to work for the entire list. Before indexing I could get some Ids to return items and some would return no items even though they definitely existed. Then I indexed the lookup column and now all Ids seem to be working.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 222 members 4,821 guests
Please welcome our newest community members: