cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ovis2018
Level: Powered On

OData filtering for SharePoint Get Items based on SP Column

 

 

Hello MS Flow Community -

Looking for aid with setting up OData filtering within a flow to pull SP list items that meet two specific variables.  I'm looking to pull SPlist items where:

    (1)Approval Status = Approved               @{items('Apply_to_each')?['Actual_x0020_Costs_x0020_Reconci']}

    (2)Actual Costs Reconciled? = No            @{items('Apply_to_each')?['Approval_x0020_Status']?['Value']}

 

I've referenced SO many previous posts and tried using the offered solutions but still haven't had success. Every combination I've tried results in a failed test run with "BadRequest" as my error message. 

 

Also, is it correct that I'm setting up the flow to be Reccurence > Get Items (no filtering) > Apply to each > Get Items with OData filtering ??

Thanks for the help!

OData 1.PNGOData2.PNG

 

 

 

11 REPLIES 11
Super User
Super User

Re: OData filtering for SharePoint Get Items based on SP Column

To answer your second question: no, you would do all the OData stuff in the first Get Items.

 

I too have had issues with doing multiple criteria in the filter as And doesn't seem to work well. On one of my flows I've got round it by doing the filter in the Get Items and then adding a Condition. So I'm interested in hearing if other shave got this to work well.

 

Rob

ovis2018
Level: Powered On

Re: OData filtering for SharePoint Get Items based on SP Column

@RobElliott thanks for the reply.


I'm having trouble getting even one of the filtering elements to work.  Mind sharing the setup you did to get your first criteria working with OData filtering? 

 

When you filter within the first 'Get Items' how do you get the dynamic content to be available? I found that I only have access to the Dynamic Content with a 2nd 'Get Items'... = /

Super User
Super User

Re: OData filtering for SharePoint Get Items based on SP Column

Aha, I've just realised that I did get 2 criteria to work on my flows. I was creating a news digest that sends an email of the news stories published on my SharePoint site in the last week.

 The filter query needed single aspostrophes for both the expression and the typed metadata (PageType is a custom column in my SharePoint SitePages library:

 Created gt '[Expression]adddays(utcNow(),-6)[endofExpression]' and PageType eq 'News'

_multiple_criteria.png

  

Hope that helps.

 Rob
Los Gallardos

ovis2018
Level: Powered On

Re: OData filtering for SharePoint Get Items based on SP Column

Thanks for the visual @RobElliott.  Do your SP Columns have spaces? I think my input isn't working due to my columns having spaces in the title and/or because of the type of column.  i.e. Actual Costs Reconcilied? is a calculated column, and Approval Status is part of a workflow triggering a column value of Approved or Denied. 

Any thoughts on how to insert the column name into the filter when there's spaces and/or with those types of columns?

 

When I use _x0020_ in place of the spaces, it still doesn't work despite some help articles saying that's the solution.  See issues below:

OData 3.PNG

Thank you!

Super User
Super User

Re: OData filtering for SharePoint Get Items based on SP Column

@ovis2018

 

You need to use a columns internal name in the OData filter.  To find the internal name, go to list settings and click on the column name.  You'll see the internal name at the end of the pages URL after "field=".

 

Also, it looks like you might be evaluating a yes/no/boolean column.  If so, you should check for 1 for true and 0 for false.

 

Scott

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
ovis2018
Level: Powered On

Re: OData filtering for SharePoint Get Items based on SP Column

Hi @ScottShearer - thanks, I've learned something new!  Still having issues though..

 

The two columns display as: 

 

Approval%5Fx0020%5FStatus

Actual%5Fx0020%5FCosts%5Fx0020%5FReconci

 

Does that look right? Also, the Reconciled Costs is text form yes/no...

Would my query logic be: 

Actual%5Fx0020%5FCosts%5Fx0020%5FReconci eq 'No'

 

I get the following error when using that logic statement: 

{"status": 400, 
"message": "The expression \"Actual%5Fx0020%5FCosts%5Fx0020%5FReconci eq 'No'\n\" is not valid.\r\nclientRequestId: \r\nserviceRequestId: e"}

 

The only successful logic statement I've been able to do is: startswith(title, dial)  --> but that was only for testing purposes and not part of what I'm trying to generate. ugh! 

 

Super User
Super User

Re: OData filtering for SharePoint Get Items based on SP Column

@ovis2018 

Can you post a screen shot of the list settings page so that I can see the columns and what type of columns you have please?  A screenshot of your filter would also help.

 

Scott

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
ovis2018
Level: Powered On

Re: OData filtering for SharePoint Get Items based on SP Column

@ScottShearer sure -

List 1.PNGList 2.PNG

Super User
Super User

Re: OData filtering for SharePoint Get Items based on SP Column

@ovis2018

 

I need to verify this, but I don't believe that you can use a calculated column in an ODData filter.  I will test and get back to you.

 

I suspected that this was the issue when I saw the internal name.

 

Scott

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Super User
Super User

Re: OData filtering for SharePoint Get Items based on SP Column

My column names don't have spaces and wherever possible I try to avoid them.

Rob
Los Gallardos

v-zhos-msft
Level 10

Re: OData filtering for SharePoint Get Items based on SP Column

Hi,

First, I suggest you to run the flow to get the column name in flow. As shown below:

1.png

Then use the Name in the Filter Query, as shown below:

The expression is : Approval_x0020_Status eq 'Approved' and Actual_x0020_Costs_x0020_Reconci eq 1

However, as you said, the Actual Costs Reconciled column is a calculated column. I'm afraid you can use it in the 

ODData filter

2.png

Best Regards,

Zhongys

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,753)