Unanswered

Filter Query with multiple conditions

(0) Share

Report

Posted on by Jukie

I am new to Power Automate, but based on another community post, I successfully created a flow to automatically copy items created or modified in one Sharepoint List to another in real time.

- When an item is created or modified

- Get Items (Filter: SyncedID eq Body/IDx)

- Condition to create the item in the second list if true or update the item in the second list if false

Works great.

If possible, under Get Items, I want to continue to add another condition that filters out any records where BOTH of the following are true: Column A is not "placeholder" and Column B is not null.

The filter would need to return all records where:

- Synced ID eq Body/ID, Column A is "placeholder" and Column B is not null

- Synced ID eq Body/ID, Column A is not "placeholder" and Column B is null

Is that possible under Filter Query or do I need to research Filter Array? Thanks!

Categories:

Building flows

I have the same question (3)

All responses (10)

Answers (1)

Tristan_D 400 on at

Like (0)

Report
Hi @Jukie !

Sadly, it's not possible to construct complex conditions directly within the Filter Query using logical operators like AND or OR to handle multiple conditions simultaneously.

You can however use the Filter Array action instead.

After the "Get Items" action, add a "Filter Array" action from the Data Operations category.
In the "Filter Array" action, specify the following conditions:
Column A is not "placeholder" AND Column B is not null
OR
Column A is "placeholder" AND Column B is null
it would look like :
@and(not(equals(item()?['ColumnA'], 'placeholder')), not(empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and empty(item()?['ColumnB']))

Finally, you can connect the output of the "Filter Array" action to the subsequent steps in your flow.

Would that work for you ?

T

Was this reply helpful? Yes No
Tristan_D 400 on at

Like (0)

Report
Hi @Jukie !

You can use the Filter Array action instead actually :
After "Get Items", add a "Filter Array" action.
In the "Filter Array" action, you can use the expression builder and the result should be something like :
@and(not(equals(item()?['ColumnA'], 'placeholder')), not(empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and empty(item()?['ColumnB']))

Then, you can use the output of the "Filter Array" later on in your flow 😉.

Does that work for you ?

T

Was this reply helpful? Yes No
Jukie 82 on at

Like (0)

Report
Hi Tristan,

Apologies for the delayed reply. I am clearly doing something wrong!

The parameters above are almost correct, so I adjusted for it. Either of these two conditions must be true for the item to be included in the new Sharepoint List.
Column A is not "placeholder" AND Column B is null
Column A is "placeholder" AND Column B is not null
In the From of the Filter Array, I have tried both (1)outputs('Get_items') and (2)outputs('Get_items')?['body/value']

In the Filter Query (using Advanced Mode), I have copied in:
@(not(equals(item()?['ColumnA'], 'placeholder') and (empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and not(empty(item()?['ColumnB'])))

Unfortunately, it always turns up as an invalid expression. Do you see where I am messing up? Thanks!!!

Was this reply helpful? Yes No
Jukie 82 on at

Like (0)

Report
Hi @Tristan_D ,

Apologies for the delayed reply. I am clearly doing something wrong!

The parameters above are almost correct, so I adjusted for it. Either of these two conditions must be true for the item to be included in the new Sharepoint List.
Column A is not "placeholder" AND Column B is null
Column A is "placeholder" AND Column B is not null
In the From of the Filter Array, I have tried both (1)outputs('Get_items') and (2)outputs('Get_items')?['body/value']

In the Filter Query (using Advanced Mode), I have copied in:
@(not(equals(item()?['ColumnA'], 'placeholder') and (empty(item()?['ColumnB']))) or (equals(item()?['ColumnA'], 'placeholder') and not(empty(item()?['ColumnB'])))

Unfortunately, it always turns up as an invalid expression. Do you see where I am messing up? Thanks!!!

Was this reply helpful? Yes No
Tristan_D 400 on at

Like (0)

Report
Hi @Jukie !

No worries 😊 !

Could you try this expression instead ?
@(not(equals(item()?['ColumnA'], 'placeholder')) and (empty(item()?['ColumnB'])) or (equals(item()?['ColumnA'], 'placeholder')) and not(empty(item()?['ColumnB'])))
I think there is an issue with the brackets.

Let me know if it works 😉.

T

Was this reply helpful? Yes No
Jukie 82 on at

Like (0)

Report

Hi @Tristan_D ,

Your expression looks good to me, but it is still telling me it is invalid. I have the feeling that I must be doing something else wrong, so let me back up. Just as a reference, I am syncing my two SharePoint lists based on these instructions: https://www.youtube.com/watch?v=looyVm_8OKI

CoPilot gave me the following expression which does not return invalid if I have Value in the From field, but it also doesn't work when I test it.
@or(and(not(equals(item()?['field_2'], 'Placeholder')),empty(item()?['field_9'])), and(equals(item()?['field_2'], 'Placeholder'),not(empty(item()?['field_9']))))
(field_2 and field_9 are the actual names of the SharePoint List columns. An issue from when I first set the list up.)

Is there something else that I would need to add under the conditions? Seems like it is skipping the filter array.
Under Update item, ID = outputs('Get_Items')?['body/value'][0]['ID']
Under Create item and Update item, the two fields I am filtering = triggerOutputs()?['body/field_2/Value'] and triggerOutputs()?['body/field_9']

The Syncing works perfectly without the filter array. Do you see anything obvious? Again, thanks so much for your time and help!

Was this reply helpful? Yes No
Tristan_D 400 on at

Like (0)

Report

Hi @Jukie !

I am not sure at 100% what might happen here, could you switch the filter array back to the classic view in order to see if it is an issue generated by the advanced code ?

Have you got one of the flow run that we could check (especially what is returned in the filter array ouput) ?

Thanks.

T

Was this reply helpful? Yes No
Verified answer

Jukie 82 on at

Like (1)

Report

Hi @Tristan_D ,

Thanks again for all your assistance! Work took me away from this project for a bit, but in the meantime, one of my colleagues was able to find an alternate solution before I could get back to you. Instead of using the Filter Array, we ended up with multiple conditional statements. It is a little more complicated, but works like a charm.

When an item is created or modified:
Condition 1: field 2 = Placeholder
If Condition 1 is true, then Get Items (Filter Query: SyncedID = ID)
Condition 2: length(outputs('Get_items')?['body/value']) = 0; if 0 then create, if not then update

If Condition 1 is not true, then look at Condition 3.
Condition 3: field 9 is null
If Condition 3 is true, then Get Items (Filter Query: SyncedID = ID)
Condition 4: length(outputs('Get_items')?['body/value']) = 0; if 0 then create, if not then update

If Condition 3 is not true, do nothing.

Have a great rest of your day!
Julie

Was this reply helpful? Yes No
Tristan_D 400 on at

Like (0)

Report

Well done Julie (@Jukie )!

Happy to hear that you managed to find a solution.

T

Was this reply helpful? Yes No
aamirghanchi 38 on at

Like (0)

Report

Power automate does not support multiple conditions in the Filter array function.

1 people found this reply helpful.

Was this reply helpful? Yes No