ODATA Filter queries on LookUp columns from a SP List not working

[Walking18]

Hello, I have a Filter Query in a Get Items action that I just can't get to work. I've spent hours searching for a solution and still no go.  The Get Items action is searching a SharePoint list for items that have a date field ("Approved") empty and and lookup field ("ProjectStatusValue") that is equal to "Active".  I can get records if the filter query is just "Approved eq null" but when I try "Approved eq null and ProjectStatusValue = 'Active'" the query fails with the error "The expression "(Approved eq null) and (ProjectStatusValue = 'Active')" is not valid. Creating query failed.".  I tried one suggest which was to look at the output from the first query that worked and found the following in the data returned.  

"Project_x003a_ProjectStatusValue":{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":12,"Value":"New"

From there I tried multiple variations based on things I've read.  Below are some examples of things I've tried.  None work.  What am I missing.  Kurt

I TRIED:

Approved eq null and Project/ProjectStatusValue = 'Active'

Approved eq null and ProjectStatusValue = 'Active'

Approved eq null and Project/ProjectStatusValue/Value = 'Active'

Approved eq null and Project/ProjectStatusValue = 'Active'

Approved eq null and Project_x003a_ProjectStatusValue = 'Active'

(Approved eq null) and (Project_x003a_ProjectStatusValue = "Active")

and several other variations.  

[grantjenkins]

Your Approved eq null is correct (will return items where there is no date entered).

For your Project Status Choice Field, you just need to use the internal name of your Column. If my internal name was ProjectStatus then I'd just use ProjectStatus eq 'Active'

So, my full query would be:

ProjectStatus eq 'Active' and Approved eq null

You can see the internal name of your column by going into your list settings and hovering over the column. Or you can remove the filter from your Get items, add a Compose after it - taking in the value or body of the Get items and run your flow. Then look at the output of your Compose to see the internal name.

[Walking18]

Grant thank you for replying but I believe I did try the internal name.  Note if I go to the list and look at the URL for that field, the field value is "Project_x003a_ProjectStatusValue" and if I look at the output from the unfiltered query (as shown above) is the same which is "Project_x003a_ProjectStatusValue" but when I run the flow I get the error "The expression "(Approved eq null) and (Project_x003a_ProjectStatusValue = 'Active')" is not valid."  I've also tried it without the ( )s and changing the order putting the Project_x003a_ProjectStatusValue first.  I get an error every time. Any other suggestions?

Note one other thing that may be a little different about this scenario is that in the master list, the field "ProjectStatusValue" is a "calculated" field that uses a "choice" field as its value.  I did this because you can't get to choice fields in the child list using a lookup column. Hopefully that makes sense.   Attached are some print screens of the columns.

[Ajinder31]

Hi @Walking18 , 

Can you please try with eq instead of = operator in OData filter query? For eg. Project_x003a_ProjectStatusValue eq 'Active' 

[Walking18]

I just tried that as well and got a different error.

FILTER QUERY:  Project_x003a_ProjectStatusValue eq 'Active' and Approved eq null

ERROR:  Column 'Project_x003a_ProjectStatusValue' does not exist. It may have been deleted by another user.

[Walking18 · Accepted solution]

Okay, I finally got this to work.  I'm posting the solution so it will hopefully save someone else hours of searching...🙁 

The issue was that the primary lookup field I was using in the child list was "Project" and then I included the "ProjectStatusValue in the added columns to return with the Project items.  The actual field name of the primary column (internal) was "AssociatedProject" which I had renamed to just "Project".  In one of the post I read about issues with renaming the fields and making sure you used the internal name but when you rename the primary lookup field, the internal name remains the same for that field but all the additional fields you include with the lookup column get renamed as well.  In my scenario, when I renamed the "AssociatedProject" to "Project", the included fields got renamed to "Project_x003a_ProjectStatusValue" versus "AssociatedProject_x003a_ProjectStatusValue" which turns out to be the _true_ internal name. Note that true internal name "AssociatedProject_x003a_ProjectStatusValue" did not show up anywhere in the output of the query so go figure.  Note that even using this newly found internal name didn't work at first but then I changed it to the filter query below which did work. Using the "/" between the primary column and the added column did the trick.  I hope this helps someone!

WORKING FILTER QUERY:  AssociatedProject/ProjectStatusValue eq 'Active' and Approved eq null

[v-liwei-msft]

Hi @Walking18 

Glad to see you solved the problem.

You could mark your answer/solution as a 'Solution' to allow more people with the same problem to solve their own problem as quickly as possible.

Thank you so much! 

Best Regards,

Levi

[Walking18]

Done.  Thanks for the reminder.

[Steve-416]

Saved my project - not sure I would have found the slash / solution - kudos to you!

[Tango]

@Walking18 THANK YOU SO MUCH. Saved me a heap of time

[Anonymous]

Hi sorry, its not really clear for me, can someone describe one more time or comment the "desired" filter? Thank mates btw

[VNO]

Great.
I was looking for this !  Thank you for the tips.