Unanswered

Fitler array - multiple conditions

(2) Share

Report

Posted on by 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?

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

efialttes 14,756 on at

Like (0)

Report
@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

Was this reply helpful? Yes No
Brad_Groux 4,556 on at

Like (3)

Report
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!

Was this reply helpful? Yes No
Verified answer

v-bacao-msft on at

Like (13)

Report
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,

Was this reply helpful? Yes No
RobinV86 45 on at

Like (0)

Report
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" }

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report

Hi @RobinV86 ,

Have you tried the method I provided?

Please let me know if you have any question.

Best Regards,

Was this reply helpful? Yes No
RobinV86 45 on at

Like (0)

Report
"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 🙂

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.
{

Was this reply helpful? Yes No
hebbo7 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Benabbas 6 on at

Like (2)

Report

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

Was this reply helpful? Yes No
Digitize 11 on at

Like (0)

Report

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-matching-to/m-p/736001#M100874

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No