OData filtering for SharePoint Get Items based on SP Column

[ovis2018]

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!

[RobElliott]

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]

@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'... = /

[RobElliott]

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'

Hope that helps.

 Rob
Los Gallardos

[ovis2018]

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:

Thank you!

[ScottShearer]

@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

[ovis2018]

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! 

[ScottShearer]

@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

[ovis2018]

@ScottShearer sure -

[ScottShearer]

@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

[RobElliott]

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

Rob
Los Gallardos

[v-zhos-msft]

Hi,

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

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

Best Regards,

Zhongys

[deweyblue]

This was the best answer, thank you!!!