cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Please, help to make Odata filter

Hello,

 

I am buildig a flow, and need to put Odata filter, when try to get items in the SP list.

 

I need to filter this SP list by 3 paramenets , Car number, Month, Year. Please,could you help me to bulid filter. I have tried to do it at least by one parameter ( see screenshot attached), but it does not work

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Galina73 ,

So this is the key point!

It's not supported to compare text column  with a lookup field value directly.

The syntax should be like this:

internalFieldName/Title eq 'Lookup Value'

or

internalFieldName/Id eq 'Lookup Id'

 

In your issue , you should try:

columnname in list2 eq 'columnname in list1/Title'

 

This similar issue may help you:
https://powerusers.microsoft.com/t5/Building-Flows/OData-Filter-Query-on-SharePoint-Lookup-Site-Colu...

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

17 REPLIES 17
Super User III
Super User III

@Galina73 

Assuming that the name of your SharePoint columns are CarNumber, Month, and Year, the OData expression to return a record with CarNumber of 'ABC', month of 6, and year of 2020 would look like this:

CarNumber eq 'ABC' and Month eq 6 and Year eq 2020

I assume you want to call this Flow from PowerApps, so you would replace these hardcoded values with 'ask in powerapps' parameters.

Regular Visitor

Hi @Galina73 !

 

ODATA queries can be quiet tricky for the first time, but it sure has logic behind it.

 

screenshot.PNG 

As you can see in this image I took, you can notice 3 points:

-you can chain ODATA filter parameters with the " and " keyword,

-around dynamic content you have to surround the parameter with single quotation marks,

-if you have a column in Sharepoint with space in it's name, you have to replace it with "_x0020_"


In your example it would look somewhat like this:
Car_x0020_number eq 'carNumberParam' and Month eq 'monthParam' and Year eq 'yearParam'

 

You can learn more about ODATA on the following links:

https://docs.microsoft.com/en-us/azure/search/search-query-odata-filter


https://powerusers.microsoft.com/t5/Building-Flows/OData-syntax-for-sharepoint-column-with-a-space-G...

 

Best regards

I have tried with a first parameter car number, but it gives a mistake ( see attachment). It says expression is unacceptable

Hi @Galina73 

 

You seem to have forgotten to place single quote marks around the value. Its always necessary when you use strings as parameter even from the Dynamic content window. 

In the attachment there's an accepted expression.

Hello, I have added quote marks, but stilL codes not work ( see attachment)

Hi @Galina73 !

 

You still need to place the "_x0020_" string in the place where the space is in your column name like: Car_x0020_number

I have already tried this, but it says " This column  does not exist"

Hi @Galina73 ,

Firstly please check whether you've entered right column name.

Secondly, could you show me how do you set the dynamic part?

9112.PNG

 

Here's a doc about how to Use dynamic ODATA Filter values in a Microsoft Flow SharePoint GetItems Action in details, please refer:

https://toddbaginski.com/blog/use-dynamic-odata-filter-values-in-a-microsoft-flow-sharepoint-getitem...

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, Phoebe Liu

 

I have attached field name and also made a print screen for the dynamic content

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (43,556)