cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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.

8 REPLIES 8
Highlighted
Community Support
Community Support

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

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.

Highlighted

2017-05-22_170806.png

Highlighted
Anonymous
Not applicable

Has this been fixed yet please? Filtering by lookup column in an odata query should be part of core functionality. Update please Microsoft.
Highlighted
Advocate I
Advocate I

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.


 

Highlighted

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.

Highlighted

Tks @JackWert  worked for me.

Highlighted

Thanks @JackWert I was struggling with the syntax on this one... how simple...just needed an /

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (9,936)