Answered

Get items filter on column value when column is an object

(0) Share

Report

Posted on by Community Power Pla...

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.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

v-qiaqi@microsoft.com on at

Like (1)

Report
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!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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.)

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

And yet, if I remove the filter in the Get Items block, this works:
I really don't understand why the filter query fails.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report
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'

Was this reply helpful? Yes No