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

Fitler array - multiple conditions

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? 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Fitler array - multiple conditions

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:12.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
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

7 REPLIES 7
Highlighted
Dual Super User III
Dual Super User III

Re: Fitler array - multiple conditions

@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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Super User II
Super User II

Re: Fitler array - multiple conditions

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

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

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!

Highlighted
Community Support
Community Support

Re: Fitler array - multiple conditions

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:12.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
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

Highlighted
Helper I
Helper I

Re: Fitler array - multiple conditions

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"
}
Highlighted
Community Support
Community Support

Re: Fitler array - multiple conditions

Hi @RobinV86 ,

 

Have you tried the method I provided?

Please let me know if you have any question.

 

Best Regards,

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

Re: Fitler array - multiple conditions

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 🙂

Highlighted
New Member

Re: Fitler array - multiple conditions

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

Helpful resources

Announcements
Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Top Solution Authors
Top Kudoed Authors
Users online (5,704)