“I need to get all SharePoint items with the same value in a lookup column, but the OData filter in Power Automate doesn’t return any items. What should the filter look like to work properly?”
To filter items in Power Automate it’s important to understand how the SharePoint lookup column works. Lookup column creates a connection between an item in one list and another item in a second, lookup list. A dropdown field with data from a selected column from the lookup list will be added to the form.
The one thing to notice here is that you can change the column from the lookup list anytime. If you change it, SharePoint will update the lookup data automatically. That means, there must be a connection that’s not related to the data itself, but by item ID instead. The lookup column in the main list contains an ID of the item in the lookup list. And that’s the data you must use in the filter itself, the lookup item ID.
Use ‘Lookup id’ in the Filter Query
As already explained, Filter Query needs ID of the item from the lookup list. It must be a number, so in case you’re using lookup column dynamic content you should use the lookup ‘Id’.
<LookupColumnMainList> eq '<LookupItemIDLookupList>'
e.g. where 'Lookup' is the name of the Lookup column
Lookup eq '61'
Lookup eq '@{items('Apply_to_each_5')?['Lookup/Id']}'
The Filter Query above will return only items with the same lookup value. Just make sure you use the internal name of the lookup column.
Updates: you can use also the value
As pointed out by Damien, you can filter also by the value, not only by Id. Just add /Title to the column internal name and it’ll search for the provided value.
Lookup/Title eq 'Value'Another good point from the comments section, you must always use the name of a column displayed in the main list. If you’re using a different column than the Title from the lookup list, use that column internal name after the / instead of Title.
Lookup/AnotherColumn eq 'Value'You can use it on any column you display in the main list, it’s not limited only to the main one.
Summary
To get more from the OData Filter Query you should understand what you’re filtering. Not only the data and its type, but also how is the column implemented in SharePoint. Once you understand that the lookup data is stored as an item ID to the lookup list, building the filter itself is a simple task.
Approval Flow with multiple approves – I want to save all Approve Names in one SharePoint Column? how to do this?
Hi, I am trying this
I am using the internal name of the columns (which has some x0020 etc in it)
I get an error of:
Column ‘EM_x0020_Board_x003a_Board_x0020’ does not exist. It may have been deleted by another user.
Hello toby,
since the column contains : (represented by _x003a_) I’d guess it’s not the main lookup column but another column that displays some other information based on the lookup. You can’t use these columns in a Filter Query, you can use only the main one.
I’m afraid that doesn’t solve it
Column ‘_APPLICATION_ID’ does not exist. It may have been deleted by another user.
And I know that’s the main column because it has the “Add a column to show each of these additional fields:” option
Cracked It!
I had the wrong internal column name.
I had used my normal way of finding the internal column name which is to go to “List settings” then select my column and check the URL, which ended “blahblahblah&Field=_APPLICATION_ID”
But using “_APPLICATION_ID” as my column name in the Filter Query kept ending up with “Column does not exist”
So I did an unfiltered get Items and then used compose to pull out one item from the value array. Then pasted the output into my text editor and found this:
“OData__APPLICATION_ID”: {
“@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
“Id”: 19,
“Value”: “19”
}
So now, I used “OData__APPLICATION_ID”as my Filter Query Column name and Bingo!
well…
after I remembered that the first two items on he value array were folders and had no value in that field…
And then remembered that the test record I was using was not pulling back any results as this was all part of a duplicate check and I had chosen a record that would not find a duplicate!
Hello Toby,
well done! It’s strange that the url contained wrong internal name, but at least the JSON output didn’t lie… 🙂
Hi Tom,
Thanks! I would never had guessed I needed to take this lookup column, which displays as a string, and compare it to a numeral representing the ID back over in the original list we’re looking up from. Got it fixed now!
Might be interesting to add to the Update part: Lookup/Title eq ‘Value’
/Title works if your LookUp uses the Title column of the lookup list.
If you use another column you must use the name of that column instead of /Title.
As another hopefully useful addition to this – it also appears that the filter and sort oData fields will work on additional columns in the lookup list; it doesn’t have to be the primary lookup column.
For example, I have a field where the lookup column value was the Title but I have several additional columns pulled through as well. As Tom’s pointed out using the internal column name for the additional column on the target list itself doesn’t work and will return a “column doesn’t exist” error, but I can use LookUp/ColumnName using the internal column name from the SOURCE list and it works perfectly.
I think the additional column still needs to have been pulled through to the target list, but it’s hugely useful (at least for me!) to be able to filter and sort with those as well.
Hello Tim,
thank you for sharing, I added it to the article as well.
Hello Tim,
This is exactly what I am trying to do…filter using a column that was pulled along with the Lookup.
The Column in the source or parent list is called Title
The column in the list I’m trying to filter has the internal name PARENTID_x003a__x0020_RES.
I got this name from running the get items unfiltered and looking at the compose output.
I have been pulling my hair out for days. (I even tried using the word “LookUp/Title” but it says the Column LookUp does not exist. ) I’ve tried and tried different combinations of the two column names to no avail.
Any chance you’d be willing to spoonfeed me something I might try?
Hello Joren,
thank you for sharing, I added it to the article.