OData Filter Query on SharePoint Lookup Site Column

[TechShed]

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.

[v-yamao-msft]

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.

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

[TechShed]

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]

[Anonymous]

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

[JackWert]

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]

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.

[darrenmuscat]

Tks @JackWert  worked for me.

[Dowsetts101]

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

[jcfDaniel]

Thank you @JackWert ,

I was looking to get a Case Resolution from a Dynamics 365 Case and your solution works here too:

incidentid/incidentid eq 'Dynamic content Case'

Regards.

[Anonymous]

@JackWert When I try this, I get "One or more field types are not installed properly. Go to the list settings page to delete these fields." Well, that would break it all, because these lookup columns are key columns for data integrity (restrict delete).
On a different note, in internalFieldName/Id eq 'Lookup Id' normally these are integers, so no need for single quotes around LookupId (but I would attempt not to use spaces).

[jleira]

This is the last solution

If you want to compare the string then use

internalFieldName/Title eq 'Value of your LookUp field'

If you want to compare the id then use

internalFieldName/Id eq 'id of your LookUp field '

[vikas1606]

This is the LookUp column so need to use a query like below.

Country/Title eq 'Value'

Here Country is the Lookup column name and Title is the column name in the source list.