Fitler array - multiple conditions

[RobinV86]

I need some help with a filter on a spreadsheet. This follows a 'list rows present in a table' action. The goal of the flow is to update or create items in a sharepoint list. 

One of the columns is called project phase. I want to filter out all projects where the phase is either empty or closed. 

I currently have this set up using two conditions inside an apply to each action. However, this is very slow and the flow takes over 1 hr to complete. 

I know I can use the filter array to do this, but how do I set it up to test for both empty and closed items? And how do I tie this in with the updating/creating of a sharepoint item? 

[efialttes]

@RobinV86 

For sure you can nest conditions in a "Filter Array" action block.

THis is an Filter Array condition example taken from another post in this forum:

https://powerusers.microsoft.com/t5/Building-Flows/ODATA-Filter-Query-for-Date/td-p/105072

@and(equals(item()?['Title'], 'Chief of Staff'),and(greaterOrEquals(formatDateTime(item()?['End_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd')),lessOrEquals(formatDateTime(item()?['Join_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))))

In this example, the input of the 'Filter array action block' is a Sharepoint 'Get items', so all entries in the Sharepoint list are evaluated in order to get all the ones matching:

-'Title' column with value 'Chief of Staff', and

-'End Date' column is nost referring to the past  and 'Join Date' column is not referring to the future, taking current flow execution date as the reference for evaluation (utcNow() is the current date and time)

Hope this helps

[Brad_Groux]

You would use the ODATA Filter Query entry on the Excel List rows in an Excel table step before the Apply to each loops:

For help on how to best utilize the Filter Query, please review these links:

• Using Filter Expressions in OData URIs BLOG: Filtering Data with OData in Flow 
• Filter and copy data with Microsoft Flow
• OData 2.0 URI Conventions

NOTE: A Filter Array is not as effecient as Filter Query, so it is only recommended if you can't utilize Filter Query for the task. 

If you could provide an expanded screenshot of your Flow and steps, and of any detailed error messages you're receiving we could likely better assist you.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-bacao-msft · Accepted solution]

Hi @RobinV86 ,

I did a test on my side, please refer to the following method to configure Flow.

• Use advanced mode of Filter array to integrate the two conditions.

Expression reference:

@or(equals(item()?['project phase'], ''),equals(item()?['project phase'], 'closed'))

• After filtering out the expected rows, traverse Body of Filter array by Apply to each.
• And the corresponding field value is obtained by way of item()['ColumnName'].

Image reference:

Hope it helps.

Best Regards,

[RobinV86]

I've tried the filter query solution first but I am getting error messages as a result. 

All the project phases i want to filter for coincidentally include the letter t, so I've been trying: 

substringof(Current_x0020_Phase, 't')

{
  "status": 400,
  "message": "An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\n     inner exception: An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\nclientRequestId: 12948adb-a95b-4481-a645-7e6260456f27",
  "source": "excelonline-ase.azconn-ase.p.azurewebsites.net"
}

and variations of this as posted in the link provided. This doesnt seem to work. 

Edit. Also no luck using

Project_x0020_Reference NE '' or Project_x0020_Reference NE 'Closed'

{
  "status": 400,
  "message": "Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.\r\nclientRequestId: 41cc9017-7078-4849-bf0a-7ebc3abd9cb5",
  "source": "excelonline-ase.azconn-ase.p.azurewebsites.net"
}

[v-bacao-msft]

Hi @RobinV86 ,

Have you tried the method I provided?

Please let me know if you have any question.

Best Regards,

[RobinV86]

I'm trying to implement it now. Wouldn't your solution accomplish the opposite of what I am trying to accomplish? I'm trying to go ahead with a list that does not contain empties and closed projects. Either way, I've changed it to the 'contains t'  and that works! 

I managed to get the odata filter working in a sense; it now throws a 502 error though. 

{
  "error": {
    "code": 502,
    "source": "australia-001.azure-apim.net",
    "clientRequestId": "5338bd9d-2683-4126-a87c-3081d3a95fa7",
    "message": "BadGateway",
    "innerError": {
      "status": 502,
      "message": "Graph API is currently unavailable.\r\nclientRequestId: 5338bd9d-2683-4126-a87c-3081d3a95fa7",
      "source": "excelonline-ase.azconn-ase.p.azurewebsites.net"
    }
  }
}

I'm going with the filter 🙂

[hebbo7]

Hi, I was trying to filter multiple conditions in flow (validating forms response with SP list) for my desk booking flow; 1) Date & Desk No exist in SP 2) Date & Employee name exist in SP its working for validate the date & desk no but 2nd validation didn't worked

[Benabbas]

You can simply overlay filters array

The first condition is a normal filter array

The second filter array should take the body from the first filter array, the condition item should be item()?['Date']

and so on

Never put filter array inside an apply to each to avoid looping

[Digitize]

Hi @Brad_Groux,

My flow to add feedback to excel rows, based on matching to the first column 'Message ID' is to slow to use, I think your above solution may help but I am not sure - please take alook:

https://powerusers.microsoft.com/t5/Building-Flows/Conditions-to-update-correct-excel-row-based-on-m...

[Anonymous]

can we also use StartsWith with the column name from a excel sheet while using filter array ? If yes, how do we write it ?

[Anonymous]

How to handle the case when no row matches a criteria ? It throws me an error saying that row with ID "xx" not found even after I have specified a child process which is configured to run only if the parent fails to run.

[Anonymous]

It does not take more than one condition in filter array advanced mode. Has this feature been deprecated ? 

Speaking as of August, 2021.