Get items filter on column value when column is an object

[KarlInOz1]

I am trying to get items from a list and trying to limit the number of items I get via a filter on the Get Items action. I am having problems and have tried many iterations.

I am trying to filter the Items by the value in the column called Employee:Mobile No.

Here is the output of some of the first item in the list if I do a query with no filter:

  "body": {
    "value": [
      {
        "@odata.etag": "\"1\"",
        "ItemInternalId": "49718",
        "ID": 49718,
        "Title": "Rostered Day Off",
        "Employee_x003a_Mobile_x0020_No": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
          "Id": 4,
          "Value": "0411111111"
        },
        "Employee_x003a_Mobile_x0020_No#Id": 4,

and here is my query

And the result when I run the flow is

Some of the iterations I have tried in my query are:

Employee_x003a_Mobile_x0020_No/Title eq '0411111111'

Employee_x003a_Mobile_x0020_No eq '0411111111'

Employee:Mobile_x0020_No/Value eq '0411111111'

Employee:Mobile No/Value eq '0411111111'

I haven't found any internet searches which seem to deal specifically with this. The only one which looked like it may work seemed to have the sub item called Title and that is why, out of desperation I tried the iteration with Title. 

The error states that the column does not exist when in the data returned from a query without the filter I can see it does exist so I clearly have something wrong in my syntax. 

[v-qiaqi-msft]

Hi @KarlInOz1,

Is your column which name is Employee:Mobile No a lookup column in the SharePoint list?

If so, the format for filter lookup column should be:

InternalFieldName/Title eq 'Lookup Value'

InternalFieldName/Id eq 'Lookup Id'

 Where the Title is the column from the list as the source.

I have made a simple test for your reference:
1. Here is the SharePoint list with a lookup column called Employee:Mobile No

Where the source is the list called test230328 and column CarName

2.  Using the format Employee_x003a_MobileNo/CarName eq 'Car1' in Filter Query

where

Employee_x003a_MobileNo is the internal column name for the lookup column called Employee:Mobile No

CarName is the source internal column name

3. The result shows it was successfully filtered:

Using the format InternalFieldName/Id eq 'Lookup Id' also works with the same result.

However, if I tried to use Employee_x003a_MobileNo/Title eq 'Car1', it did not filter the condition since the Title column is not the source column that connected to.

So please note to check the correct format of the internal column name, and get the correct column that is connected to the list.

Here is the link for more reference:

SharePoint - ODATA filtering by lookup values · Community (powerautomate.com)

Hope it helps!

[KarlInOz1]

@v-qiaqi-msft Thank you for taking the time to respond to this for me.

Your suggestion still hasn't solved my problem but you have clarified what I should be putting in the position of 'Title'.

Yes my column is a lookup column. The Internal field name is:

   

so it seems I should be using 

Employee_x003a_Mobile_x0020_No

rather than 

Employee_x003a_MobileNo

 But even when I try 

Employee_x003a_Mobile_x0020_No/Id eq '1'

as my filter query I still get the result

Column 'Employee_x003a_Mobile_x0020_No' does not exist. It may have been deleted by another user.

[KarlInOz1]

I have also tried

EmployeeMobileNo/Id eq '1'

and

Employee:MobileNo/Id eq '1'

The first one fails again saying the column is not found, the second fails on bein an invalid query (understandably because of the colon.)

[KarlInOz1]

And yet, if I remove the filter in the Get Items block, this works:

I really don't understand why the filter query fails.

[KarlInOz1 · Accepted solution]

It's OK, with the help of one of my colleagues we cracked it! 🕺

Our column Employee:Mobile No is a lookup column but, and I don't know the terminology to describe this, it is not the 'root' column; it's a sub column in some sense and seems to be invisible to the filter. The root lookup column in our list is Employee and the column in the remote list I want is Mobile No. So, the query that actually works is

Employee/Mobile_x0020_No eq '0411111111'